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

DML

表1 DML语法兼容介绍

概述

详细语法说明

差异

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

DELETE

-

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

    在严格模式下，则报错Incorrect Date/Time/Datetime/Timestamp/Year value。

SELECT支持指定多分区查询

SELECT

-

UPDATE支持指定多分区更新

UPDATE

-

LOAD DATA导入数据功能

LOAD DATA

在使用LOAD DATA导入数据功能时，GaussDB与MySQL相比有如下差异：

  • LOAD DATA语法执行结果与M*严格模式一致，宽松模式暂未适配。
  • IGNORE与LOCAL参数功能仅为当导入数据与表中数据存在冲突时，忽略当前冲突行数据功能和当文件中字段数小于指定表中列数时自动为其余列填充默认值功能，其余功能暂未适配。
  • [(col_name_or_user_var [, col_name_or_user_var] ...)]指定列参数不支持重复指定列。
  • [FIELDS TERMINATED BY 'string']指定换行符不能与[LINES TERMINATED BY 'string']分隔符相同。
  • 执行LOAD DATA语法写入表中的数据若无法转换为表中数据类型格式时报错。
  • LOAD DATA SET表达式中不支持指定列名计算。
  • LOAD DATA只能用于表，不能用于视图。
  • Windows下的文件与Linux环境下文件默认换行符存在差异，LOAD DATA无法识别此场景会报错，建议用户导入时检查导入文件行尾换行符。
  • MySQL在指定LOCAL参数时支持从客户端环境导入数据，不指定LOCAL时则从服务端环境导入数据。GaussDB不设置GUC参数m_format_behavior_compat_options值时，LOAD DATA无论是否指定LOCAL参数，仅支持从服务端导入数据；GaussDB设置GUC参数m_format_behavior_compat_options值包含enable_load_data_remote_transmission后，LOAD DATA LOCAL参数行为与MySQL一致。

INSERT支持VALUES引用列语法

INSERT INTO tabname VALUES(1,2,3) ON DUPLICATE KEY UPDATE b = VALUES(column_name)

GaussDB的ON DUPLICATE KEY UPDATE子句中的VALUES()不支持表名.列名格式，MySQL支持。

LIMIT限制差异

DELETE、SELECT、UPDATE

各个语句的limit子项与MySQL的limit项当前存在差异。

GaussDB中limit参数最大值为BIG INT类型限制（超过9223372036854775807报错）。在MySQL中，limit最大值为unsigned LONGLONG类型限制（超过18446744073709551615 报错）。

limit可以设置小数值，实际执行时四舍五入。MySQL不能取小数。

GaussDB的DELETE语句中，不允许limit 0。MySQL在DELETE语句中允许limit 0。

反斜杠(\)用法差异

INSERT

反斜杠（\）的用法在GaussDB和MySQL中都可以由参数控制但当前默认用法不同：

MySQL中使用参数NO_BACKSLASH_ESCAPES控制字符串和标识符中的反斜杠（\）被解析为普通字符还是转义字符，默认反斜杠字符（\）作为字符串和标识符中的转义字符。使用“set sql_mode='NO_BACKSLASH_ESCAPES';”语句可以禁用反斜杠字符（\）作为字符串和标识符中的转义字符。

GaussDB中使用参数standard_conforming_strings控制字符串和标识符中的反斜杠（\）被解析为普通字符还是转义字符。默认值为on，在普通字符串文本中按照SQL标准把反斜杠（\）当普通文本。使用“set standard_conforming_strings=off;”语句将反斜杠字符（\）作为字符串和标识符中的转义字符。

插入值少于字段数目时，MySQL报错，GaussDB补充空值。

INSERT

GaussDB不指定列的列表时，如果插入值少于字段数目，默认按建表时的字段顺序赋值。字段上有非空约束时报错，没有非空约束时，如果指定了默认值则缺省部分补充默认值，若未指定默认值则补充空。

ORDER BY中排序的列必须包括在结果集的列中。

SELECT

在GaussDB中，在与GROUP BY子句一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。在与DISTINCT关键字一起使用的情况下，ORDER BY中排序的列必须包括在SELECT语句所检索的结果集的列中。

不允许对约束字段用 ON DUPLICATE KEY UPDATE 进行修改。

INSERT

-

SELECT结果允许存在重复列名。

SELECT

-

