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

DML

表1 DML语法兼容介绍

概述

详细语法说明

差异

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

DELETE
  • 在多表删除执行过程中，若发现将要删除的元组被其他会话并发修改，会取该条会话匹配中所有元组的最新值重新进行匹配，若依然满足条件再对这条元组进行删除。这个过程中MySQL对所有目标表的删除是一致的，而GaussDB仅会对涉及并发更新的目标表的元组进行重新匹配，可能产生数据不一致。
  • 多表操作语法中目标表和范围表的校验规则与MySQL会存在差异，设置GUC兼容性参数m_format_dev_version为's2'后，检验规则保持一致。

DELETE支持ORDER BY和LIMIT

DELETE

-

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

DELETE

-

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

UPDATE

在多表更新执行过程中，若发现将要更新的元组被其他会话并发修改，会取该条会话匹配中所有元组的最新值重新进行匹配，若依然满足条件再对这条元组进行更新。这个过程中MySQL对所有目标表的更新是一致的，而GaussDB仅会对涉及并发更新的目标表的元组进行重新匹配，可能产生数据不一致。

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语句显示的列名

SELECT
  • 为了使SELECT语句显示的列名与MySQL一致，需要打开列名回显控制开关： 
    SET m_format_behavior_compat_options = 'select_column_name'
  • 不设置此配置项时：
    • SELECT系统函数：回显为系统函数名。
    • SELECT表达式：回显为?column?。
    • SELECT布尔值：回显为bool。
  • 设置此配置项时，列名回显为全部的函数或表达式输入。
    • 对于普通注释，MySQL客户端会将注释忽略，gsql客户端和pymysql不会忽略。
    • 对于如/*!形式开头的注释，MySQL服务端会将其转为可执行语句，M兼容暂不支持识别此类注释，因此作为普通注释处理。
    • 对于包含--且后面无空格的表达式，M兼容不支持将其识别为两个-号，当前识别为注释；MySQL服务端将其识别为两个-号。
    • 如果显示的列名字符串中含有转义字符，只有在设置了m_format_behavior_compat_options为enable_escape_string后才会显示转义后的字符，否则会显示转义字符本身，比如“SELECT"abc\tdef";”M兼容在未开启上述设置时显示为abc\tdef。 
      m_db=# SET m_format_behavior_compat_options='select_column_name,enable_escape_string';
SET
m_db=# SELECT "abc\tdef";
 abc     def 
-------------
 abc     def
(1 row)

m_db=# SET m_format_behavior_compat_options='select_column_name';
SET
m_db=# SELECT "abc\tdef";
 abc\tdef 
----------
 abc\tdef
(1 row)
    • 列名超过63个字符时，会截断后面部分。
    • 表达式最后的部分为注释时，则不会显示最后的注释以及与注释相连的空格。 
      m_db=# SELECT 123        /* 456 */;
 123 
-----
 123
(1 row)
    • 表达式为布尔值时，无论输入大小写，回显为TRUE或FALSE。 
      m_db=# SELECT true;
 TRUE 
------
 t
(1 row)
    • 表达式为null时，无论输入大小写，回显为NULL。 
      m_db=# SELECT null;
 NULL 
------

(1 row)
    • 表达式包含-时，会将全部的输入作为列名输出。 
      m_db=# SELECT (+-+1);
 (+-+1) 
--------
     -1
(1 row)

m_db=# SELECT -true;
 -true 
-------
    -1
(1 row)

m_db=# SELECT -null;
 -null 
-------

(1 row)
  • 当使用pymysql执行SELECT语句，查询的字符串前缀字符不是ASCII字符，且数据库的编码不是UTF-8时，显示的列名会与MySQL存在差异。

SELECT导出文件（into outfile）

SELECT ... INTO OUFILE ...

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

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

UPDATE/INSERT/REPLACE ... SET
  • SELECT语句指定投影列时，MySQL支持“模式名.表别名.列名”的三段式用法，GaussDB不支持。 
    m_db=# CREATE SCHEMA test;
CREATE SCHEMA
m_db=# CREATE TABLE test.t1(a int);
CREATE TABLE
m_db=# SELECT test.alias1.a FROM t1 alias1;
ERROR:  invalid reference to FROM-clause entry for table "alias1"
LINE 1: SELECT test.alias1.a FROM t1 alias1;
               ^
