胖头鱼的技术专栏-445 KES V9 MySQL兼容性实战（20260714）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

最近金仓数据也是动作不断，更新了MySQL兼容版本，具体版本号为 V9R3C18，本期探索MySQL兼容性相关特性。

本次测试环境安装在2C8G，100GB磁盘的RHEL 8.10虚拟机上。配置与安装过程省略，可通过下面命令登陆数据库：

ksql -p 54321 -U system test
## 输入system用户密码登录

1 数据库信息

通过SQL对数据库相关信息进行检查。

1.1 数据库版本

SELECT version();

1.3 数据库模式

SHOW database_mode;

1.3 扩展信息

SELECT extname, extversion FROM sys_extension ORDER BY extname;

对其中MySQL兼容性相关扩展介绍：

1.5 关键兼容性参数信息

SELECT name, setting  FROM sys_settings
WHERE name LIKE '%mysql%' OR name LIKE '%compat%'
ORDER BY name;

对其中重要参数进行介绍：

1.6 sql_mode

在MySQL中， sql_mode是一项重要的配置，对SQL模型进行控制，下面是KES V9R3C18的SQL_MODE默认信息。

-- 查看当前生效值 (含会话级SET修改)
SHOW sql_mode;
-- 查看GUC参数配置 (含描述、上下文、默认值等完整信息)
SELECT name, setting, short_desc, context
  FROM sys_settings WHERE name = 'sql_mode';

2 MySQL语法兼容性测试

2.1 #注释（MySQL单行注释）

MySQL语法:  #作为单行注释符，等同于  --

SELECT 'hash_comment_test' AS val; # 这是 MySQL 风格的 # 注释

测试结果：✅完全兼容， #后续内容作为注释不影响SQL语句执行

2.2 零值日期（0000-00-00）

MySQL行为: 在 sql_mode不含 NO_ZERO_DATE时，允许存储 0000-00-00和 0000-00-00 00:00:00

CREATE TABLE zerodate_test (id INT AUTO_INCREMENT PRIMARY KEY, d1 DATE, d2 DATETIME);
INSERT INTO zerodate_test (d1, d2) VALUES ('0000-00-00', '0000-00-00 00:00:00');

默认 sql_mode配置下，因为 NO_ZERO_DATE的配置限制，无法插入零值日期，接下来调整SQL_MODE继续尝试：

SET sql_mode = '';
INSERT INTO zerodate_test (d1, d2) VALUES ('0000-00-00', '0000-00-00 00:00:00');
INSERT INTO zerodate_test (d1, d2) VALUES ('2024-01-15', '2024-01-15 10:30:00');
SELECT * FROM zerodate_test;

测试结果: ✅完全兼容，默认 sql_mode包含 NO_ZERO_DATE(禁止零值日期)；设置 SET sql_mode=''后可正常存储和比较零值日期

2.3 CASE类型隐式转换

MySQL行为: CASE表达式中混合INT和VARCHAR返回值时，若任一分支为字符串，所有结果转为字符串类型

-- 创建测试表并插入数据
CREATE TABLE case_test (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(50));
INSERT INTO case_test (val) VALUES ('apple'), ('banana'), ('cherry'), (NULL);
SELECT CASE WHEN val = 'apple' THEN 1
            WHEN val = 'banana' THEN 'two'
            ELSE 0 END AS mixed_resultFROM case_test;
-- MySQL 结果: 1, 'two', '0', '0' (全部为字符串)
-- KingbaseES: 1, 0, 0, 0 (全部为整数, 'two'被截断)
-- 兼容场景 (第一个分支为VARCHAR):
SELECT CASE WHEN 1=1 THEN 'str' ELSE 'other' END;
-- 兼容场景 (INT与DECIMAL混合):
SELECT CASE WHEN 1=1 THEN 10.5 ELSE 20 END;
-- 兼容场景 (整数与字符串比较):
SELECT CASE 1 WHEN '1' THEN 'match' ELSE 'no' END; 
SELECT * FROM case_test WHERE id = '2';

测试结果: ⚠️部分兼容。以 第一个THEN分支的类型为结果类型，后续分支被隐式转换（字符串转整数时被截断为0）

2.4 字段位置调整 (ALTER TABLE … AFTER / FIRST)

MySQL语法:  ALTER TABLE ... ADD COLUMN ... AFTER col /  FIRST

CREATE TABLE col_pos_test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT, email VARCHAR(100));
ALTER TABLE col_pos_test ADD COLUMN phone VARCHAR(20) AFTER name;
ALTER TABLE col_pos_test ADD COLUMN seq_no INT FIRST;
ALTER TABLE col_pos_test MODIFY age INT AFTER email;