NATURAL JOIN与MySQL有差异。

SELECT

在GaussDB中，NATURAL [ [LEFT | RIGHT] OUTER] JOIN允许不指定LEFT | RIGHT，不指定时NATURAL OUTER JOIN为NATURAL JOIN。允许连续使用多次JOIN。

外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错。

UPDATE/DELETE

外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错，MySQL成功。

nature join和using兼容。

SELECT
  • GaussDB join的顺序严格按照从左往右，MySQL可能会调整顺序。
  • GaussDB和MySQL在natural join与using时均不允许左表或右表参与join的字段出现歧义（一般由左或右临时表中重名字段造成）。因为两者join的顺序有差别，故行为上可能有差别。
    • GaussDB的行为： 
      m_regression=# CREATE TABLE t1(a int,b int);
CREATE TABLE
m_regression=# CREATE TABLE t2(a int,b int);
CREATE TABLE
m_regression=# CREATE TABLE t3(a int,b int);
CREATE TABLE
m_regression=# SELECT * FROM t1 JOIN t2;
 a | b | a | b 
---+---+---+---
(0 rows)
m_regression=# SELECT * FROM t1 JOIN t2 natural join t3; -- failed, 因为:列a,b在t1 join t2 得到的临时表中存在重复，故nature join存在歧义。
ERROR:  common column name "a" appears more than once in left table
    • MySQL的行为： 
      mysql> SELECT * FROM t1 JOIN t2 NATURAL JOIN t3;
Empty set (0.00 sec)
mysql> SELECT * FROM (t1 join t2) NATURAL JOIN t3;
ERROR 1052 (23000): Column 'a' in from clause is ambiguous

with clause兼容MySQL 8.0版本

SELECT 、INSERT、UPDATE、DELETE

-

join兼容

SELECT

GaussDB join不支持使用逗号“,”的连接方式，MySQL支持。

GaussDB不支持use index for join。

SELECT语句中列表达式为函数表达式、算数表达式等情形时，查询结果显示的列名为 ?column?

SELECT

GaussDB SELECT语句中列表达式为函数表达式、算数表达式等情形时，查询结果显示的列名为 ?column?，MySQL为对应表达式。

SELECT导出文件（into outfile）

SELECT ... INTO OUFILE ...

SELECT INTO OUTFILE语法，导出文件中FLOAT、DOUBLE、REAL类型的值显示精度和MySQL存在差异，不影响COPY导入和导入后的值。

UPDATE/INSERT/REPLACE ... SET指定模式名、表名

UPDATE/INSERT/REPLACE ... SET

UPDATE/REPLACE SET中，MySQL的三段式用法为database.table.column；GaussDB的三段式用法为table.column.filed，其中filed为指定复合类型中的属性。二者存在差异。

INSERT ... SET中，MySQL支持使用column、table.column和database.table.column；GaussDB只支持使用column，不支持使用table.column和database.table.column，二者存在差异。

UPDATE SET执行顺序与MySQL存在差异

UPDATE ... SET

MySQL中，UPDATE SET的顺序是从前往后依次UPDATE，前面UPDATE的结果会影响后面的结果，且允许多次设置同一列；GaussDB中为先取出原来的所有相关的数据，再一次性UPDATE，且不允许多次设置同一列，二者存在差异。

IGNORE特性

UPDATE/DELETE/INSERT

MySQL数据库和GaussDB执行过程的差异，因此产生的WARNING条数和WARNING信息可能存在不同。

SHOW COLUMNS语法

SHOW
  • 用户权限验证与MySQL存在差异。
    • GaussDB中需要拥有指定表所在Schema的USAGE权限，同时还需要拥有指定表的任意表级权限或列级权限，仅显示拥有SELECT、INSERT、UPDATE、REFERENCES和COMMENT权限的列信息。
    • MySQL中需要拥有指定表的任意表级权限或列级权限，仅显示拥有SELECT、INSERT、UPDATE、REFERENCES和COMMENT权限的列信息。
  • LIKE和WHERE子句中涉及到字符串比较操作时，排序规则与MySQL存在差异。
    • MySQL中使用utf8_general_ci。
    • GaussDB中排序规则使用当前客户端的collation_connection。
      GaussDB中建议用户在WHERE子句中，不要对返回字段以外的列进行选择，否则可能会出现非预期的报错。 
      -- 预期报错
