# DML 表1DML语法兼容介绍 | 概述 | 详细语法说明 | 差异 | |:---|:---|:---| | DELETE支持ORDER BY和LIMIT | DELETE | - | | UPDATE支持ORDER BY和LIMIT | UPDATE | - | | REPLACE INTO语法兼容 | REPLACE | - 时间类型初始值的差异。例如: - MySQL不受严格模式和宽松模式的影响，可向表中插入时间0值，即： ``` mysql> CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO test VALUES(f1, f2, f3); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test; +---------------------+---------------------+------------+ | f1 | f2 | f3 | +---------------------+---------------------+------------+ | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 | +---------------------+---------------------+------------+ 1 row in set (0.00 sec) ``` - GaussDB在宽松模式下才可以成功插入时间0值，即 ``` gaussdb=# SET b_format_version = '5.7'; SET gaussdb=# SET b_format_dev_version = 's1'; SET gaussdb=# SET sql_mode = ''; SET gaussdb=# CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL) DISTRIBUTE BY HASH(f1); CREATE TABLE gaussdb=# REPLACE INTO test VALUES(f1, f2, f3); REPLACE 0 1 gaussdb=# SELECT * FROM test; f1 | f2 | f3 ---------------------+---------------------+------------ 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 (1 row) ``` 在严格模式下，则报错date/time field value out of range: "0000-00-00 00:00:00"。 - BIT类型在有NOT NULL时初始值的差异。例如： - MySQL BIT类型的初始值为空串''，即： ``` mysql> CREATE TABLE test(f1 BIT(3) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> REPLACE INTO test VALUES(f1); Query OK, 1 row affected (0.00 sec) mysql> SELECT f1, f1 IS NULL FROM test; +----+------------+ | f1 | f1 is null | +----+------------+ | | 0 | | | 0 | +----+------------+ 2 rows in set (0.00 sec) ``` - GaussDB BIT类型的初始值为NULL，则报错。 ``` gaussdb=# CREATE TABLE test(f1 int, f2 BIT(3) NOT NULL) DISTRIBUTE BY HASH(f1); CREATE TABLE gaussdb=# REPLACE INTO test VALUES(1, f2); ERROR: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (1, null). ``` | | SELECT支持指定多分区查询 | SELECT | - | | UPDATE支持指定多分区更新 | UPDATE | - | | LOAD DATA导入数据功能 | LOAD DATA | - LOAD DATA语法执行结果与MySQL严格模式一致，宽松模式暂未适配。 - IGNORE与LOCAL参数功能仅为当导入数据与表中数据存在冲突时，忽略当前冲突行数据功能和当文件中字段数小于指定表中列数时自动为其余列填充默认值功能，其余功能暂未适配。 - 指定LOCAL关键字，且文件路径为相对路径时，文件从客户端目录下搜索；不指定LOCAL关键字，且文件路径为相对路径时，文件从数据目录下搜索。 - 语法中指定分隔符，转义字符，分行符等符号时，若指定为单引号，将导致词法解析错误。 - \[(col_name_or_user_var \[, col_name_or_user_var\] ...)\]指定列参数不支持重复指定列。 - \[FIELDS TERMINATED BY 'string'\]指定换行符不能与\[LINES TERMINATED BY 'string'\]分隔符相同。 - 执行LOAD DATA语法写入表中的数据若无法转换为表中数据类型格式时报错。 - 指定列时，仅支持指定为列名，不支持指定为用户变量。 - LOAD DATA SET表达式中不支持指定列名计算。 - 若set表达式返回值类型与对应列类型之间不存在隐式转换函数则报错。 - LOAD DATA不支持INSERT、DELETE触发器。 - LOAD DATA只能用于表，不能用于视图。 - Windows下的文件与Linux环境下文件默认换行符存在差异，LOAD DATA无法识别此场景会报错，建议用户导入时检查导入文件行结尾的换行符。 | | INSERT IGNORE兼容 | INSERT IGNORE | - GaussDB会返回降级后的错误信息，MySQL则会将降级后的错误信息记录到错误堆栈中，然后调用show warnings;命令查看。例如： - 时间类型的差异。例如： - GaussDB中date、datetime、timestamp默认零值。 ``` gaussdb=# CREATE TABLE test(f1 DATE NOT NULL, f2 DATETIME NOT NULL, f3 TIMESTAMP NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL, NULL, NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). WARNING: null value in column "f3" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 | f2 | f3 ------------+---------------------+--------------------- 1970-01-01 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 (1 row) ``` - MySQL中date、datetime、timestamp默认零值。 ``` mysql> CREATE TABLE test(f1 DATE NOT NULL, f2 DATETIME NOT NULL, f3 TIMESTAMP NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL, NULL, NULL); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+----------------------------+ | Level | Code | Message | +---------+------+----------------------------+ | Warning | 1048 | Column 'f1' cannot be null | | Warning | 1048 | Column 'f2' cannot be null | | Warning | 1048 | Column 'f3' cannot be null | +---------+------+----------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM test; +------------+---------------------+---------------------+ | f1 | f2 | f3 | +------------+---------------------+---------------------+ | 0000-00-00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | +------------+---------------------+---------------------+ 1 row in set (0.00 sec) ``` - 由于GaussDB不支持MySQL的bit类型，因此忽略bit类型NOT NULL约束和插入的bit类型长度与定义不同的场景下不支持INSERT IGNORE错误降级。 - GaussDB中bit类型 ``` gaussdb=# CREATE TABLE test(f1 BIT(10) NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL); ERROR: Un-support feature DETAIL: ignore null for insert statement is not supported in column f1. gaussdb=# INSERT IGNORE INTO test VALUES('1010'); ERROR: bit string length 4 does not match type bit(10) CONTEXT: referenced column: f1 ``` - MySQL中bit类型 ``` mysql> CREATE TABLE test(f1 BIT(10) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> INSERT IGNORE INTO test VALUES('1010'); Query OK, 1 row affected, 1 warning (0.01 sec) ``` - MySQL数据库时间类型指定精度时，插入时间零值会显示精度，GaussDB则不显示，例如： - GaussDB指定时间精度 ``` gaussdb=# CREATE TABLE test(f1 TIME(3) NOT NULL, f2 DATETIME(3) NOT NULL, f3 TIMESTAMP(3) NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL,NULL,NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null, null). WARNING: null value in column "f3" violates not-null constraint DETAIL: Failing row contains (null, null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 | f2 | f3 ----------+---------------------+--------------------- 00:00:00 | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 (1 row) ``` - MySQL指定时间精度 ``` mysql> CREATE TABLE test(f1 TIME(3) NOT NULL, f2 DATETIME(3) NOT NULL, f3 TIMESTAMP(3) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL,NULL,NULL); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> SELECT * FROM test; +--------------+-------------------------+-------------------------+ | f1 | f2 | f3 | +--------------+-------------------------+-------------------------+ | 00:00:00.000 | 0000-00-00 00:00:00.000 | 0000-00-00 00:00:00.000 | +--------------+-------------------------+-------------------------+ 1 row in set (0.00 sec) ``` - 由于MySQL数据库和GaussDB执行过程的差异，因此，产生的warnings条数可能不同，例如： - GaussDB产生的warnings条数 ``` gaussdb=# CREATE TABLE test(f1 INT, f2 INT not null); CREATE TABLE gaussdb=# INSERT INTO test VALUES(1,0),(3,0),(5,0); INSERT 0 3 gaussdb=# INSERT IGNORE INTO test SELECT f1+1, f1/f2 FROM test; WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (2, null). WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (4, null). WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (6, null). INSERT 0 3 ``` - MySQL产生的warnings条数 ``` mysql> CREATE TABLE test(f1 INT, f2 INT not null); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test VALUES(1,0),(3,0),(5,0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT IGNORE INTO test SELECT f1+1, f1/f2 FROM test; Query OK, 3 rows affected, 4 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 4 ``` - MySQL数据库和GaussDB INSERT IGNORE在触发器中的差异，例如： - GaussDB触发器中使用INSERT IGNORE ``` gaussdb=# CREATE TABLE test1(f1 INT NOT NULL); CREATE TABLE gaussdb=# CREATE TABLE test2(f1 INT); CREATE TABLE gaussdb=# CREATE OR REPLACE FUNCTION trig_test() RETURNS TRIGGER AS $$ gaussdb$# BEGIN gaussdb$# INSERT IGNORE INTO test1 VALUES(NULL); gaussdb$# RETURN NEW; gaussdb$# END; gaussdb$# $$ LANGUAGE plpgsql; CREATE FUNCTION gaussdb=# CREATE TRIGGER trig2 BEFORE INSERT ON test2 FOR EACH ROW EXECUTE PROCEDURE trig_test(); CREATE TRIGGER gaussdb=# INSERT INTO test2 VALUES(NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: SQL statement "INSERT IGNORE INTO test1 VALUES(NULL)" PL/pgSQL function trig_test() line 3 at SQL statement INSERT 0 1 gaussdb=# SELECT * FROM test1; f1 ---- 0 (1 rows) gaussdb=# SELECT * FROM test2; f1 ---- (1 rows) ``` - MySQL触发器中使用INSERT IGNORE ``` mysql> CREATE TABLE test1(f1 INT NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE test2(f1 INT); Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER || mysql> CREATE TRIGGER trig2 BEFORE INSERT ON test2 FOR EACH ROW -> BEGIN -> INSERT IGNORE into test1 values(NULL); -> END|| Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> INSERT INTO test2 VALUES(NULL); ERROR 1048 (23000): Column 'f1' cannot be null mysql> INSERT IGNORE INTO test2 VALUES(NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test1; +----+ | f1 | +----+ | 0 | +----+ 1 row in set (0.00 sec) mysql> SELECT * FROM test2; +------+ | f1 | +------+ | NULL | +------+ 1 row in set (0.00 sec) ``` - GaussDB的bool、serial的实现机制与MySQL不同，因此其默认零值与MySQL不同，例如： - GaussDB的行为 ``` gaussdb=# CREATE TABLE test(f1 SERIAL, f2 BOOL NOT NULL); NOTICE: CREATE TABLE will create implicit sequence "test_f1_seq" for serial column "test.f1" CREATE TABLE gaussdb=# INSERT IGNORE INTO test values(NULL,NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 | f2 ----+---- 0 | f (1 row) ``` - MySQL的行为 ``` mysql> CREATE TABLE test(f1 SERIAL, f2 BOOL NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test values(NULL,NULL); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT * FROM test; +----+----+ | f1 | f2 | +----+----+ | 1 | 0 | +----+----+ 1 row in set (0.00 sec) ``` |