Kunlundb's support for MySQL private DML syntax

Preface

In order to make MySQL More convenient migration of applications to KunlunDB, We've done a lot MySQL The job of .

This chapter mainly introduces KunlunDB Now supported MySQL Common private DML grammar , And these grammars and native MySQL The difference of .

One 、 compatible MySQL Of insert ignore grammar

function ： Ignore new element groups that violate unique constraints .

Example ：

postgres -> create table t1(a int primary key, b int not null unique);
CREATE TABLE
#  Violate the only constraint , No insertion 
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

And native MySQL The difference of ：

• Only the uniqueness constraint is ignored , If other constraints are violated （ For example, partition constraints 、 Not null constraint ）, False report .

for example ：

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)

Two 、 compatible MySQL Of INSERT…ONDUPLICATE KEY UPDATE… grammar

function ： insert data ; If a unique constraint is violated , Change to update operation , Update one of the conflicting tuples .

Example ：

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


#  The inserted data conflicts with the two existing tuples , But only one tuple was updated (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

And native MySQL The difference of ：

• Temporarily not supported in ON DUPLICATE KEY UPDATE Used in clauses VALUES() Function to reference the new value , have access to excluded Virtual table instead .

for example ：

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"

• When batch writing multiple new element groups to the temporary table , If there is a uniqueness conflict between new element groups , May be an error （ The root cause is that the temporary table exists in the calculation node , It's not innodb engine ）.

for example ：

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

• The difference in the number of rows returned by the temporary table . Even if the values before and after the update are the same , The number of affected rows returned by the temporary table is still greater than 0.

for example ：

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

3、 ... and 、 compatible mysql Of replace into grammar

function ： Insert tuples ; If there are conflicting old tuples , Delete all old tuples that conflict with it .

Example ：

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)

And native MySQL The difference of ：

• When batch writing multiple new element groups to the temporary table , If there is a uniqueness conflict between new element groups , May be an error （ The root cause is that the temporary table exists in the calculation node , It's not innodb engine ）.

for example ：

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.

Four 、 compatible MySQL Of update/delete…order by…limit… grammar

function ： Specify updates / The order and number of tuples deleted .

Example ：

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

#  Orderly update of non partitioned tables 
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

#  Specifies the total amount of partition table deletion 
postgres -> delete from t1  limit 4 returning *;
 a | b
---+---
 0 |
 1 |
 2 |
 3 |
(4rows)

DELETE 4

And native MySQL The difference of ：

• The update of the specified partition table is not supported at the moment / Order of deletion （ Be careful ： Partition table of temporary table already supports ）. Of course , In actual use, it is necessary to strictly regulate and update / The scenario of deleting order is very few , This restriction will not affect KunlunDB Of users .

for example ：

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

#  Cannot specify partition table deletion order 
postgres -> delete from t1 order by a limit 4 returning *;
ERROR:  Kunlun-db: Cannot push down plan
postgres ->

END

Kunlun database is a HTAP NewSQL Distributed database management system , It can meet the all-round needs of users for the storage, management and utilization of massive relational data .
Application developers and DBA The experience of using Kunlun database is similar to that of single machine MySQL And stand alone PostgreSQL almost the same , Because first of all, Kunlun database supports PostgreSQL and MySQL Double agreement , Support standards SQL:2011 Of
DML Grammar and function and PostgreSQL and MySQL To the standard
SQL An extension of . meanwhile , Kunlun database cluster supports horizontal elastic expansion , Automatic data splitting , Distributed transaction processing and distributed query processing , Robust fault tolerance , Improve the intuitive monitoring, analysis and alarm capability , Cluster data backup and recovery
frequently-used DBA Data management and operation . All these functions do not require any coding work on the application system side , No need DBA Artificial intervention , Non-stop service does not affect the normal operation of business .
Kunlun database has a comprehensive OLAP
Data analysis capabilities , Passed TPC-H and TPC-DS Standard test set , It can analyze the latest business data in real time , Help users discover the value of data . Kunlun database supports the deployment of public cloud and private cloud environments , It can be done with docker,k8s Wait for cloud infrastructure to work seamlessly , You can easily build cloud database services .
Please visit http://www.zettadb.com/ Get more information and download Kunlun database software 、 Documents and materials . KunlunDB The project is open source
【GitHub：】 https://github.com/zettadb 【Gitee：】
https://gitee.com/zettadb