m_db=# SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
ERROR:  Column "b" does not exist.
LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
                                         ^

-- 非预期报错
m_db=# SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
ERROR:  input of anonymous composite types is not implemented
LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
                                             ^

SHOW CREATE DATABASE语法

SHOW

用户权限验证与MySQL存在差异。

  • GaussDB中需要拥有指定Schema的USAGE权限。
  • MySQL中需要拥有任意库级权限（除GRANT OPTION和USAGE）、任意表级权限（除GRANT OPTION）或任意列级权限。

SHOW CREATE TABLE语法

SHOW
  • 用户权限验证与MySQL存在差异。
    • GaussDB中需要拥有指定表所在Schema的USAGE权限和指定表的任意表级权限。
    • MySQL中需要拥有指定表的任意表级权限（除GRANT OPTION）。
  • 返回的建表语句与MySQL存在差异。
    • GaussDB中索引以CREATE INDEX语句的形式返回。MySQL中表的索引在CREATE TABLE语句中返回。主要因为GaussDB中CREATE INDEX语法支持的可选参数范围与CREATE TABLE语法中创建索引不同，因此某些索引无法在CREATE TABLE语句中创建。
    • GaussDB中CREATE TABLE语法的ENGINE和ROW_FORMAT选项仅做了语法适配，实际不生效，因此在返回的建表语句中不予显示。
  • 设置兼容性参数m_format_dev_version为's2'后，返回的建表语句才兼容MySQL。兼容的内容包括：列注释位置变更、表注释位置变更、全局临时表ON COMMIT选项位置变更、主键与唯一约束位置变更、主键与唯一约束中的USING INDEX TABLESPACE选项不再显示以及索引注释位置变更。

SHOW CREATE VIEW语法

SHOW
  • 用户权限验证与MySQL存在差异。
    • GaussDB中需要拥有指定视图所在Schema的USAGE权限和指定视图的任意表级权限。
    • MySQL中需要拥有指定视图的表级SELECT和表级SHOW VIEW权限。
  • 返回的视图创建语句与MySQL存在差异。以SELECT * FROM tbl_name形式创建的视图，GaussDB中*不会被展开，而MySQL中会展开。
  • 返回结果中的character_set_client和collation_connection字段与MySQL存在差异。
    • MySQL中显示视图创建时系统变量character_set_client和collation_connection的会话值
    • GaussDB中未记录相关元数据，显示为NULL。

SHOW PROCESSLIST语法

SHOW

GaussDB中该命令的查询结果中的字段内容和大小写与information_schema.processlist视图内字段内容与大小写保持一致，MySQL中可能存在差异。

  • GaussDB中用户只能访问自己的线程信息，拥有SYSADMIN权限的用户可以访问所有用户的线程信息。
  • MySQL中用户只能访问自己的线程信息，拥有PROCESS权限的用户可以访问所有用户的线程信息。

SHOW [STORAGE] ENGINES

SHOW

GaussDB中该命令的查询结果中的字段内容和大小写与information_schema.engines视图内字段内容与大小写保持一致，MySQL中可能存在差异。因为MySQL与GaussDB的存储引擎不同，所以该指令查询的结果不同。

SHOW [SESSION] STATUS

SHOW

GaussDB中该命令的查询结果中的字段内容和大小写与information_schema.session_status视图内字段内容与大小写保持一致，MySQL中可能存在差异。GaussDB中当前仅支持Threads_connected和Uptime。

SHOW [GLOBAL] STATUS

SHOW

GaussDB中该命令的查询结果中的字段内容和大小写与information_schema.global_status视图内字段内容与大小写保持一致，MySQL中可能存在差异。GaussDB中当前仅支持Threads_connected和Uptime。

SHOW INDEX

SHOW
  • 用户权限验证与MySQL存在差异。
    • GaussDB中需要拥有指定SCHEMA的USAGE权限和指定表的任意表级权限或者任意列级权限。
    • MySQL中需要拥有指定表的任意表级权限（除GRANT OPTION）或者任意列级权限。
  • GaussDB中临时表存储于独立的临时Schema中，在使用FROM或IN db_name条件来展示指定临时表索引信息时，须指明db_name为临时表所在的Schema才能展示临时表索引信息，否则会提示不存在该临时表，这一点和Mysql在部分情况下存在差异。

