跳至主要內容

Klustron（KunlunBase）的 SQL 语法兼容性

Klustron大约 11 分钟

Klustron（KunlunBase）的 SQL 语法兼容性

Klustron 的 SQL 兼容性概述

对于应用系统开发人员来说，Klustron 与 PostgreSQL 和MySQL 完全相同，绝大多数原本使用PostgreSQL或者MySQL的应用程序可以不需任何修改和重新编译，就能够连接Klustron正常工作，这极大的便利了PostgreSQL 和MySQL 用户迁移到Klustron。能做到这一点是 因为Klustron支持 PostgreSQL 和MySQL 的所有DML SQL 语法和大部分DDL语法，同时Klustron支持MySQL 特有的 诸多功能。

对于DBA来说，Klustron支持PostgreSQL的 DDL语法（除了外键、Rule等功能之外），并且从Klustron-1.2版本开始，支持MySQL 常见DDL语法。在Klustron-1.2版本之前的版本，klustron提供了辅助工具DDL2Kunlun 可以让用户把MySQL的DDL语法的SQL语句转换为PostgreSQL的DDL语法的SQL语句。Klustron的计算节点支持MySQL的SHOW系列命令，还支持MySQL的私有数据类型。

Klustron 支持SQL 系列标准，这源自PostgreSQL对SQL标准极好的兼容性。Klustron支持触发器和 PL/SQL语法的存储过程，这极大便利了使用PostgreSQL和Oracle等数据库系统的应用软件迁移到Klustron。

只有当你希望写存储过程时才需要 PostgreSQL 的 PL/SQL 知识。另外由于完全基于标准 SQL 语句，所以各种 SQL生态的工具能够自动与Klustron系统工作，特别的， ORM 工具比如 hibernate 能够与 Klustron 协同工作，这些 ORM 工具生成的代码和模块不需要修改就可以访问 Klustron 。

对于习惯使用 PostgreSQL 的开发者来说， PostgreSQL 的私有SQL 扩展，除了被禁用的部分，都是良好支持的，详见下文；

Klustron支持PostgreSQL的JSON数据管理，用户可以使用PostgreSQL 的JSON 函数和运算符来读写JSON数据，这些函数和运算符有很多是SQL标准，所以MySQL也支持。Klustron不支持MySQL私有的JSON函数和运算符。在Klustron-1.3之前的版本，用户不能对JSON 里面的字段建立索引，从1.3版本开始，已经支持创建和使用JSON路径索引。

有少量运算符，在MySQL和PostgreSQL中有不同的意义，比如 $$, ||, ^, #, ? 等，这些运算符在Klustron的MySQL协议的连接中遵循MySQL中的意义， 在PostgreSQL 协议的连接 中遵循其在PostgreSQL中的意义。