HINT:  There is an entry for table "alias1", but it cannot be referenced from this part of the query.
CONTEXT:  referenced column: a
  • 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，且不允许多次设置同一列，二者存在差异。设置GUC兼容性参数m_format_dev_version为's2'后，仅在单表场景可保持与MySQL行为一致，既支持同一列设置多次，且引用更新后的结果。

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子句中涉及到字符串比较操作时，Field、Collation、Null、Extra、Privileges字段使用字符集utf8mb4、字符序utf8mb4_general_ci，Type、Key、Default、Comment字段使用字符集utf8mb4、字符序utf8mb4_bin。
  • 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在部分情况下存在差异。
  • GaussDB中，查询结果中Table、Index_type、Index_comment字段使用字符集utf8mb4、字符序utf8mb4_bin；字段Key_name，Column_name、Collation、Null、Comment字段使用字符集utf8mb4、字符序utf8mb4_general_ci。

SHOW SESSION VARIABLES

SHOW

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

GaussDB中对查询结果中字段使用LIKE和WHERE进行选择时，排序规则与information_schema.session_variables视图内对应字段保持一致。

SHOW GLOBAL VARIABLES

SHOW

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

GaussDB中对查询结果中字段使用LIKE和WHERE进行选择时，排序规则与information_schema.global_variables视图内对应字段保持一致。

SHOW CHARACTER SET

SHOW

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

GaussDB中对查询结果中字段使用LIKE和WHERE进行选择时，排序规则与information_schema.character_sets视图内对应字段保持一致。

SHOW COLLATION

SHOW

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

GaussDB中对查询结果中字段使用LIKE和WHERE进行选择时，排序规则与information_schema.collations视图内对应字段保持一致。

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存在差异。
  • GaussDB中：查询结果中字段使用字符集utf8mb4、字符序utf8mb4_bin。
  • GaussDB中：LIKE子句中，当目标database是information_schema时，pattern被转为小写再进行匹配。在MySQL 8.0中，当目标database是information_schema时，pattern被转为大写再进行匹配。

SHOW TABLE STATUS

SHOW
  • GaussDB中：该语法展示数据依赖information_schema下的tables视图。MySQL中tables指定的是表。
  • GaussDB中：表和数据库的权限需要分开赋予用户，查询的数据库必须是用户在SHOW SCHEMAS上可以查询到，不能仅仅有表的权限，必须还需要有数据库的权限。MySQL中只要拥有表权限即可访问。
  • GaussDB中：校验逻辑中优先校验Schema是否存在，后校验当前用户是否对Schema具有权限，与MySQL存在差异。
  • GaussDB中：对查询结果中字段使用LIKE和WHERE进行选择时，排序规则与information_schema.tables视图内对应字段保持一致。
  • GaussDB中：LIKE子句中，当目标database是information_schema时，pattern被转为小写再进行匹配。在MySQL 8.0中，当目标database是information_schema时，pattern被转为大写再进行匹配。

GROUP BY 后支持WITH ROLLUP

SELECT

GaussDB支持既有WITH ROLLUP又有ORDER BY写法，MySQL不支持。

支持SQL Mode中ONLY_FULL_GROUP_BY选项

SELECT

SELECT列表中非聚合函数列与GROUP BY字段不一致时，非聚合函数列都需要出现GROUP BY列表或WHERE列表中，且WHERE子句中的列需要等于某一常量时，不报错。其中WHERE子句中的列，GaussDB支持入参数为1的函数列表达式，MySQL不支持函数列表达式。

GaussDB只支持GROUP BY后字段为正整数。

HAVING语法

SELECT

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

使用SELECT查询系统参数、用户变量

SELECT @variable、SELECT @@variable
  • MySQL支持查询用户变量时，不添加具体的变量名（即SELECT @），GaussDB不支持。

    MySQL的行为：

    mysql> SELECT @;
+------------+
| @          |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
    GaussDB的行为： 
    m_db=# SELECT @;
ERROR:  syntax error at or near "@"
LINE 1: SELECT @;
               ^
  • 当查询的系统变量类型为BOOLEAN时，GaussDB中输出结果为t/f，MySQL为1/0，BOOLEAN类型实际映射为TINYINT类型。

子查询

SELECT
  • GaussDB不支持子查询结果包含多列，包含多列时执行会报错；MySQL支持子查询包含多列。

    MySQL的行为：

    mysql> SELECT row(1,2) = (SELECT 1,2);
