跳至主要內容

Klustron（KunlunBase） 对 MySQL 私有 DML 语法的支持

Klustron大约 5 分钟

Klustron（KunlunBase） 对 MySQL 私有 DML 语法的支持

前言

为了让 MySQL 的应用更为便捷地迁移到 Klustron，我们做了很多兼容 MySQL 的工作。

本篇章主要介绍 Klustron 现在已经支持的 MySQL 常用的私有 DML 语法，以及这些语法与原生 MySQL 的差异。

一、兼容MySQL的insert ignore语法

功能： 忽略违背唯一约束的新元组。

示例：

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
# 违背唯一约束，不进行插入
postgres -> insert ignore into t1(a,b) values (4,4);
INSERT 0 1
postgres -> insert ignore into t1(a,b) values (4,4);
INSERT 0 0

和原生MySQL的差异：

  • 只忽略唯一性约束，如果违背其他约束（例如分区约束、非null约束），则报错。

例如：

postgres -> insert ignore into t1(a,b) values (4,NULL);
ERROR:  null value in column "b" violates not-null constraint
DETAIL:  Failing row contains (4, null)

二、兼容MySQL的INSERT...ONDUPLICATE KEY UPDATE...语法

功能： 插入数据；如果违背了某个唯一约束，则转变为更新操作，对其中一个冲突的元组进行更新。

示例：

postgres -> create table t1 (a int primary key, b int not null unique);
CREATE TABLE
postgres -> insert into t1 values(3,3), (4,4);
INSERT 0 2


# 插入的数据和已有的两个元组都冲突，但只更新了其中一个元组(3,3)
postgres -> insert into t1 values(3,4) on duplicate key update b=2;
INSERT 0 2
postgres -> select * from t1;
 a | b
---+---
 3 | 2
 4 | 4

和原生MySQL的差异：

  • 暂不支持在ON DUPLICATE KEY UPDATE子句中使用VALUES()函数来引用新值，可以使用excluded虚拟表来代替。

例如：

postgres -> INSERT INTO t1 VALUES(3,0) ON DUPLICATE KEY UPDATE b = excluded.b;
INSERT 0 2
postgres -> select * from t1;
 a | b
---+---
 3 | 0
 4 | 4
(2rows)
postgres -> INSERT INTO t1 VALUES(3,0) ON DUPLICATE KEY UPDATE b=VALUES(b);
ERROR:  syntax error at or near "VALUES"
  • 往临时表批量写入多个新元组时，如果新元组之间存在唯一性冲突，则会报错（根本原因是临时表存在于计算节点，使用的不是innodb引擎）。

例如：

postgres -> create temp table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> INSERT INTO t1 VALUES(5,5), (5,6) ON DUPLICATE KEY UPDATE b = excluded.b;
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a secondtime
HINT:  Ensure that norows proposed for insertion within the same command have duplicate constrained values.
postgres ->
  • 临时表返回的影响行数的差异。即使更新前后的值相同，临时表返回的影响行数仍然大于0。

例如：

postgres -> create temp table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> INSERT INTO t1 VALUES(5,5) ON DUPLICATE KEY UPDATE b=excluded.b;
INSERT 0 1
postgres -> INSERT INTO t1 VALUES(5,5) ON DUPLICATE KEY UPDATE b=excluded.b;
INSERT 0 1

三、兼容mysql的replace into语法

功能： 插入元组；如果存在冲突的旧元组，则删除所有与之冲突的旧元组。

示例：

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> insert into t1 values(3,3),(4,4);
INSERT 0 2

postgres -> replace into t1 values(3,4);
INSERT 0 3
postgres -> select * from t1;
 a | b
---+---
 3 | 4
(1row)

和原生MySQL的差异：

  • 往临时表批量写入多个新元组时，如果新元组之间存在唯一性冲突，则会报错（根本原因是临时表存在于计算节点，使用的不是innodb引擎）。

例如：

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
postgres -> replace into t1 values(1,1),(1,2);
INSERT 0 3