测试结果: ✅ 完全兼容

2.5 RENAME TABLE

MySQL语法:  RENAME TABLE old TO new

RENAME TABLE col_pos_test TO col_pos_test_new1;
ALTER TABLE col_pos_test_new1 RENAME TO col_pos_test_new2;

测试结果: ✅ 完全兼容 (同时支持  ALTER TABLE ... RENAME TO)

2.6 RENAME USER

MySQL语法:  RENAME USER old TO new

CREATE USER test_user_old WITH PASSWORD 'password123';
RENAME USER test_user_old TO test_user_new;
ALTER USER test_user_new RENAME TO user_new1;

测试结果: ✅ 兼容 (使用  CREATE USER ... WITH PASSWORD 创建用户后， RENAME USER 正常工作)

2.7 PREPARE/EXECUTE

MySQL语法:  PREPARE stmt FROM 'SQL'; EXECUTE stmt [USING @var];

-- @变量引用
SET @x = 42; SET @y = 100;
PREPARE stmt FROM 'SELECT @x + @y AS total';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ? 占位符
PREPARE stmt FROM 'SELECT ? AS val';

测试结果: ⚠️ 部分兼容

• ✅ 支持  @变量 在 SQL 字符串中引用
• ✅ 支持  PREPARE ... FROM @变量 动态构建
• ❌  不支持  ? 占位符语法 ( PREPARE stmt FROM 'SELECT ?')

2.8 其他 MySQL 语法兼容一览

-- 反引号标识符
CREATE TABLE `order` (
    `id` INT AUTO_INCREMENT PRIMARY KEY, 
    `select` VARCHAR(50), 
    `from` VARCHAR(50),
    `where` VARCHAR(50)
);
INSERT INTO `order` (`select`, `from`, `where`) VALUES ('a', 'b', 'c');
SELECT `id`, `select`, `from`, `where` FROM `order`;
-- ON DUPLICATE KEY UPDATE
CREATE TABLE upsert_test (id INT PRIMARY KEY, name VARCHAR(50), count INT);
INSERT INTO upsert_test VALUES (1, 'Alice', 1);
INSERT INTO upsert_test VALUES (1, 'Alice', 1) ON DUPLICATE KEY UPDATE count = count + 1;
SELECT * FROM upsert_test;
INSERT INTO upsert_test VALUES (1, 'Alice2', 1) ON DUPLICATE KEY UPDATE name = VALUES(name), count = count + 1;
SELECT * FROM upsert_test;
-- REPLACE INTO
CREATE TABLE replace_test (id INT PRIMARY KEY, val VARCHAR(20));
INSERT INTO replace_test VALUES (1, 'original');
REPLACE INTO replace_test VALUES (1, 'replaced');
SELECT * FROM replace_test;
-- LIMIT offset, count
CREATE TABLE limit_test (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(10));
INSERT INTO limit_test (val) VALUES ('a'),('b'),('c'),('d'),('e');
SELECT * FROM limit_test LIMIT 2, 2;
SELECT * FROM limit_test LIMIT 2 OFFSET 1;
-- DIV, MOD, <=>
SELECT 7 DIV 2 AS div_result;
SELECT 7 MOD 3 AS mod_result;
SELECT 1 <=> 1 AS ns_equal;
SELECT NULL <=> NULL AS ns_null;
SELECT 1 <=> NULL AS ns_null_cmp;
-- 字符串隐式转整数
SELECT '123' + 456 AS str_plus_int;
SELECT '123abc' + 1 AS str_plus_int2;
-- GROUP_CONCAT
CREATE TABLE gc_test (id INT, grp INT, name VARCHAR(20));
INSERT INTO gc_test VALUES (1, 1, 'apple'), (2, 1, 'banana'), (3, 1, 'cherry'), (4, 2, 'dog'), (5, 2, 'cat');
SELECT grp, GROUP_CONCAT(name) FROM gc_test GROUP BY grp;
SELECT grp, GROUP_CONCAT(name SEPARATOR ' | ') FROM gc_test GROUP BY grp;
SELECT grp, GROUP_CONCAT(name ORDER BY name DESC) FROM gc_test GROUP BY grp;
SELECT grp, GROUP_CONCAT(DISTINCT name) FROM gc_test GROUP BY grp;

测试结果: ✅ 完全兼容

3 JSON与函数兼容性测试

在MySQL中也支持JSON存取，包含相关函数。