+-------------------------+
| row(1,2) = (SELECT 1,2) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

    GaussDB的行为：

    m_db=# SELECT row(1,2) = (SELECT 1,2);
ERROR:  subquery must return only one column
LINE 1: SELECT row(1,2) = (SELECT 1,2);                          ^
  • 在开启精度传递的场景下，MySQL对于子查询from子句中返回类型为numeric类型时，如果满足以下条件之一：
    • SELECT子句中存在GROUP BY；
    • SELECT子句中存在HAVING；
    • SELECT子句中存在DISTINCT；
    • SELECT子句中存在LIMIT；
    • SELECT子句中不存在FROM table；
    • SELECT子句中存在用户自定义变量赋值语句；

    将可能发生精度截断，将此类子查询作为下一步运算的中间计算值时，会导致GaussDB的精度结果比MySQL高。

    MySQL的行为：

    mysql> SELECT greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000);
+-----------------------------------------------------------------------------------------+
| greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000) |
+-----------------------------------------------------------------------------------------+
|                                                                 39144.72670800000000000 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    GaussDB的行为：

    m_db=# SELECT greatest((SELECT * FROM (SELECT DISTINCT c2/1.61 FROM t_time) t4), 1.00000000000000000); 
        greatest         
-------------------------
 39144.72670807453416149
(1 row)

    另外，PBE与用户自定义变量一起使用的场景。如果满足上述条件，MySQL会按照30位精度输出；否则，MySQL会按照原数据精度输出，而GaussDB始终按照30位精度输出，例如：

    MySQL的行为：

    -- 满足上述条件：
mysql> SET @var6=12.1234567891;
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE p1 FROM "SELECT * FROM (SELECT @var6) t";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE p1;
+-----------------------------------+
| @var6                             |
+-----------------------------------+
| 12.123456789100000000000000000000 |
+-----------------------------------+
1 row in set (0.00 sec)
-- 不满足上述条件：
mysql> PREPARE p1 FROM "SELECT * FROM (SELECT @var6 FROM (SELECT 1) v1) t";
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE p1;
+---------------+
| @var6         |
+---------------+
| 12.1234567891 |
+---------------+
1 row in set (0.00 sec)

    GaussDB的行为：

    -- 满足上述条件：
m_db=# SET @var6=12.1234567891;
SET
m_db=# PREPARE p1 FROM "SELECT * FROM (SELECT @var6) t";
PREPARE
m_db=# EXECUTE p1;
               @var6               
-----------------------------------
 12.123456789100000000000000000000
(1 row)
-- 不满足上述条件：
m_db=# PREPARE p1 FROM "SELECT * FROM (SELECT @var6 FROM (SELECT 1) v1) t";
PREPARE
m_db=# EXECUTE p1;
               @var6               
-----------------------------------
 12.123456789100000000000000000000
(1 row)

SHOW DATABASES

SHOW

GaussDB中：查询结果中字段使用字符集utf8mb4、字符序utf8mb4_bin。

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视图查询、子查询或UNION涉及NUMERIC转TIME/DATETIME进位差异

SELECT

SELECT部分场景下输出TIME/DATETIME类型结果与MySQL存在差异。

差异场景：视图查询、子查询或UNION；涉及NUMERIC转TIME/DATETIME。

差异行为：GaussDB的SELECT行为统一，NUMERIC转TIME/DATETIME类型时，只对最大精度位（6）作进位处理。MySQL的视图查询、子查询和UNION场景，对实际的结果精度位作进位处理。

MySQL的行为：

-- 简单查询，只对最大精度位6作进位，所以输出11:11:00.00002。
mysql> SELECT maketime(11, 11, 2.2/time '08:30:23.01');
+------------------------------------------+
| maketime(11, 11, 2.2/time '08:30:23.01') |
+------------------------------------------+
| 11:11:00.00002                           |
+------------------------------------------+
1 row in set (0.01 sec)

-- 子查询，对实际的结果精度位作进位，所以输出11:11:00.00003。
mysql> SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1;
+------------------------------------------+
| maketime(11, 11, 2.2/time '08:30:23.01') |
+------------------------------------------+
| 11:11:00.00003                           |
+------------------------------------------+
1 row in set (0.00 sec)

GaussDB的行为：

