KunlunDB對MySQL私有DML語法的支援

前言

為了讓MySQL的應用更為便捷地遷移到KunlunDB，我們做了很多相容MySQL的工作。

本篇章主要介紹KunlunDB現在已經支援的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 1postgres -> 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 2postgres -> 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 2postgres -> 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 1postgres -> 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 2postgres -> replace into t1 values(3,4);
INSERT 0 3postgres -> 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 3postgres -> 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 4postgres -> 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的差異：

• 暫不支援指定分割槽表的更新/刪除的順序（注意：臨時表的分割槽表已經支援）。 當然，實際使用中需要嚴格規定更新/刪除順序的場景是極少的，這一限制並不會對KunlunDB的使用者造成困擾。

例如：

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

END