3.1 JSON_OVERLAPS

MySQL 8.0+函数: 判断两个JSON文档是否包含共同元素

SELECT JSON_OVERLAPS('[1,2,3,4]', '[3,4,5,6]') AS arr_overlap;
SELECT JSON_OVERLAPS('[1,2,3]', '[4,5,6]') AS arr_no_overlap;
SELECT JSON_OVERLAPS('{"a":1,"b":2}', '{"b":2,"c":3}') AS obj_overlap;
SELECT JSON_OVERLAPS('{"a":1}', '{"a":2}') AS obj_no_overlap;
SELECT JSON_OVERLAPS('"hello"', '"hello"') AS str_overlap;
SELECT JSON_OVERLAPS('true', 'true') AS bool_overlap;
SELECT JSON_OVERLAPS('null', 'null') AS null_overlap;
SELECT JSON_OVERLAPS('[1,2,[3,4]]', '[[3,4]]') AS nested_overlap;
SELECT JSON_OVERLAPS('[1,2,3]', '"2"') AS scalar_in_arr;

CREATE TABLE json_overlap_test (id INT AUTO_INCREMENT PRIMARY KEY, tags JSON);
INSERT INTO json_overlap_test (tags) VALUES
    ('["red","green","blue"]'),
    ('["yellow","green"]'),
    ('["black","white"]');
SELECT id, tags FROM json_overlap_test WHERE JSON_OVERLAPS(tags, '["red"]');

测试结果: ✅ 完全兼容

3.2 JSON 操作符 -> / ->>

MySQL语法:  ->返回JSON类型,  ->>返回文本类型

SELECT '{"name":"Alice","age":30}'::jsonb -> 'name' AS obj_json;
SELECT '{"name":"Alice","age":30}'::jsonb ->> 'name' AS obj_text;
SELECT '[10,20,30]'::jsonb -> 1 AS arr_json;
SELECT '[10,20,30]'::jsonb ->> 1 AS arr_text;
SELECT '{"nested":{"key":"value"}}'::jsonb -> 'nested' -> 'key' AS chain_json;
SELECT '{"nested":{"key":"value"}}'::jsonb -> 'nested' ->> 'key' AS chain_text;
SELECT '{"a":{"b":{"c":42}}}'::jsonb -> 'a' -> 'b' -> 'c' AS deep_chain;

测试结果: ⚠️ 部分兼容

• ✅ 支持 jsonb类型的 ->和 ->>操作
• ✅ 支持链式操作 -> 'a' -> 'b' ->> 'c'
• ✅ 支持数组索引 -> 1
• ❌  不支持  mysql_json 类型的-> /->>`操作(语法错误)

3.3 JSON 操作符优先级

MySQL 行为: -> /  ->>优先级 高于比较运算符 ( =,  >,  <)

-- ->> 优先于 = 比较
SELECT '{"x":"42"}'::jsonb ->> 'x' = '42' AS prec_eq;
SELECT '{"x":"42"}'::jsonb ->> 'x' = '43' AS prec_eq_false;
-- ->>优先于>比较
SELECT '{"x":42}'::jsonb ->> 'x' > '40' AS prec_gt;
-- 算术运算需显式转换
SELECT ('{"x":42}'::jsonb ->> 'x')::int + 1 AS prec_arith;
CREATE TABLE json_prec_test (id INT AUTO_INCREMENT PRIMARY KEY, data JSONB);
INSERT INTO json_prec_test (data) VALUES
    ('{"status":"active","count":5}'),
    ('{"status":"inactive","count":10}'),
    ('{"status":"active","count":20}');
SELECT id, data ->> 'status' AS status, data ->> 'count' AS cnt
FROM json_prec_test
WHERE data ->> 'status' = 'active'
ORDER BY id;

测试结果: ✅ 完全兼容

3.4 INSERT() 字符串函数

MySQL函数:  INSERT(str, pos, len, newstr) - 从pos位置删除len个字符并插入newstr

测试结果: ❌ 不兼容。KingbaseES V9R3C18 未提供 MySQL  INSERT()字符串函数

替代方案: 使用 overlay()`函数

SELECT overlay('Quadratic' placing 'What' from 3 for 4) AS alt_insert1;
SELECT overlay('hello world' placing 'there' from 7 for 5) AS alt_insert2;
SELECT overlay('abcdef' placing 'XYZ' from 1 for 3) AS alt_insert3;

3.5 LEFT()函数

MySQL函数:  LEFT(str, n) - 返回字符串左端n个字符

