文档首页/ 云数据库 GaussDB/ 与MySQL兼容性参考（集中式）/ MySQL兼容性B模式/ SQL/ DML
更新时间：2024-12-04 GMT+08:00
查看PDF
分享

DML

MySQL数据库功能概述

详细语法说明

GaussDB数据库实现差异

DELETE支持从多个表中删除数据

DELETE

-

DELETE支持ORDER BY和LIMIT

DELETE

-

DELETE支持从指定分区（或子分区）删除数据

DELETE

-

UPDATE支持从多个表中更新数据

UPDATE

-

UPDATE支持ORDER BY和LIMIT

UPDATE

-

SELECT INTO语法兼容

SELECT
  • GaussDB可以使用SELECT INTO根据查询结果创建一个新表，MySQL不支持。
  • GaussDB的SELECT INTO语法不支持将多个查询进行集合运算后的结果作为查询结果。

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);
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"。

  • 位串类型初始值的差异。例如：
    • MySQLBIT类型的初始值为空串''，即： 
      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 BIT(3) NOT NULL);
CREATE TABLE
gaussdb=# REPLACE INTO test VALUES(f1);
ERROR:  null value in column "f1" violates not-null constraint
DETAIL:  Failing row contains (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只能用于表，不能用于视图。
  • 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)
父主题： SQL

相关文档