# DML 表1DML语法兼容介绍 | 概述 | 详细语法说明 | 差异 | |:---|:---|:---| | DELETE支持从多个表中删除数据 | DELETE | - 在多表删除执行过程中，若发现将要删除的元组被其他会话并发修改，会取该条会话匹配中所有元组的最新值重新进行匹配，若依然满足条件再对这条元组进行删除。这个过程中MySQL对所有目标表的删除是一致的，而GaussDB仅会对涉及并发更新的目标表的元组进行重新匹配，可能产生数据不一致。 - 多表操作语法中目标表和范围表的校验规则与MySQL会存在差异，设置GUC兼容性参数m_format_dev_version为's2'后，检验规则保持一致。 | | UPDATE支持从多个表中更新数据 | UPDATE | 在多表更新执行过程中，若发现将要更新的元组被其他会话并发修改，会取该条会话匹配中所有元组的最新值重新进行匹配，若依然满足条件再对这条元组进行更新。这个过程中MySQL对所有目标表的更新是一致的，而GaussDB仅会对涉及并发更新的目标表的元组进行重新匹配，可能产生数据不一致。 | | 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) ``` 在严格模式下，则报错： ``` The date, time, datetime, timestamp, or year is incorrect. ``` | | LOAD DATA导入数据功能 | LOAD DATA | 在使用LOAD DATA导入数据功能时，GaussDB与MySQL相比有如下差异： - LOAD DATA语法执行结果与MySQL严格模式一致，宽松模式暂未适配。 - 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无法识别此场景会报错，建议用户导入时检查导入文件行尾换行符。 - GaussDB不设置GUC参数m_format_behavior_compat_options值时，LOAD DATA无论是否指定LOCAL参数都仅支持从服务端导入数据；MySQL在指定LOCAL参数时支持从客户端环境导入数据，不指定LOCAL时则从服务端环境导入数据；GaussDB设置GUC参数值包含enable_load_data_remote_transmission后，LOAD DATA LOCAL参数行为与MySQL一致。 | | LIMIT子句差异 | DELETE、SELECT、UPDATE | 各个语句的limit子项与MySQL的limit项当前存在差异。 GaussDB中limit参数最大值为BIG INT类型限制（超过9223372036854775807报错）。在MySQL中，limit最大值为unsigned LONGLONG类型限制（超过18446744073709551615 报错）。 limit可以设置小数值，实际执行时四舍五入。MySQL不能取小数。 | | 反斜杠(\\)用法差异 | 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语句所检索的结果集的列中。 | | 外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错 | UPDATE/DELETE | 外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错，MySQL成功。 | | NATURAL JOIN语法 | SELECT | - 在GaussDB中，NATURAL \[ \[LEFT \| RIGHT\] OUTER\] JOIN允许不指定LEFT \| RIGHT，不指定时NATURAL OUTER JOIN为NATURAL JOIN。允许连续使用多次JOIN。 - 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 ``` | | JOIN语法 | SELECT | GaussDB JOIN不支持使用逗号","的连接方式，MySQL支持。 GaussDB不支持USE INDEX FOR JOIN。 GaussDB中STRAIGHT_JOIN多表关联场景下生成的执行计划，与MySQL可能存在差异。 | | SELECT语句显示的列名 | SELECT | - 为了使SELECT语句显示的列名与MySQL一致，GaussDB需要打开列名回显控制开关： ``` SET m_format_behavior_compat_options = 'select_column_name' ``` - GaussDB不设置此配置项时： - SELECT系统函数：回显为系统函数名。 - SELECT表达式：回显为?column?。 - SELECT布尔值：回显为bool。 - GaussDB设置此配置项时，列名回显为全部的函数或表达式输入。 - 对于普通注释，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导入和导入后的值。 | | SELECT/UPDATE/INSERT/REPLACE指定模式名、表名 | SELECT/UPDATE/INSERT/REPLACE | - 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.field，其中field为指定复合类型中的属性。 - INSERT ... SET中，MySQL支持使用column、table.column和database.table.column；GaussDB只支持使用column，不支持使用table.column和database.table.column。 - INSERT ... SET中，MySQL支持SET子句中等式右边引用列名以及包含列名的表达式，GaussDB不支持： - GaussDB的行为： ``` m_db=# CREATE TABLE t2 (a int default 3, b int default 5); CREATE TABLE m_db=# INSERT INTO t2 SET a = b + 1; ERROR: Column "b" does not exist. LINE 1: INSERT INTO t2 SET a = b + 1; ^ HINT: There is a column named "b" in table "t2", but it cannot be referenced from this part of the query. m_db=# INSERT INTO t2 SET a = b + 1, b = 0; ERROR: Column "b" does not exist. LINE 1: INSERT INTO t2 SET a = b + 1, b = 0; ^ HINT: There is a column named "b" in table "t2", but it cannot be referenced from this part of the query. m_db=# INSERT INTO t2 SET b = 0, a = b + 1; ERROR: Column "b" does not exist. LINE 1: INSERT INTO t2 SET b = 0, a = b + 1; ^ HINT: There is a column named "b" in table "t2", but it cannot be referenced from this part of the query. m_db=# INSERT INTO t2 SET a = a + 1; ERROR: Column "a" does not exist. LINE 1: INSERT INTO t2 SET a = a + 1; ^ HINT: There is a column named "a" in table "t2", but it cannot be referenced from this part of the query. m_db=# DROP TABLE t2; DROP TABLE ``` - MySQL的行为： ``` mysql> CREATE TABLE t2 (a int default 3, b int default 5); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO t2 SET a = b + 1; Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM t2; +------+------+ | a | b | +------+------+ | 6 | 5 | +------+------+ 1 row in set (0.00 sec) mysql> INSERT INTO t2 SET a = b + 1, b = 0; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | a | b | +------+------+ | 6 | 5 | | 6 | 0 | +------+------+ 2 rows in set (0.00 sec) mysql> INSERT INTO t2 SET b = 0, a = b + 1; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | a | b | +------+------+ | 6 | 5 | | 6 | 0 | | 1 | 0 | +------+------+ 3 rows in set (0.00 sec) mysql> INSERT INTO t2 SET a = a + 1; Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM t2; +------+------+ | a | b | +------+------+ | 6 | 5 | | 6 | 0 | | 1 | 0 | | 4 | 5 | +------+------+ 4 rows in set (0.00 sec) mysql> DROP TABLE t2; Query OK, 4 rows affected (0.40 sec) ``` | | 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视图内对应字段保持一致。 | | SHOW TABLES | SHOW | - LIKE行为存在差异，具体请参见[操作符](https://support.huaweicloud.com/usermanual-ugo/zh-cn_topic_0000002514582978.html)章节的"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被转为大写再进行匹配。 | | SHOW DATABASES | SHOW | GaussDB中：查询结果中字段使用字符集utf8mb4、字符序utf8mb4_bin。 | | 支持SQL_MODE中ONLY_FULL_GROUP_BY选项 | SELECT | SELECT列表中非聚合函数列与GROUP BY字段不一致时，非聚合函数列都需要出现GROUP BY列表或WHERE列表中，且WHERE子句中的列需要等于某一常量时，不报错。其中WHERE子句中的列，GaussDB支持入参数为1的函数列表达式，MySQL不支持函数列表达式。 GaussDB只支持GROUP BY后字段为正整数。 | | 使用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) ``` | | 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 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 SHARE/FOR UPDATE/LOCK IN SHARE 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两侧的表同时上锁。 - MySQL不支持给同一张表指定多次锁子句；GaussDB支持给同一张表指定多次锁子句，实际生效按照最强的锁处理。 ``` -- 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 FOR UPDATE OF t1 LOCK IN SHARE MODE; a | b ---+--- 1 | 2 (1 row) m_db=# DROP TABLE t1; DROP TABLE -- MySQL mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE t1(a INT, b INT); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO t1 VALUES(1,2); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t1 FOR UPDATE OF t1 LOCK IN SHARE MODE; ERROR 3569 (HY000): Table t1 appears in multiple locking clauses. mysql> DROP TABLE t1; Query OK, 0 rows affected (0.05 sec) ``` | | SELECT语法支持范围 | SELECT | - GaussDB的HAVING子句必须与GROUP BY子句或聚合函数一同使用，MySQL支持在查询语句中仅指定HAVING子句。 - GaussDB的HAVING必须且只能引用GROUP BY子句中的列或聚合函数中使用的列。MySQL支持对此行为的扩展，并允许HAVING引用列表中的SELECT列和外部子查询中的列。 - GaussDB以及MySQL5.7使用GROUP BY WITH ROLLUP时，不支持与DISTINCT、ORDER BY子句共同使用，MySQL8.0支持。 - 当查询表为空表时，GaussDB在使用指定WITH ROLLUP语句进行查询时，查询结果为一条空行数据，而MySQL查询结果为空。 - 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) ``` | | 支持WITH AS语句 | SELECT UPDATE DELETE | - GaussDB的WITH递归场景下：当subquery中非递归部分的列与subquery最终获得的结果列的类型、typmod、字符序不一致时，会产生语法错误，暂不支持该场景使用。 - GaussDB的WITH递归部分不支持聚合函数，窗口函数，FOR UPDATE/SHARE，LIMIT与OFFSET；MySQL支持FOR UPDATE/SHARE，MySQL 8.0.19以后的版本支持LIMIT与OFFSET。 - GaussDB的WITH递归部分支持DISTINCT与GROUP BY，MySQL不支持。 - WITH递归场景下：当WITH递归部分列生成的值比非递归部分更宽时，MySQL宽松模式数据截断，严格模式报错。GaussDB数据不截断，与MySQL数据长度加宽后的结果一致。 - GaussDB的WITH递归部分进行外连接时，与MySQL支持范围存在差异。 ``` -- 例如以下差异： -- 数据初始化 DROP TABLE IF EXISTS t2; CREATE TABLE t2(c INT); INSERT INTO t2 VALUES (5); -- GaussDB，非递归部分使用UNION SELECT 1::bigint为了强转非递归部分的列类型。 m_db=# WITH RECURSIVE cte AS (SELECT 1 AS a UNION SELECT 1::bigint UNION SELECT a+1 FROM cte RIGHT JOIN t2 ON t2.c>cte.a WHERE cte.a<3) SELECT * FROM cte; ERROR: recursive reference to query "cte" must not appear within an outer join LINE 1: ...AS a UNION SELECT 1::bigint UNION SELECT a+1 FROM cte RIGHT ... ^ -- MySQL 8.0 mysql> WITH RECURSIVE cte AS (SELECT 1 AS a UNION SELECT a+1 FROM cte RIGHT JOIN t2 ON t2.c>cte.a WHERE cte.a<3) SELECT * FROM cte; +------+ | a | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (0.00 sec) DROP TABLE IF EXISTS t2; ``` | | INSERT ... ON DUPLICATE KEY UPDATE语法 | INSERT | - GaussDB的ON DUPLICATE KEY UPDATE子句中的VALUES()不支持表名.列名格式，MySQL支持。 - INSERT ... query ON DUPLICATE KEY UPDATE语句中，query为子查询，且子查询为UNION查询或EXCEPT查询时，MySQL 5.7支持UPDATE子句引用子查询中的列名；MySQL 8.0不支持UPDATE子句引用子查询中的列名；GaussDB与MySQL 8.0保持一致，不支持UPDATE子句引用子查询中的列名。 - ON DUPLICATE KEY UPDATE子句更新多列时，MySQL前面UPDATE的结果会影响后面的结果，且允许同一列设置多次。GaussDB中，前面UPDATE的结果不会影响后面的结果，且不支持同一列设置多次，二者存在差异。设置GUC兼容性参数m_format_dev_version为's2'后，可保持与MySQL行为一致，既支持同一列设置多次，且引用更新后的结果。 - 插入数据违反唯一约束执行update操作时，GaussDB和MySQL返回的受影响行数存在差异。更新一条数据时，GaussDB返回1，MySQL返回2；如果将现有行更新为其当前值，GaussDB返回1，MySQL返回0。 - ON DUPLICATE KEY UPDATE子句更新自增列为NULL，且自增列含有NOT NULL约束时，GaussDB报错The null value in column xxx violates the not-null constraint.；MySQL执行成功，更新对应自增列为0。 - 对于表字段为VARCHAR类型，隐式转换为数值数据类型的场景，MySQL在字符串转换失败的部分比字符串完整长度少2的情况下（如："AA"没有字符被转换成功，长度差为2-0=2；"4XY"的第一个字符转换成功，长度差为3-1=2），不会报错。此时MySQL在长度差不等于2的情况下，仍然会报错。GaussDB中对应场景字符串转换失败时会始终报错。 ``` m_db=# CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(10)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE m_db=# INSERT INTO t1 values(1, 'A'); INSERT 0 1 m_db=# INSERT INTO t1 VALUES(1, 'X') ON DUPLICATE KEY UPDATE b=values(a)+values(b); ERROR: The double value 'X' is incorrect. CONTEXT: referenced column: b m_db=# INSERT INTO t1 VALUES(1, 'YY') ON DUPLICATE KEY UPDATE b=values(a)+values(b); ERROR: The double value 'YY' is incorrect. CONTEXT: referenced column: b m_db=# INSERT INTO t1 VALUES(1, 'ZZZ') ON DUPLICATE KEY UPDATE b=values(a)+values(b); ERROR: The double value 'ZZZ' is incorrect. CONTEXT: referenced column: b m_db=# DROP TABLE t1; DROP TABLE mysql> CREATE TABLE t1(a INT PRIMARY KEY, b VARCHAR(10)); Query OK, 0 rows affected (0.00 sec) mysql> TRUNCATE TABLE t1; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 values(1, 'A'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1, 'X') ON DUPLICATE KEY UPDATE b=values(a)+values(b); ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'X' mysql> INSERT INTO t1 VALUES(1, 'YY') ON DUPLICATE KEY UPDATE b=values(a)+values(b); Query OK, 2 rows affected, 2 warnings (0.00 sec) mysql> INSERT INTO t1 VALUES(1, 'ZZZ') ON DUPLICATE KEY UPDATE b=values(a)+values(b); ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'ZZZ' mysql> DROP TABLE t1; Query OK, 0 rows affected (0.00 sec) ``` | | GROUP BY HAVING | SELECT | 查询表达式别名与表字段重名的情况下： - GaussDB中，无论GROUP BY后面是数字还是名称，HAVING条件中名称指代表字段。 - MySQL中，GROUP BY后面是数字，HAVING条件中名称指代投影列中的表达式别名；GROUP BY后面是名称，HAVING条件中名称指代表字段。 ``` m_db=# CREATE TABLE t1(col_int int); CREATE TABLE m_db=# INSERT INTO t1 VALUES(1),(2); INSERT 0 2 m_db=# SELECT abs(col_int) + 2 AS col_int FROM t1 GROUP BY col_int having col_int > 2; col_int --------- (0 rows) m_db=# SET sql_mode = ''; SET m_db=# SELECT abs(col_int) + 2 AS col_int FROM t1 GROUP BY 1 HAVING col_int > 2; col_int --------- (0 rows) m_db=# DROP TABLE t1; DROP TABLE mysql> CREATE TABLE t1(col_int int); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT abs(col_int) + 2 AS col_int FROM t1 GROUP BY col_int HAVING col_int > 2; Empty set, 2 warnings (0.00 sec) mysql> SELECT abs(col_int) + 2 AS col_int FROM t1 GROUP BY 1 HAVING col_int > 2; +---------+ | col_int | +---------+ | 3 | | 4 | +---------+ 2 rows in set (0.00 sec) mysql> DROP TABLE t1; Query OK, 0 rows affected (0.00 sec) ``` |