支持的其他PostgreSQL和MySQL功能

  • 支持 PostgreSQL 和MySQL 的引号规则(`/'/")
  • 支持PostgreSQL 和MySQL 的数据类型和运算符
  • Prepared statement(PostgreSQL语法和MySQL语法)
  • PostgreSQL 和 MySQL 字符集和collation
  • PostgreSQL 和 MySQL 的系统函数、系统变量读写语法

MySQL 兼容性

关于Klustron 对 MySQL 私有 DML 语法 支持的细节，详见 Klustron 对 MySQL 私有 DML 语法的支持

关于Klustron 的 MySQL 连接协议 的细节，详见Klustron 的 MySQL 连接协议简介

关于Klustron 与 MySQL 语法的细微差异，详见 Klustron 不支持的 MySQL 语法和功能汇总

  1. 标识符长度限制
  • PostgreSQL 要求标识符名称字节数<64, 意味着如果标识符是中文的话，中文标识符最大字符数 M 在一个范围内变化，最小的M不到20个字符。这里的标识符包括database，table，column，index, sequence, procedure，user等的名称。
  1. Klustron 支持常用的 MySQL 私有的DML语法以及变量读写语法 。

  • 无论在使用 PostgreSQL 协议的连接还是 MySQL 协议的连接中，都可以执行 Klustron 支持的 PostgreSQL和MySQL 语法，包括这些 MySQL 私有 DML 语法，也包括标准的 SQL 语法，以及 Klustron 支持的任何其他 PostgreSQL 私有语法。

  • 从Klustron-1.2版本开始，Klustron也支持部分MySQL常用DDL语法。

  1. Klustron 支持 autoincrement 关键字定义列为自增列，也支持 last_insert_id() 函数，其用法与 MySQL 完全相同
  • 比MySQL更加强大的是，在Klustron中，用户可以定义多个列为autoincrement的，而不仅仅限于一个列。
  • 可以使用PostgreSQL的CREATE TABLE语法，来 更加灵活的使用sequence，比如多个表使用同一个sequence产生ID 列值。

  1. Klustron 支持 MySQL 的所有私有数据类型，包括：

    • TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, VARBINARY, BINARY: 在 Klustron 中统一按照 bytea 类型来存储和处理

    • TINYTEXT, MEDIUMTEXT, LONGTEXT: 在 Klustron 中都统一按照 text 类型来存储和处理

    • DOUBLE [PRECISION]

    • byte, tinyint, mediumint, middleint

    • 所有整形类型的 UNSIGNED 修饰符（但是unsigned修饰符没有约束力，被直接忽略了）

    • DATETIME：作为不带时区信息的时间戳。

    • TIMESTAMP：作为带时区信息的时间戳

    • 在 MySQL 和 PostgreSQL 中 TIMESTAMP 类型有不同的意义：在 MySQL 中它表示带有时区信息的时间戳，在 PostgreSQL 中表示不带时区信息的时间戳。

      • 为了与二者都保持一致，在 Klustron 中，在 MySQL 连接中遵循 MySQL 的定义，在 PostgreSQL 连接中遵循 PostgreSQL 的定义。

  2. Klustron 不支持 MySQL 存储过程或者 MySQL 专有的 DDL 语法，以及 load data infile 命令，replication相关命令等任何 MySQL 特有功能的语法。

  3. MySQL特有的系统数据库 performance_schema, sys, mysql 在Klustron 的计算节点中不存在，无法在计算节点的MySQL连接中访问这些database。

  • information_schema 系统库是SQL标准，不过MySQL的information_schema 库中的部分系统表和视图，在klustron-1.2 版本中不存在，即使有同名的表，其表定义也未必等价。
  • 从Klustron-1.3版本开始，information_schema 已经具备了几乎全部MySQL information_schema 中的视图，同名的表则增加了MySQL information_schema 特有的列。

  1. 支持 MySQL的 常用 show 系列命令

  2. 支持MySQL 事务处理功能

  • autocommit语句
  • 隐式的事务启动和提交
  • 可选地(enable_stmt_subxact=true时）支持MySQL的错误处理机制，即语句返回错误只回滚语句而不回滚事务，由应用逻辑决定提交还是回滚这个事务等等。
  1. ENUM 和 SET类型 如果从MySQL数据库导入数据到Klustron集群，那么CREATE TABLE语句中的 ENUM和SET类型的列会被当做字符串类型，相应的DML语句中的ENUM 和 SET类型的字段也会当做字符串来插入和更新。这意味着下面这些问题。
  • 目前不支持enum类型的字段用角标指代, 在klustron中enum项的编号被当做varchar处理
create table t1(name ENUM('a','b'));
insert into t1 values (2);
insert into t1 values ('2');
  • 区分大小写，MySQL enum/set字段是不区分的
insert into t1 values ('A');
  • 没有的值应该报错，但是在Klustron中并没有报错。
create table t2(name set('a','b','c'));
insert into t2 values ('a,c,h');
  • 不支持set 元素去重
insert into t2 values ('a,b,b,a'),('a,b,c,c,b,a,a,b,c');
  1. 浮点数精度设置被忽略 下面的列类型定义中的精度限制(p,n)被忽略，因为这不是SQL标准定义的用法。
float(p,n)
DOUBLE(p,n)
DOUBLE(n)

这就导致下面的问题。

  • 无法进行四舍五入，总是存储精确数值。这样做通常不是错误也没有损失。
CREATE TABLE t3(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2),
f5 DOUBLE(2)
);

INSERT INTO t3(f1, f2) VALUES(123.45,123.45);
INSERT INTO t3(f1, f2) VALUES(123.456789,123.456789);

insert 语句:支持大部分 MySQL 私有扩展语法

支持 on conflict do update 子句并且保持 MySQL 的语法（其语法与 PostgreSQL 略有不同）

支持 insert ignore 来忽略unique key/primary key冲突，不忽略其他错误

例如：insert ignore into t1 values(1,2);

支持replace语句来覆盖重复行

例如：

replace into t1 set a=1,b=2;

replace t1 values(1,2);

支持 insert 语句的 set 语法：

例如：insert into t1 set a=1,b=2;

支持 insert into... select from

禁止 insert 和 replace 的其他修饰关键字，即 delayed， low_priority, high_priority

不可以指定分区，也就是不支持 insert into t2 partition(p0,p1) values(1,2),(3,4);

update & delete 语句

  1. 支持多表更新 updating/deleting rows of multiple tables in one statement;

  2. 支持排序和行数限制 support‘'order by' clause and 'limit' clause of update/delete statement;

  3. 不支持MySQL私有修饰符，包括 IGNORE ， delayed， low_priority, high_priority,quick

  4. 不支持指定分区的更新和删除语法 do not allow specifying partitions

  5. update和delete中 使用的common table expression (CTE) 要使用 PostgreSQL 语法

  6. 支持更新分区列

select：禁止 MySQL 私有扩展

  1. 不支持 MySQL 私有的modifiers，包括

    [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

  2. 不支持with rollup 子句，因为PostgreSQL 的OLAP 功能比这要强大很多，不需要这个 with rollup 子句

  3. 不支持指定分区查询

  4. 不支持导出数据语法

    [INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]

export_options
| INTO DUMP-FILE 'file_name' | INTO var_name [, var_name]]

  5. 支持SELECT 加锁子句

    支持通用语法 [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] | LOCK IN SHARE MODE] 但是不支持MySQL私有选项： [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]

  6. CTE should be in PostgreSQL syntax

  7. Window functions: window definition and usage should be in PostgreSQL syntax

MySQL系统函数的兼容性

MySQL 的系统函数中除了 GIS 和 json 函数，其余绝大多数都是标准的 SQL 函数。所有 SQL 标准函数都是 Klustron 支持的，剩余少数 MySQL 特有的非 gis 和 json 函数会根据用户需要来支持，目前已经支持了所有主流语言的客户端库和大量应用软件用到的系统函数。另外用户也可以从 PostgreSQL 中找到替代函数；

1、Klustron支持的MySQL函数包括：

postgres=# select distinct t1.*from  MySQL_funcs t1, pg_proc t2 where lower(t1.fname)=t2.proname;

fname
------------------
ABS
ACOS
ASIN
ATAN
ATAN2
BIT_LENGTH
CEIL
CEILING
CHARACTER_LENGTH
CHAR_LENGTH
CONCAT
CONCAT_WS
COS
COT
DEGREES
EXP
FLOOR
JSON_OBJECT
LENGTH
LN
LOG
LOWER
LPAD
LTRIM
MD5
OCTET_LENGTH
PI
POW
POWER
RADIANS
REGEXP_REPLACE
REVERSE
ROUND
RPAD
RTRIM
SIGN
SIN
SQRT
TAN
UPPER
VERSION

MySQL 的所有 window 函数，在 PostgreSQL 和昆仑系统中都是支持的，但是没有列在上面的列表中，所以在此处单独列出

ROW_NUMBER
RANK
DENSE_RANK
CUME_DIST
PERCENT_RANK
NTILE
LEAD
LAG
FIRST_VALUE
LAST_VALUE
NTH_VALUE

2、Klustron 不支持的 MySQL 系统函数列表

由于我们会持续合并上游percona-mysql 主版本的代码来持续升级 Klustron-storage ，以及Klustron每个版本中也会持续增加支持更多的MySQL系统函数，下面这个列表会随着Klustron的版本而变化。对于你所使用的的Klustron，连接到任意一个计算节点，执行下列语句可以获得你所使用的的Klustron不支持的MySQL系统函数列表。

这里是使用某个版本的Klustron得到的结果，可以看到大多数是gis和json函数，其余的函数，也可以在Klustron 中找到相同功能的函数，只是函数名称不同。 从Klustron-1.3版本开始，如果加载了PostGIS插件，那么下面列表中几乎全部GIS函数就可以使用了，并且GIS数据的表示方法完全遵循OGC定义的WKT/WKB格式，与MySQL相同。

postgres=# select * from  MySQL_funcs t1 where lower(t1.fname) not in (select distinct proname from pg_proc);

fname
-----------------------------------
ADDTIME
AES_DECRYPT
AES_ENCRYPT
ANY_VALUE
BENCHMARK
BIN
BIN_TO_UUID
BIT_COUNT
COERCIBILITY
COMPRESS
CONNECTION_ID
CONV
CONVERT_TZ
CRC32
CURRENT_ROLE
DATEDIFF
DATE_FORMAT
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
ELT
EXPORT_SET
EXTRACTVALUE
FIELD
FIND_IN_SET
FOUND_ROWS
FROM_BASE64
FROM_DAYS
FROM_UNIXTIME
GET_LOCK
GREATEST
GTID_SUBSET
GTID_SUBTRACT
HEX
IFNULL
INET6_ATON
INET6_NTOA
INET_ATON
INET_NTOA
INSTR
IS_FREE_LOCK
IS_IPV4
IS_IPV4_COMPAT
IS_IPV4_MAPPED
IS_IPV6
ISNULL
IS_USED_LOCK
IS_UUID
JSON_ARRAY
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_DEPTH
JSON_EXTRACT
JSON_INSERT
JSON_KEYS
JSON_LENGTH
JSON_MERGE
JSON_MERGE_PATCH
JSON_MERGE_PRESERVE
JSON_PRETTY
JSON_QUOTE
JSON_REMOVE
JSON_REPLACE
JSON_SEARCH
JSON_SET
JSON_STORAGE_FREE
JSON_STORAGE_SIZE
JSON_TYPE
JSON_UNQUOTE
JSON_VALID
LAST_DAY
LAST_INSERT_ID
LCASE
LEAST
LOAD_FILE
LOCATE
LOG10
LOG2
MAKEDATE
MAKE_SET
MAKETIME
MASTER_POS_WAIT
MBRCONTAINS
MBRCOVEREDBY
MBRCOVERS
MBRDISJOINT
MBREQUALS
MBRINTERSECTS
MBROVERLAPS
MBRTOUCHES
MBRWITHIN
MONTHNAME
NAME_CONST
NULLIF
OCT
ORD
PERIOD_ADD
PERIOD_DIFF
QUOTE
RAND
RANDOM_BYTES
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
RELEASE_ALL_LOCKS
RELEASE_LOCK
ROLES_GRAPHML
ROTATE_SYSTEM_KEY
SEC_TO_TIME
SHA
SHA1
SHA2
SLEEP
SOUNDEX
SPACE
ST_AREA
ST_ASBINARY
ST_ASGEOJSON
ST_ASTEXT
ST_ASWKB
ST_ASWKT
STATEMENT_DIGEST
STATEMENT_DIGEST_TEXT
ST_BUFFER
ST_BUFFER_STRATEGY
ST_CENTROID
ST_CONTAINS
ST_CONVEXHULL
ST_CROSSES
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_DISTANCE_SPHERE
ST_ENDPOINT
ST_ENVELOPE
ST_EQUALS
ST_EXTERIORRING
ST_GEOHASH
ST_GEOMCOLLFROMTEXT
ST_GEOMCOLLFROMTXT
ST_GEOMCOLLFROMWKB
ST_GEOMETRYCOLLECTIONFROMTEXT
ST_GEOMETRYCOLLECTIONFROMWKB
ST_GEOMETRYFROMTEXT
ST_GEOMETRYFROMWKB
ST_GEOMETRYN
ST_GEOMETRYTYPE
ST_GEOMFROMGEOJSON
ST_GEOMFROMTEXT
ST_GEOMFROMWKB
ST_INTERIORRINGN
ST_INTERSECTION
ST_INTERSECTS
ST_ISCLOSED
ST_ISEMPTY
ST_ISSIMPLE
ST_ISVALID
ST_LATFROMGEOHASH
ST_LATITUDE
ST_LENGTH
ST_LINEFROMTEXT
ST_LINEFROMWKB
ST_LINESTRINGFROMTEXT
ST_LINESTRINGFROMWKB
ST_LONGFROMGEOHASH
ST_LONGITUDE
ST_MAKEENVELOPE
ST_MLINEFROMTEXT
ST_MLINEFROMWKB
ST_MPOINTFROMTEXT
ST_MPOINTFROMWKB
ST_MPOLYFROMTEXT
ST_MPOLYFROMWKB
ST_MULTILINESTRINGFROMTEXT
ST_MULTILINESTRINGFROMWKB
ST_MULTIPOINTFROMTEXT
ST_MULTIPOINTFROMWKB
ST_MULTIPOLYGONFROMTEXT
ST_MULTIPOLYGONFROMWKB
ST_NUMGEOMETRIES
ST_NUMINTERIORRING
ST_NUMINTERIORRINGS
ST_NUMPOINTS
ST_OVERLAPS
ST_POINTFROMGEOHASH
ST_POINTFROMTEXT
ST_POINTFROMWKB
ST_POINTN
ST_POLYFROMTEXT
ST_POLYFROMWKB
ST_POLYGONFROMTEXT
ST_POLYGONFROMWKB
STRCMP
STR_TO_DATE
ST_SIMPLIFY
ST_SRID
ST_STARTPOINT
ST_SWAPXY
ST_SYMDIFFERENCE
ST_TOUCHES
ST_TRANSFORM
ST_UNION
ST_VALIDATE
ST_WITHIN
ST_X
ST_Y
SUBSTRING_INDEX
SUBTIME
TIMEDIFF
TIME_FORMAT
TIME_TO_SEC
TO_BASE64
TO_DAYS
TO_SECONDS
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UNIX_TIMESTAMP
UPDATEXML
UUID
UUID_SHORT
UUID_TO_BIN
VALIDATE_PASSWORD_STRENGTH
WAIT_FOR_EXECUTED_GTID_SET
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
WEEKDAY
WEEKOFYEAR
YEARWEEK

MySQL运算符的兼容性

MySQL 的 SQL 标准的运算符在 Klustron 中都已经完全支持；MySQL 私有扩展的运算符已经支持的具体包括：

  • 逻辑运算符 &&，||， ！，XOR
  • 比较运算符 <=> ， A<=>B 意思是 A IS NOT DISTINCT FROM B
  • 位运算符 ^ ， 即bitwise XOR
  • 算数运算符 DIV 和 MOD
  • 赋值运算符 := 大家通常很少用这个而是使用 =

其他 RDBMS 的兼容性

如果用户原来使用 SQL server或者 Oracle server 等其他关系数据库，那么用户可以通过 jdbc 和 odbc 协议连接 Klustron 系统，然后使用标准的 SQL 语法来操作和访问 Klustron 。用户使用的 SQL server 和 oracle server 的所有 SQL 标准功能都可以正常工作。对于 SQL server 和 oracle server 的私有扩展功能，则需要用户修改应用程序的 SQL 语句，改为使用标准的 SQL 功能，或者Klustron 支持的 PostgreSQL 功能扩展。

当前主流的 RDBMS 都有很好的 SQL 标准兼容性，这些标准 SQL 是绝大多数应用软件访问数据库时使用的语法。所以，如果你的应用软件或者网站原本是使用 SQL server， Oracle server，那么你只需要极少的修改或完全不需要修改 SQL 语句，即可使用和读写访问 Klustron。

END

Copyright © Klustron
Copyright © 2025 Klustron