m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET

-- 简单查询，只对最大精度位6作进位，所以输出11:11:00.00002。
m_db=# SELECT maketime(11, 11, 2.2/time '08:30:23.01');
    maketime
----------------
 11:11:00.00002
(1 row)

-- 子查询，也只对最大精度位6作进位，结果精度是5，所以输出。11:11:00.00002
m_db=# SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1;
    maketime
----------------
 11:11:00.00002
(1 row)

SELECT在数值类型和子查询日期与时间函数的运算处理差异

SELECT

SELECT在数值类型和子查询日期与时间函数的运算场景，GUC参数m_format_behavior_compat_options开启enable_precision_decimal选项，GaussDB会先将函数返回的日期与时间转换为数值类型，然后按照数值类型运算，结果也为数值类型。MySQL在子查询条件查询、分组查询等场景会截断日期与时间函数返回值。

MySQL的行为：

mysql> SELECT 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true GROUP BY 1 HAVING true);
+--------------------------------------------------------------------------------+
| 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true HAVING true) |
+--------------------------------------------------------------------------------+
|                                                                       3168.976 |

GaussDB的行为：

m_db=# SELECT 1.5688 * (SELECT adddate('2020-10-20', interval 1 day) WHERE true GROUP BY 1 HAVING true);
      ?column?      
--------------------
 31691361.744799998
(1 row)

SELECT在嵌套子查询时unsigned类型差异

SELECT

SELECT在嵌套子查询时unsigned类型不会被覆盖，与MySQL 5.7存在差异。

MySQL 5.7的行为：

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t1 (
    ->     c10 real(10, 4) zerofill
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> DESC t1;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type                           | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| c10   | double(10,4) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t1_sub_1 AS SELECT (SELECT * FROM t1);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1_sub_1;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| (SELECT * FROM t1) | double(10,4) | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

GaussDB的行为：

test=# DROP TABLE IF EXISTS t1;
DROP TABLE
test=# CREATE TABLE t1 (
test(#     c10 real(10, 4) ZEROFILL
test(# );
CREATE TABLE
test=# INSERT INTO t1 VALUES(123.45);
INSERT 0 1
test=# DESC t1;
 Field |              Type              | Null | Key | Default | Extra 
-------+--------------------------------+------+-----+---------+-------
 c10   | double(10,4) unsigned zerofill | YES  |     |         | 
(1 row)
test=# CREATE TABLE t1_sub_1 AS SELECT (SELECT * FROM t1);
INSERT 0 1
test=# DESC t1_sub_1;
 Field |         Type          | Null | Key | Default | Extra 
-------+-----------------------+------+-----+---------+-------
 c10   | double(10,4) unsigned | YES  |     |         | 
(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)

不携带ORDER BY子句的UNION、GROUP BY等语句在数据合并或聚合的时候，由于使用执行器算子存在差异，不保证输出数据顺序和MySQL顺序一致。

SELECT

以GROUP BY场景为例，使用hashagg算子时和原始顺序不同，建议需要保证数据顺序的场景添加ORDER BY子句。

-- 数据初始化
DROP TABLE IF EXISTS test;
CREATE TABLE test(id INT);
INSERT INTO test VALUES (1),(2),(3),(4),(5);
-- GaussDB
-- 不开精度传递，id顺序为(1 3 2 4 5)
m_db=# SET m_format_behavior_compat_options= '';
SET
m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2;
 id |        pi
----+-------------------
  1 | 3.141592653589793
  3 | 3.141592653589793
  2 | 3.141592653589793
  4 | 3.141592653589793
  5 | 3.141592653589793
(5 rows)
-- 打开精度传递，由于值变化导致顺序变化，id顺序为(5 4 2 3 1)
m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET
m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2;
 id |    pi
----+----------
  5 | 3.141593
  4 | 3.141593
  2 | 3.141593
  3 | 3.141593
  1 | 3.141593
(5 rows)
-- MySQL，id顺序为原始顺序
mysql> SELECT id, pi() FROM test GROUP BY 1,2;
+------+----------+
| id   | pi()     |
+------+----------+
|    1 | 3.141593 |
|    2 | 3.141593 |
|    3 | 3.141593 |
|    4 | 3.141593 |
|    5 | 3.141593 |
+------+----------+
5 rows in set (0.00 sec)
父主题： SQL

相关文档