SELECT LEFT('foobarbar', 5) AS left1;
SELECT LEFT('hello', 10) AS left2;
SELECT LEFT('hello', 0) AS left3;
SELECT LEFT('hello', -1) AS left4;
SELECT LEFT('你好世界', 2) AS left5;

测试结果: ✅ 完全兼容

3.6 QUOTE() 函数

MySQL函数:  QUOTE(str) - 用单引号包裹字符串并对内部特殊字符转义

在KingbaseES中提供的函数名称为 quote_text

SELECT quote_text('hello') AS q1;
SELECT quote_text('a"b') AS q2;
SELECT quote_text('a\bb') AS q3;
SELECT quote_text('') AS q4;
SELECT quote_text(NULL) AS q5;

测试结果: ✅ 通过提供相同功能的其他函数实现兼容

3.7 FORMAT() 函数

MySQL函数:  FORMAT(n, d) - 将数字格式化为千位分隔符+指定小数位

-- float8类型
SELECT format(12332.123456::float8, 4) AS fmt1;
SELECT format(12332.2::float8, 2) AS fmt2;
SELECT format(12332.2::float8, 0) AS fmt3;
SELECT format(-12332.123456::float8, 2) AS fmt4;
SELECT format(1234567.89::float8, 2) AS fmt5;
SELECT format(0::float8, 5) AS fmt6;

直接使用FORMAT函数无法正确显示结果，需要额外配置 SET mysql_format_compatible = on

-- numeric类型
SELECT format(12332.123456::numeric, 4);
SELECT format(12332.50::numeric, 1);

两条结果应当分别为12,332.1235和12,332.5

测试结果: ⚠️ 部分兼容。
需设置 SET mysql_format_compatible = on

建议: 在KingbaseES中使用FORMAT函数时，始终将参数显式转换  float8(double precision)类型。

3.8 TIME_FORMAT() 函数

MySQL函数:  TIME_FORMAT(time, format) - 按 MySQL 格式符格式化时间

SELECT time_format('12:30:45'::time, '%H:%i:%s') AS tf1;
SELECT time_format('12:30:45'::time, '%h:%i:%s %p') AS tf2;
SELECT time_format('08:15:30'::time, '%H hours, %i minutes, %s seconds') AS tf3;
SELECT time_format('23:59:59'::time, '%r') AS tf4;
SELECT time_format('00:00:01'::time, '%T') AS tf5;
-- mysql_time_format 支持文本输入
SELECT mysql_time_format('100:00:00', '%H %k %h %I %l') AS mtf1;
SELECT mysql_time_format('08:15:30', '%H hours and %i minutes') AS mtf2;

直接使用TIME_FORMAT函数无法正确显示结果，需要额外配置 SET mysql_format_compatible = on

测试结果: ⚠️ 部分兼容。
需设置  SET mysql_time_format_compatible = on

3.9 其他 JSON 函数兼容一览

SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.a') AS je1;
SELECT JSON_EXTRACT('{"a": {"b": 3}}', '$.a.b') AS je2;
SELECT JSON_CONTAINS('[1,2,3,4,5]', '3') AS jc1;
SELECT JSON_CONTAINS('{"a":1,"b":2}', '1', '$.a') AS jc2;
SELECT JSON_ARRAY(1, 2, 3, 'four') AS jarr;
SELECT JSON_OBJECT('name', 'Alice', 'age', 30) AS jobj;
SELECT * FROM JSON_TABLE(
    '[{"name":"Alice","age":30},{"name":"Bob","age":25}]',
    '$[*]'
    COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;

测试结果: ✅ 完全兼容

4 兼容性测试汇总

4.1 综合汇总

4.2 需注意的兼容性差异

4.3 推荐初始化设置

从MySQL迁移到KingbaseES时，建议在会话开始时执行以下设置：

-- 关闭NO_ZERO_DATE以支持零值日期
SET sql_mode = '';
-- 启用FORMAT千位分隔符兼容
SET mysql_format_compatible = on;
-- 启用TIME_FORMAT格式符兼容
SET mysql_time_format_compatible = on;

4.4 其他注意事项

KingbaseES V9R3C18的 sql_mode参数仅支持以下三个关键字：

注意: MySQL 的  TRADITIONAL、 ALLOW_INVALID_DATES、 NO_ZERO_IN_DATE、 STRICT_TRANS_TABLES 等模式 不支持。

总结

本期通过实战对KingbaseES V9R3C18 MySQL兼容版中的MySQL语法以及MySQL中JSON与函数等特性兼容性进行了实战测试。

老规矩，知道写了些啥。