postgres -> create temp table t2(a int primary key,b int not null unique);
CREATE TABLE
postgres -> replace into t2 values(1,1),(1,2);
ERROR:  REPLACEINTO command cannot affect row a secondtime
HINT:  Ensure that norows proposed for insertion within the same command have duplicate constrained values.

四、兼容MySQL的update/delete...order by...limit.. 语法

功能： 指定更新/删除的元组的顺序和数量。

示例：

postgres -> create table t1 (a int primary key, b int);
CREATE TABLE
postgres -> insert into t1 select generate_series(1,100),generate_series(1,100);
INSERT 0 100

# 对非分区表的有序更新
postgres -> update t1 set b=b+1 order by a desc limit 4 returning*;
  a  |  b
-----+-----
 100 | 101
  99 | 100
  98 |  99
  97 |  98
(4rows)

UPDATE 4

postgres -> drop table t1;
DROP TABLE
postgres -> CREATE TABLE t1 (A INT PRIMARY KEY,B INT) PARTITION BY RANGE(a);
CREATE TABLE
postgres -> CREATE TABLE t1p1 PARTITION OF t1 FOR VALUES FROM (0) TO (100);
CREATE TABLE
postgres -> CREATE TABLE t1p2 PARTITION OF t1 FOR VALUES FROM (100) TO (200);
CREATE TABLE
postgres -> insert into t1 select generate_series(0,199);
INSERT 0 200

# 指定分区表删除的总量
postgres -> delete from t1  limit 4 returning *;
 a | b
---+---
 0 |
 1 |
 2 |
 3 |
(4rows)

DELETE 4

和原生MySQL的差异：

  • 暂不支持指定分区表的更新/删除的顺序（注意：临时表的分区表已经支持）。 当然，实际使用中需要严格规定更新/删除顺序的场景是极少的，这一限制并不会对Klustron的用户造成困扰。

例如：

postgres -> CREATE TABLE t1 (A INT PRIMARY KEY, B INT) PARTITION BY RANGE(a);
CREATE TABLE
postgres -> CREATE TABLE t1p1 PARTITION OF t1 FOR VALUESFROM (0) TO (100);
CREATE TABLE
postgres -> CREATE TABLE t1p2 PARTITION OF t1 FORVALUESFROM (100) TO (200);
CREATE TABLE

# 不能指定分区表删除顺序
postgres -> delete from t1 order by a limit 4 returning *;
ERROR:  Kunlun-db: Cannot push down plan
postgres ->

五、兼容MYSQL的INSERT/REPLACE SET语法

功能: 以类似update的语法指定每一列具体的值

示例：

postgres=# create table t1(a int primary key, b int);
CREATE TABLE
postgres=# insert into t1 set a=1;
INSERT 0 1
postgres=# replace into t1 set a=1, b=3;

INSERT 0 2

六、兼容MySQL的INSERT/REPLACE其他特有语法

功能： INTO关键字是可选的；INSERT除了使用VALUES关键字外，也可以使用VALUE关键字。

示例：

postgres=# create table t1(a int primary key, b int);
CREATE TABLEpostgres=# insert t1 values(1,1);
INSERT 0 1
postgres=# insert t1 value(2,2),(3,3);
INSERT 0 2
postgres=# replace t1 values(1,2);

INSERT 0 2
postgres=# replace t1 value(2,3),(3,4);
INSERT 0 4

七、兼容MYSQL的UPDATE IGNORE语法

功能： 忽略update过程中的约束冲突错误

示例：

postgres=# create table t1(a int primary key, b int);
CREATE TABLE
postgres=# insert into t1 values(1,1),(2,2);
INSERT 0 2
postgres=# update t1 set a=+1;
ERROR: Kunlun-db: MySQL storage node (1, 1) returned error: 1062, Duplicate entry '2' for key 't1.PRIMARY'.
postgres=# update ignore t1 set a=a+1;
UPDATE 1
postgres=# select * from t1;
a | b
---+---
1 | 1
3 | 2
(2 rows)

END

Copyright © Klustron
Copyright © 2025 Klustron