SHOW SESSION VARIABLES

SHOW

GaussDB中查询结果中字段内容及大小写与information_schema.session_variables视图内字段内容及大小写保持一致，与MySQL可能存在差异。

SHOW GLOBAL VARIABLES

SHOW

GaussDB中查询结果中字段内容及大小写与information_schema.global_variables视图内字段内容及大小写保持一致，与MySQL可能存在差异。

SHOW CHARACTER SET

SHOW

GaussDB中查询结果中字段内容及大小写与information_schema.character_sets视图内字段内容及大小写保持一致，与MySQL可能存在差异。

SHOW COLLATION

SHOW

GaussDB中查询结果中字段内容及大小写与information_schema.collations视图内字段内容及大小写保持一致，与MySQL可能存在差异。

EXCEPT语法

SELECT

-

SELECT支持STRAIGHT_JOIN语法

SELECT

GaussDB中多表关联JOIN场景下生成的执行计划，与MySQL可能存在差异。

SHOW TABLES

SHOW
  • LIKE行为存在差异，具体请参见操作符章节的“LIKE”。
  • WHERE表达式行为存在差异，具体行为请参见GaussDB数据库的“WHERE表达式”。
  • GaussDB中：表和数据库的权限需要分开赋予用户，查询的数据库必须是用户在SHOW SCHEMAS上可以查询到，不能仅仅有表的权限，必须还需要有数据库的权限。MySQL中只要拥有表权限即可访问。
  • GaussDB中：校验逻辑中优先校验Schema是否存在，后校验当前用户是否对Schema具有权限，与MySQL存在差异。

SHOW TABLE STATUS

SHOW
  • GaussDB中：该语法展示数据依赖information_schema下的tables视图。MySQL中tables指定的是表。
  • GaussDB中：表和数据库的权限需要分开赋予用户，查询的数据库必须是用户在SHOW SCHEMAS上可以查询到，不能仅仅有表的权限，必须还需要有数据库的权限。MySQL中只要拥有表权限即可访问。
  • GaussDB中：校验逻辑中优先校验Schema是否存在，后校验当前用户是否对Schema具有权限，与MySQL存在差异。

HAVING语法

SELECT

GaussDB的HAVING必须且只能引用GROUP BY子句中的列或聚合函数中使用的列。MySQL支持对此行为的扩展，并允许HAVING引用列表中的SELECT列和外部子查询中的列。

SELECT后跟行表达式

SELECT

MySQL不支持SELECT后跟行表达式，GaussDB支持SELECT后跟行表达式。

MySQL的行为：

mysql> SELECT row(1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

GaussDB的行为：

m_db=# SELECT row(1,2);
 row(1,2) 
----------
 (1,2)
(1 row)

SELECT FOR SHRAE/FOR UPDATE/LOCK IN SHRAE MODE

SELECT
  • GaussDB不支持FOR SHARE/FOR UPDATE/LOCK IN SHARE MODE子句和UNION/EXCEPT/DISTINCT/GROUP BY/HAVING子句一起使用，MySQL 5.7部分支持（FOR SHARE/EXCEPT语法不支持），MySQL 8.0均支持。
  • 当将锁子句与LEFT/RIGHT [OUTER] JOIN子句连用时，LEFT JOIN不支持给右表上锁，RIGHT JOIN不支持给左表上锁；MySQL可以给JOIN两侧的表同时上锁。

SELECT语法支持范围

SELECT
  • GaussDB指定from子句中的表别名时，支持带字段名称。MySQL 5.7不支持指定表别名时带字段名称，MySQL 8.0仅支持给子查询指定表别名时带字段名称。 
    -- GaussDB
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(1,2);
INSERT 0 1
m_db=# SELECT * FROM t1 t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

m_db=# SELECT * FROM (SELECT * FROM t1) t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

-- MySQL 5.7
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1

-- MySQL 8.0
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t1 t2(a, b);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
  • 当查询语句不带from子句时，GaussDB支持带where子句，与MySQL 8.0保持一致，MySQL 5.7不支持。 
    -- GaussDB
m_db=# SELECT 1 WHERE true;
 1 
---
 1
(1 row)

-- MySQL 5.7
mysql> SELECT 1 WHERE true;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where true' at line 1

-- MySQL 8.0
mysql> SELECT 1 WHERE true;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
父主题： SQL