# DML 表1DML语法兼容介绍 | 概述 | 详细语法说明 | 差异 | |:---|:---|:---| | DELETE支持从指定分区（或子分区）删除数据 | DELETE | - | | 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语法执行结果与MySQL严格模式一致，宽松模式暂未适配。例如当导入源数据包含非法字符时，GaussDB报错，MySQL严格模式下报错，宽松模式下对非法字符进行截断处理后导入。 ``` -- GaussDB m_db=# SET sql_mode=''; SET m_db=# DROP TABLE IF EXISTS t1; DROP TABLE m_db=# CREATE TABLE t1(a text) CHARSET gbk; CREATE TABLE m_db=# SELECT unhex('1AFF1F') INTO OUTFILE '/tmp/3.data'; COPY 1 m_db=# LOAD DATA INFILE '/tmp/3.data' INTO TABLE t1 CHARACTER SET gbk; ERROR: invalid byte sequence for encoding "GBK": 0xff 0x1f CONTEXT: COPY t1, line 1. m_db=# DROP TABLE t1; DROP TABLE -- MySQL mysql> SET sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t1(a text) CHARSET gbk; Query OK, 0 rows affected (0.03 sec) mysql> SELECT unhex('1AFF1F') INTO OUTFILE '/tmp/3.data'; Query OK, 1 row affected (0.00 sec) mysql> LOAD DATA INFILE '/tmp/3.data' INTO TABLE t1 CHARACTER SET gbk; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 1 mysql> SHOW warnings; +---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xFF\x1F' for column 'a' at row 1 | +---------+------+------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT hex(a) FROM t1; +--------+ | hex(a) | +--------+ | 1A | +--------+ 1 row in set (0.00 sec) mysql> DROP TABLE t1; Query OK, 0 rows affected (0.02 sec) ``` - IGNORE与LOCAL参数功能仅为当导入数据与表中数据存在冲突时，忽略当前冲突行数据功能和当文件中字段数小于指定表中列数时自动为其余列填充默认值功能，其余功能暂未适配。 - 指定LOCAL关键字，且文件路径为相对路径时，文件从二进制目录下搜索；不指定LOCAL关键字，且文件路径为相对路径时，文件从数据目录下搜索。 - LOAD DATA仅支持从服务端导入文件。 - \[(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提示告警信息，MySQL在以下任一场景提示告警信息，其他场景报错。 - sql_mode未设置严格模式。 - 指定了IGNORE选项。 - 指定了LOCAL选项且未指定REPLACE选项。 - 当源数据文件尾以列分隔符结束时，MySQL按分隔符后无新列处理，GaussDB下按分隔符后包含一列空字符串处理。 ``` -- 1.csv源数据 1,2, -- GaussDB m_db=# DROP TABLE IF EXISTS t1; NOTICE: table "t1" does not exist, skipping DROP TABLE m_db=# CREATE TABLE t1(a char(30), b char(30), c char(30)); CREATE TABLE m_db=# LOAD DATA infile '/tmp/1.csv' INTO TABLE t1 fields terminated by ','; COPY 1 m_db=# SELECT c IS NULL, length(c) FROM t1; ?column? | length ----------+-------- f | 0 (1 row) -- MySQL mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE t1(a char(30), b char(30), c char(30)); Query OK, 0 rows affected (0.04 sec) mysql> LOAD DATA infile '/tmp/1.csv' INTO TABLE t1 fields terminated by ','; ERROR 1261 (01000): Row 1 doesn't contain data for all columns ``` | | 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语句所检索的结果集的列中。 | | SELECT结果允许存在重复列名。 | SELECT | - | | NATURAL JOIN与MySQL有差异。 | SELECT | 在GaussDB中，NATURAL \[ \[LEFT \| RIGHT\] OUTER\] JOIN允许不指定LEFT \| RIGHT，不指定时NATURAL OUTER JOIN为NATURAL JOIN。允许连续使用多次JOIN。 | | UPDATE/DELETE支持ORDER BY和LIMIT | UPDATE/DELETE | 在ORDER BY子句和LIMIT子句和UPDATE/DELETE一起使用的情况下，若发现将要更新/删除的元组被其他会话并发修改，会取该条会话匹配中已排序得到的元组的最新值重新进行匹配，若依然满足条件再对这条元组进行更新。这个过程中MySQL会重新获取排序结果进行更新，而GaussDB仅会对该条会话已排序得到的涉及并发更新的元组进行更新/删除，可能导致数据不一致。 - GaussDB的行为： ``` m_db=# CREATE TABLE t1(a INT, b INT); CREATE TABLE m_db=# CREATE TABLE t2(a INT, b INT); CREATE TABLE m_db=# INSERT INTO t1 VALUES(1,1),(2,2); INSERT 0 2 -- session one。 m_db=# START TRANSACTION; START TRANSACTION -- session one先进行更新，未提交事务。 m_db=# UPDATE t1 SET a=3 WHERE a=1; UPDATE 1 -- session two。 m_db=# START TRANSACTION; START TRANSACTION -- session two后进行更新，等待session one事务结束后更新成功。 m_db=# UPDATE t1 SET b=4 ORDER BY a LIMIT 1; UPDATE 1 -- session one提交事务。 m_db=# COMMIT; COMMIT -- session two更新成功后查询数据。 m_db=# SELECT * FROM t1; a | b ---+--- 3 | 4 2 | 2 (2 rows) ``` - MySQL的行为： ``` -- session one。 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- session one先进行更新，未提交事务。 mysql> UPDATE t1 SET a=3 WHERE a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- session two。 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE t1 SET b=4 ORDER BY a LIMIT 1; -- session two后进行更新，等待session one事务结束后更新成功。 Query OK, 1 row affected (5.12 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- session one提交事务。 mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) -- session two更新成功后查询数据。 mysql> SELECT * FROM t1; +------+------+ | a | b | +------+------+ | 3 | 1 | | 2 | 4 | +------+------+ 2 rows in set (0.00 sec) ``` | | 外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错。 | UPDATE/DELETE | 外键数据类型是timestamp/datetime时，UPDATE/DELETE外表报错，MySQL成功。 | | natural 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 得到的临时表中存在重复，故natural 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 OUTFILE ... | 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，且不允许多次设置同一列，二者存在差异。 | | 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行为存在差异，具体请参见[操作符](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0563.html)章节的"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 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两侧的表同时上锁。 - 当加锁子句与ORDER BY子句一起使用时，GaussDB若发现将要锁定的元组被其他会话并发修改，会取该条会话匹配中已排序得到的元组的最新值。这个过程中MySQL会重新获取排序结果。 - GaussDB的行为： ``` gaussdb=# CREATE TABLE t1(a INT,b INT); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(1,1),(2,2); INSERT 0 2 -- session one。 gaussdb=# START TRANSACTION; START TRANSACTION -- session one先进行更新，未提交事务。 gaussdb=# UPDATE t1 SET a=3 WHERE a=1; UPDATE 1 -- session two开启事务。 gaussdb=# START TRANSACTION; START TRANSACTION -- session two后进行查询，等待session one事务结束后查询成功。 gaussdb=# SELECT * FROM t1 ORDER BY a FOR UPDATE; commit a | b ---+--- 3 | 1 2 | 2 (2 rows) -- session one提交事务。 gaussdb=# COMMIT; COMMIT ``` - MySQL的行为： ``` mysql> CREATE TABLE t1(a INT,b INT); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 VALUES(1,1),(2,2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 -- session one。 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- session one先进行更新，未提交事务。 mysql> UPDATE t1 SET a=3 WHERE a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- session two开启事务。 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- session two后进行查询，等待session one事务结束后查询成功。 mysql> SELECT * FROM t1 ORDER BY a FOR UPDATE; +------+------+ | a | b | +------+------+ | 3 | 1 | | 2 | 4 | +------+------+ 2 rows in set (0.00 sec) -- session one提交事务。 mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) ``` - 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) ``` | | SELECT投影列赋值用户变量差异 | SELECT @var_name := expr | - MySQL用户变量名支持使用转义字符或双重引号转义，GaussDB用户变量名不支持。 单引号括起的变量名，变量名不能出现单引号，如@'''、@''''、@'\\''不支持，解析时匹配不到'或解析报错，如： ``` -- 解析报错 m_db=# SET @'''' = 1; ERROR: syntax error at or near "@" LINE 1: SET @'''' = 1; -- 解析时匹配不到' m_db=# SET @'\'' = 1; m_db'# ``` 双引号括起的变量名，变量名不能出现双引号，如@"""、@""""、@"\\""不支持，解析时匹配不到"或解析报错，如： ``` -- 解析报错 m_db=# SET @"""" = 1; ERROR: syntax error at or near "@" LINE 1: SET @"""" = 1; -- 解析时匹配不到" m_db=# SET @"\"" = 1; m_db"# ``` 反引号括起的变量名，变量名不能出现反引号，如@\`\`\`、@\`\`\`\`、@\`\\\`\`不支持，解析时匹配不到\`或解析报错，如： ``` -- 解析报错 m_db=# SET @```` = 1; ERROR: syntax error at or near "@" LINE 1: SET @```` = 1; -- 解析时匹配不到` m_db=# SET @`\`` = 1; m_db`# ``` - 当SQL语句中同时出现为用户变量赋值和读取用户变量时，变量的求值顺序是未定义的，最终结果与MySQL可能会有差异。以赋值用户变量，同时用户变量作为MAX函数的参数为例： ``` -- GaussDB m_db=# SELECT @a := 2; @a ---- 2 (1 row) m_db=# SELECT MAX(@a), @a := 3; max | @a -----+---- 2 | 3 (1 row) m_db=# SELECT @a; @a ---- 3 (1 row) -- MySQL 5.7 mysql> SELECT @a := 2; +------+ | @a | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> SELECT max(@a), @a := 3; +---------+---------+ | max(@a) | @a := 3 | +---------+---------+ | 3 | 3 | +---------+---------+ 1 row in set (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) ``` - 设置用户变量为子查询或CASE WHEN表达式且最终结果为子查询表达式时，GaussDB中自定义变量的值与直接查询的结果保持一致，而MySQL使用的中间计算结果。GaussDB中最终自定义变量的值与MySQL可能会有差异。 ``` CREATE TABLE t1 (a double(6,3)); INSERT INTO t1 VALUES(1.1); -- GaussDB，用户变量的值与直接查询一致 m_db=# SELECT @a := (SELECT a FROM t1); @a ------- 1.100 (1 row) m_db=# SELECT @a; @a ------- 1.100 (1 row) -- MySQL 5.7，用户变量的值与直接查询的不一致 mysql> SELECT @a := (SELECT a FROM t1); +--------------------------+ | @a := (SELECT a FROM t1) | +--------------------------+ | 1.100 | +--------------------------+ 1 row in set (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 1.1 | +------+ 1 row in set (0.00 sec) ``` - ORDER BY 子句中引用用户变量表达式相关的列时，GaussDB用户变量的结果与MySQL可能会有差异，具体表现与执行计划有关。 ``` CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1),(3),(2); -- GaussDB m_db=# SELECT @a := a FROM t1 ORDER BY a; @a ---- 1 2 3 (3 rows) m_db=# SELECT @a; @a ---- 2 (1 row) -- MySQL 5.7 mysql> SELECT @a := a FROM t1 ORDER BY a; +---------+ | @a := a | +---------+ | 1 | | 2 | | 3 | +---------+ 3 rows in set (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) ``` - @var_name :=expr作为IF/IFNULL/COALESCE函数的入参，当此参数未被选中时，MySQL不会给用户变量赋值，而GaussDB会给用户变量赋值，实现不一致。以IF函数为例： ``` -- GaussDB，无论@var:=val参数是否被选中，都会为@var赋值 m_db=# SELECT IF (1, @a:=1, 2); if ---- 1 (1 row) m_db=# SELECT @a; @a ---- 1 (1 row) m_db=# SELECT IF (0, @a:=10, 2); if ---- 2 (1 row) m_db=# SELECT @a; @a ---- 10 (1 row) -- MySQL 5.7，@var:=val参数被选中时为@var赋值，@var:=val参数未被选中时不会为@var赋值 mysql> SELECT IF (1, @a:=1, 2); +------------------+ | if (1, @a:=1, 2) | +------------------+ | 1 | +------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> SELECT IF (0, @a:=10, 2); +-------------------+ | if (0, @a:=10, 2) | +-------------------+ | 2 | +-------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 1 | +------+ 1 row in set (0.00 sec) ``` - 当用户变量赋值操作出现在 FROM 子查询中时，GaussDB直接把子查询结果传递到上层访问，而MySQL会把FROM子查询的结果全部物化到临时表中供上层访问查询，两者的实现不一致。 ``` CREATE TABLE t1(a int); INSERT INTO t1 VALUES (1),(2),(3); -- GaussDB m_db=# SELECT @a := a, a FROM t1; @a | a ----+--- 1 | 1 2 | 2 3 | 3 (3 rows) m_db=# SELECT @a, a FROM (SELECT @a := a, a FROM t1)t; @a | a ----+--- 1 | 1 2 | 2 3 | 3 (3 rows) m_db=# SELECT @a; @a ---- 3 (1 row) -- MySQL 5.7 mysql> SELECT @a := a, a FROM t1; +---------+------+ | @a := a | a | +---------+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +---------+------+ 3 rows in set (0.00 sec) mysql> SELECT @a, a FROM (SELECT @a := a, a FROM t1)t; +------+------+ | @a | a | +------+------+ | 3 | 1 | | 3 | 2 | | 3 | 3 | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec) ``` | | INSERT ... ON DUPLICATE KEY UPDATE语法 | INSERT | - GaussDB的ON DUPLICATE KEY UPDATE子句中的VALUES()不支持表名.列名格式；MySQL支持。 - GaussDB不允许对约束字段用ON DUPLICATE KEY UPDATE进行修改；MySQL允许。 - INSERT ... query ON DUPLICATE KEY UPDATE语句中，query为子查询时，GaussDB的ON DUPLICATE KEY UPDATE子句不支持引用子查询中的列名；MySQL支持. - ON DUPLICATE KEY UPDATE子句更新多列时，MySQL前面UPDATE的结果会影响后面的结果，且允许同一列设置多次。GaussDB中，前面UPDATE的结果不会影响后面的结果，且不支持同一列设置多次，二者存在差异。 - 插入数据违反唯一约束执行UPDATE操作时，GaussDB和MySQL返回的受影响行数存在差异。更新一条数据时，GaussDB返回1，MySQL返回2；如果将现有行更新为其当前值，GaussDB返回1，MySQL返回0。 - 对于表字段为VARCHAR类型，隐式转换为数值数据类型的场景：MySQL在字符串转换失败时，转换失败的部分比字符串完整长度少2的情况下（即长度差等于2），不会产生告警或报错，对于长度差不等于2的情况下，产生告警或报错。GaussDB在字符串转换失败时，会始终产生告警或报错。 ``` -- 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 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) ``` | | UNION/EXCEPT语句 | SELECT | GaussDB集合操作中，当前两个查询中合并的数据是字符串字面量的形式时，如果存在第三个查询，集合操作的返回类型直接以第三个查询的数据类型为准。 ``` -- 使用第三个查询中的类型做转换，第二个查询中的数据转换失败。 m_db=# SELECT '123' AS col UNION SELECT 'test' UNION SELECT 9999 ORDER BY col; WARNING: The integer syntax of the input is invalid: "test". CONTEXT: referenced column: col col ------ 0 123 9999 (3 rows) ``` |