KES V9 MySQL兼容性实战

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

SELECT version();

SHOW database_mode;

SELECT extname, extversion FROM sys_extension ORDER BY extname;

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

-- 查看当前生效值 (含会话级SET修改)
SHOW sql_mode;

-- 查看GUC参数配置 (含描述、上下文、默认值等完整信息)
SELECTname, setting, short_desc, context
FROM sys_settings
WHERE name = 'sql_mode';

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

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');

SET sql_mode = '';
INSER TINTO 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;

-- 创建测试表并插入数据
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_result
FROM 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 WHEREid = '2';

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;

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

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;

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

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

-- 反引号标识符
CREA TETABLE `order` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`select` VARCHAR(50),
`from` VARCHAR(50),
`where` VARCHAR(50)
);
INSER TINTO `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
CREA TETABLE 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 3A S 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;

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"]');

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;

-- ->> 优先于 = 比较
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;

SELECT overlay('Quadratic' placing 'What'from3for4) AS alt_insert1;
SELECT overlay('hello world' placing 'there'from7for5) AS alt_insert2;
SELECT overlay('abcdef' placing 'XYZ'from1for3) AS alt_insert3;

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;

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;

-- 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;

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

SELECTtime_format('12:30:45'::time, '%H:%i:%s') AS tf1;
SELECTtime_format('12:30:45'::time, '%h:%i:%s %p') AS tf2;
SELECTtime_format('08:15:30'::time, '%H hours, %i minutes, %s seconds') AS tf3;
SELECTtime_format('23:59:59'::time, '%r') AS tf4;
SELECTtime_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;

%i 后的 m、%s 后的 d 等字母被错误替换 (已知缺陷)

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;

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

GROUP BY 严格模式