跳至主要內容

Klustron（KunlunBase）的 SQL 语法兼容性

Klustron大约 14 分钟

Klustron（KunlunBase）的 SQL 语法兼容性

Klustron 的 SQL 兼容性概述

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

对于DBA来说，Klustron支持PostgreSQL的 DDL语法（除了外键等功能之外），并且从Klustron-1.2版本开始，支持MySQL 常见DDL语法。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 里面的字段建立索引，从Klustron 1.3版本开始，已经支持创建和使用JSON路径索引。

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

支持的其他PostgreSQL和MySQL功能

  • 同时支持 PostgreSQL 和MySQL 的引号规则(即 反引号 ` , 单引号 ' 和双引号 " 这三种引号的用法规则)
    • 在PostgreSQL连接和MySQL连接中，都使用反引号(即 `) 括住标识符，即表名，列名，过程名等各类数据库对象的名字
      • 这样就可以使用关键字做标识符，或者使用白字符和各种标点符号做名字，尽管我们非常不建议此用法
    • 在PostgreSQL连接和MySQL连接中，都使用用单引号(即 ') 括住字符串常量
    • 双引号(即 ") 的用法依赖于连接种类
      • 在PostgreSQL连接中，双引号总是用于括住标识符，即表名，列名等各类数据库对象的名字
        • 这样就可以使用关键字做名字，或者使用白字符和各种标点符号做名字，尽管我们非常不建议此用法
      • 在MySQL连接中，如果mysql_ansi_quotes = on, 那么双引号用于括住标识符，相当于MySQL中给SQL_MODE设置了ANSI_QUOTES标志位；如果mysql_ansi_quotes =off,那么双引号用于括住字符串常量，并且在这样的字符串中可以无需转义地包含单引号字符。
  • 支持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，schema, table，view, materialized view, column，index, sequence, procedure，trigger, user等的名称。
  1. Klustron 支持常用的 MySQL 私有的DDL和DML语法以及变量读写语法 。

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

  • 从Klustron-1.2版本开始，Klustron也支持大部分MySQL DDL语法，除了存储过程、触发器，EVENT之外。

  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] 和REAL

    • byte, tinyint, mediumint, middleint

    • 所有整形类型的 UNSIGNED 修饰符（从Klustron-1.3 版本开始，unsigned修饰符具备了约束力，如果输入负数会报错）

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

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

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

      • 为了与二者都保持一致，在 Klustron 中，TIMESTAMP 数据类型名， 在 MySQL 连接中遵循 MySQL 的定义（有时区），在 PostgreSQL 连接中遵循 PostgreSQL 的定义（无时区）。

  2. Klustron 不支持 MySQL 存储过程、EVENT、trigger 语法以及其他私有特殊 DDL 语法

  • 例如： load data infile 命令；replication相关命令等。
  1. 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语句
  • 隐式的事务启动和提交: 执行DDL或者BEGIN TRANSACTION或者SET AUTOCOMMIT=TRUE 时，会自动提交当前会话中活跃的事务。
  • 可选地(set enable_stmt_subxact=true时) 支持MySQL的错误处理机制，即语句返回错误只回滚语句而不回滚事务，由应用逻辑决定提交还是回滚这个事务。
  1. ENUM 和 SET类型 如果从MySQL数据库导入数据到Klustron集群，那么在Klustron-1.3.3版本之前，CREATE TABLE语句中的 ENUM和SET类型的列会被当做字符串类型，相应的DML语句中的ENUM 和 SET类型的字段也会当做字符串来插入和更新。这意味着下面这些问题。 从Klustron-1.3.3版本开始， 对于这种CREATE TABLE语句中定义的ENUM 类型，Klustron会自动为该表定义和使用enum数据类型，因此非法的enum 值不再被忽略。
  • 目前不支持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');
  • 使用没有的enum值应该报错，但是在Klustron-1.2 中并没有报错。此问题自从Klustron-1.3.3 开始不再存在。
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标准定义的用法。MySQL-8.0中这样的用法已经过时(deprecated)，未来会被去除。

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

这些关键字是MySQL早年使用myisam引擎时期的残留，即使在MySQL-5.5 之后的版本中也已经没多大意义，所以实际上很少有人真的用过这几个修饰符。因此在Klustron中，我们不支持这些修饰关键字。

不可以在增删改查语句中指定分区

也就是不支持形如这样的MySQL INSERT语法： 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 的CTE 语法

  6. 支持更新分区列的字段，如有必要KlustronDB会自动迁移被更新的行到新的表分区

  7. 支持多表连接作为WHERE 条件的UPDATE 和DELETE 语句

  8. 支持RETURNING 子句，即返回被更新、删除的行的任意字段或者使用这些字段的任意计算表达式的值

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]

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

  2. 不支持指定分区查询

Klustron坚持分区细节对用户透明，用户永远只需要使用根表来读写数据行。

  1. 不支持导出数据语法

    [INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]

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

用户需要使用PostgreSQL的COPY命令，Klustron支持COPY语法。

  1. 支持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]

  2. CTE should be in PostgreSQL syntax

不支持MySQL的CTE语法，查询语句中使用CTE的话，必须使用PostgreSQL的CTE语法。

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

必须使用PostgreSQL语法的窗口函数，并且功能远远比MySQL要更加丰富和完善。

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-1.0版本得到的结果，可以看到大多数不支持的MySQL函数是gis和json函数，其余的函数，也可以在Klustron 中找到相同功能的函数，只是函数名称不同。

从Klustron-1.3版本开始，如果加载了PostGIS插件，那么下面列表中几乎全部GIS函数就可以使用了，并且GIS数据的表示方法完全遵循OGC定义的WKT/WKB格式，与MySQL相同。 从Klustron-1.3.3 版本开始，KlustronDB已经支持MySQL中除了JSON函数之外的几乎全部系统函数，包括下列的除JSON函数之外的几乎全部函数。

    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