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

DDL

表1 DDL语法兼容介绍

概述

详细语法说明

差异

建表和修改表时支持创建主键、UNIQUE索引、外键约束

ALTER TABLE、CREATE TABLE
  • 在GaussDB中，当约束关联的表为ustore，且SQL语句中指定为using btree时，底层会建立为ubtree。
  • 在GaussDB中，允许将外键作为分区键。
  • 索引名、约束名、key名GaussDB是SCHEMA下唯一，MySQL是表下唯一。

支持自增列

ALTER TABLE、CREATE TABLE
  • GaussDB的自动增长列建议为索引的第一个字段，否则建表时产生警告，MySQL自动增长列必须为索引第一个字段，否则建表时会报错。GaussDB含有自动增长列的表进行某些操作时会产生错误，例如：ALTER TABLE EXCHANGE PARTITION。
  • GaussDB的AUTO_INCREMENT = value语法，value必须为小于2^127的正数。MySQL可以为0，GaussDB不可以。
  • GaussDB中当自增值已经达到字段数据类型的最大值时，继续自增将产生错误。MySQL有些场景产生错误或警告，有些场景仍自增为最大值。
  • 不支持innodb_autoinc_lock_mode系统变量，GaussDB的GUC参数auto_increment_cache=0时，批量插入自动增长列的行为与MySQL系统变量innodb_autoinc_lock_mode=1相似。
  • GaussDB的自动增长列在导入数据或者进行Batch Insert执行计划的插入操作时，对于混合0、NULL和确定值的场景，如果产生错误，后续插入自增值不一定与MySQL完全一致。
    • 提供auto_increment_cache参数，可以控制预留自增值的数量。
  • GaussDB的并行导入或插入自动增长列触发自增时，每个并行线程预留的缓存值也只在其线程中使用，未完全使用完毕的话，也会出现表中自动增长列的值不连续的情况。并行插入产生的自增值结果无法保证与MySQL完全一致。
  • GaussDB的本地临时表中的自动增长列批量插入时不会预留自增值，正常场景不会产生不连续的自增值。MySQL临时表与普通表中的自动增长列自增结果一致。
  • GaussDB的SERIAL数据类型为原有的自增列，与AUTO_INCREMENT自增列有差异。MySQL的SERIAL数据类型就是AUTO_INCREMENT自增列。
  • GaussDB不允许auto_increment_offset的值大于auto_increment_increment的值，会产生错误。MySQL允许，并说明auto_increment_offset会被忽略。
  • 在表有主键或索引的情况下，ALTER TABLE命令重写表数据的顺序与MySQL不一定相同，GaussDB按表数据存储顺序重写，MySQL会按主键或索引顺序重写，导致自增值的顺序可能不同。
  • GaussDB使用ALTER TABLE命令添加或修改自增列时，第一次预留自增值的数量是表统计信息中的行数，统计信息的行数不一定与MySQL一致。
  • GaussDB的last_insert_id函数返回值为128位的整型。
  • GaussDB在触发器或用户自定义函数中自增时，刷新last_insert_id返回值。MySQL不刷新。
  • GaussDB的对GUC参数auto_increment_offset和auto_increment_increment设置超出范围的值会产生错误。MySQL会自动改为边界值。

支持前缀索引

CREATE INDEX、ALTER TABLE、CREATE TABLE
  • GaussDB中前缀长度不得超过2676，键值的实际长度受内部页面限制，若字段中含有多字节字符或者一个索引上有多个键，索引行长度可能会超限报错。
  • GaussDB中主键索引中不支持前缀键，创建或添加主键时不支持指定前缀长度。

支持指定字符集与排序规则

ALTER SCHEMA、ALTER TABLE、CREATE SCHEMA、CREATE TABLE
  • 指定库级字符集时，除BINARY字符集外，暂不支持创建新库/模式的字符集与数据库的server_encoding不同。
  • 指定表级、列级字符集和字符序时，MySQL支持指定与库级字符集、字符序不同的字符集和字符序。在GaussDB中，表级、列级字符集和字符序仅支持BINARY字符集、字符序或者与库级字符集、字符序相同的字符集、字符序。

修改表时支持在表第一列前面或者在指定列后面添加列

ALTER TABLE

-

修改列名称/定义语法

ALTER TABLE

暂不支持DROP INDEX | DROP KEY | ORDER BY子项。

创建分区表语法

CREATE TABLE PARTITION
  • MySQL在以下场景支持表达式，不支持多个分区键：
    • 使用LIST分区/RANGE分区策略，不指定COLUMNS关键字。
    • 使用HASH分区策略。
  • MySQL在以下场景不支持表达式，支持多个分区键：
    • 使用LIST分区/RANGE分区策略，指定COLUMNS关键字。
    • 使用KEY分区策略。
  • GaussDB不支持使用表达式作为分区键，不支持指定二级分区。
  • GaussDB仅在以下场景支持使用多个分区键：使用LIST分区/RANGE分区策略。
  • GaussDB分区表不支持用生成列作为分区键。

建表和修改表时支持指定表级和列级comment

CREATE TABLE、ALTER TABLE

-

创建索引时支持指定索引级comment

CREATE INDEX

-

交换普通表和分区表分区的数据

ALTER TABLE PARTITION

ALTER TABLE EXCHANGE PARTITION的差异点：

  • 对于自增列，MySQL执行alter exchange partition后，自增列会被重置；GaussDB 则不会被重置，自增列则按照旧的自增值递增。
  • MySQL表或分区使用tablespace时，则无法进行分区和普通表数据的交换；GaussDB表或分区使用不同的tablespace时，仍可进行分区和普通表数据的交换。
  • 对于列默认值，MySQL不会校验默认值，因此默认值不同时也可进行分区和普通表数据的交换；GaussDB会校验默认值，如果默认值不同，则无法进行分区和普通表数据的交换。
  • MySQL在分区表或普通表上进行DROP列操作后，表结构仍然一致，则可进行分区和普通表数据的交换；GaussDB需要保证普通表和分区表的被删除列严格对齐才能进行分区和普通表数据的交换。
  • MySQL和GaussDB的哈希算法不同，所以两者在相同的hash分区存储的数据可能不一致，导致最后交换的数据也可能不一致。
  • MySQL的分区表不支持外键，普通表包含外键或其他表引用普通表的外键，则无法进行分区和普通表数据的交换；GaussDB的分区表支持外键，在两个表的外键约束一致时，则可进行分区和普通表数据的交换，GaussDB的分区表不带外键，普通表有其他表引用，如果分区表和普通表表一致，则可进行分区和普通表数据的交换。

修改分区表的分区键信息

ALTER TABLE

MySQL支持修改分区表的分区键信息，GaussDB中不支持。

支持CREATE TABLE ... LIKE语法

CREATE TABLE ... LIKE
  • 在MySQL 8.0.16 之前的版本中，CHECK约束会被语法解析但功能会被忽略，表现为不复制CHECK约束，GaussDB支持复制CHECK约束。
  • 对于主键约束名称，在建表时，MySQL所有主键约束名称固定为PRIMARY KEY，GaussDB不支持复制。
  • 对于唯一键约束名称，在建表时，MySQL支持复制，GaussDB不支持复制。
  • 对于CHECK约束名称，在建表时，MySQL 8.0.16 之前的版本无CHECK约束信息，GaussDB支持复制。
  • 对于索引名称，在建表时，MySQL支持复制，GaussDB不支持复制。
  • 在跨sql_mode模式建表时，MySQL受宽松模式和严格模式控制，GaussDB可能存在严格模式失效的情况。

    例如：源表存在默认值“0000-00-00”，在“no_zero_date”严格模式下，GaussDB建表成功，且包含默认值“0000-00-00”，严格模式失效；而MySQL建表失败，受严格模式控制。

支持增加子分区语法

 
ALTER TABLE [ IF EXISTS ] { table_name  [*] | ONLY table_name | ONLY ( table_name  )} add_clause;
add_clause:
ADD {{partition_less_than_item | partition_start_end_item | partition_list_item} |
PARTITION({partition_less_than_item | partition_start_end_item | partition_list_item})}

保留原分区表语法。

不支持下述语法添加多分区：

ALTER TABLE table_name ADD PARTITION (partition_definition1, partition_definition1,…);

仅支持原有添加多分区语法：

ALTER TABLE table_name ADD PARTITION (partition_definition1),  ADD PARTITION (partition_definition2[y1] ), …;

TRUNCATE子分区语法

 
ALTER TABLE [ IF EXISTS ]  table_name  
    truncate_clause;

支持子项有差异，对于truncate_clause：

  • M-Compatibility模式： 
    TRUNCATE PARTITION  { { ALL | partition_name [, ...] } | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]
  • MySQL支持： 
    TRUNCATE PARTITION {partition_names | ALL}

主键索引名

 
CREATE TABLE table_name ( col_definitine ,PRIMARY KEY [index_name] [ USING method ] ( { column_name | ( expression ) }[ ASC | DESC ] } [, ... ] ) index_parameters [USING method| COMMENT 'string'])

GaussDB中的主键指定索引名后创建的索引名为用户所指定的索引名，MySQL索引名为PRIMARY。

删除有依赖的对象

 
DROP drop_type name CASCADE;

在GaussDB中，删除有依赖的对象需要加CASCADE，MySQL不需要。

NOT NULL约束不允许插入NULL值

 
CREATE TABLE t1(id int NOT NULL DEFAULT 8);
INSERT INTO t1 VALUES(NULL);
INSERT INTO t1 VALUES(1),(NULL),(2);

在MySQL宽松模式下，会将NULL进行类型转换，并成功插入数据；在MySQL严格模式下不允许插入NULL值。在GaussDB不支持此特性，在宽松模式和严格模式下均不允许插入NULL值。

CHECK约束生效

CREATE TABLE
  • CREATE TABLE带CHECK约束的时候，MySQL 8.0会生效，MySQL 5.7只解析语法但不生效。GaussDB在此功能上同步MySQL 8.0版本，且GaussDB CHECK约束可以引用其他列，而MySQL不能。
  • GaussDB 一个表中最多只能加32767个CHECK约束。

索引的algorithm和lock选项不起作用

CREATE INDEX ...

DROP INDEX ...

M-Compatibility模式的CREATE/DROP INDEX语句中INDEX选项algorithm_option和lock_option目前只在语法上支持，创建时不报错，但实际不起作用。

CREATE TABLE hash分区和二级分区的存储与MySQL不同

CREATE TABLE

GaussDB的CREATE TABLE语句中hash分区表和二级分区表所使用的hash函数与MySQL不一致，因此hash分区表和二级分区表的存储与MySQL有区别。

分区表索引

CREATE INDEX
  • GaussDB的分区表索引分为LOCAL和GLOBAL两种。LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
  • LOCAL和GLOBAL索引的创建方法和默认规则具体说明参见《开发指南》中”SQL语法 > SQL语句 > C > CREATE INDEX”章节，例如：在非分区键上创建唯一索引，会默认创建为GLOBAL索引。
  • MySQL无GLOBAL索引的概念。在GaussDB中，当分区表索引为GLOBAL索引时，对表分区进行DROP、TRUNCATE、EXCHANGE等操作不会默认更新GLOBAL索引，进而导致GLOBAL索引失效，导致后续语句无法选中该索引。为了避免这种场景，建议用户在使用分区操作语法时在最后显指定UPDATE GLOBAL INDEX子句，或配置全局GUC参数enable_gpi_auto_update为true（推荐），使得在进行分区操作时自定更新GLOBAL索引。

CREATE/ALTER TABLE语句中分区表为KEY分区，不支持指定algorithm。部分分区定义入参不支持表达式。

CREATE TABLE、ALTER TABLE

GaussDB的CREATE/ALTER TABLE语句中分区表为KEY分区，不支持指定algorithm。

不支持表达式入参的语法：
  • PARTITION BY HASH()
  • PARTITION BY KEY()
  • VALUES LESS THAN()

分区表不支持LINEAR/KEY hash

CREATE TABLE ... PARTITION ...

GaussDB分区表不支持LINEAR/KEY hash。

check和auto_increment语法不能作用在同一字段

CREATE TABLE

由于MySQL 5.7的check字段不生效，check和auto_increment同时作用于同一字段只有auto_increment生效，但GaussDB报错。

删除存在依赖关系的表

DROP TABLE

GaussDB删除存在依赖的表必须加上CASCADE才能成功，MySQL不需要。

表定义相关选项

CREATE TABLE... 、 ALTER TABLE ...
  • GaussDB不支持以下选项：AVG_ROW_LENGTH、CHECKSUM、COMPRESSION、CONNECTION、DATA DIRECTORY、INDEX DIRECTORY、DELAY_KEY_WRITE、ENCRYPTION、INSERT_METHOD、KEY_BLOCK_SIZE、MAX_ROWS、MIN_ROWS、PACK_KEYS、PASSWORD、STATS_AUTO_RECALC、STATS_PERSISTENT、STATS_SAMPLE_PAGES。
  • 以下选项在GaussDB中不报错，但实际上也不生效：ENGINE、ROW_FORMAT。

CMK密钥轮转，轮换加密COLUMN ENCRYPTION KEY的CLIENT MASTER KEY，对COLUMN ENCRYPTION KEY明文进行重加密。

ALTER COLUMN ENCRYPTION KEY

M-Compatibility模式不支持全密态。因此不支持该语法。

密态等值查询特性使用多级加密模型，主密钥加密列密钥，列密钥加密数据。本语法用于创建主密钥对象。

CREATE CLIENT MASTER KEY

M-Compatibility模式不支持全密态。因此不支持该语法。

创建一个列加密密钥，该密钥可用于加密表中的指定列。

CREATE COLUMN ENCRYPTION KEY

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，传输密钥到服务端缓存，只在开启内存解密逃生通道的情况下使用。

\send_token

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，传输密钥到服务端缓存，只在开启内存解密逃生通道的情况下使用。

\st

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，销毁服务端缓存的密钥，只在开启内存解密逃生通道的情况下使用。

\clear_token

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，销毁服务端缓存的密钥，只在开启内存解密逃生通道的情况下使用。

\ct

M-Compatibility模式不支持全密态。因此不支持该语法。

在全密态数据库特性中,用于设置访问外部密钥管理者的参数。

\key_info KEY_INFO

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，用于开启三方动态库功能与加载三方动态库时的参数设置。

\crypto_module_info MODULE_INFO

M-Compatibility模式不支持全密态。因此不支持该语法。

全密态功能，用于开启三方动态库功能与加载三方动态库时的参数设置。

\cmi MODULE_INFO

M-Compatibility模式不支持全密态。因此不支持该语法。

generated always as语句不能再引用由generated always as生成的列。

Generated Always AS

GaussDB的generated always as语句不能再引用由generated always as生成的列，MySQL可以。

支持更改表名语法

ALTER TABLE tbl_name RENAME [TO | AS | =] new_tbl_name;
  • GaussDB的ALTER RENAME语法仅支持修改表名称功能操作，不能耦合其它功能操作。
  • GaussDB中，仅旧表名字段支持使用schema.table_name格式，且新表名与旧表名将属于同一个Schema。
  • GaussDB不支持新旧表跨Schema重命名操作；但如有权限，则可在当前Schema下修改其他Schema下表名称。

禁用GUC参数enable_expr_fusion

SET enable_expr_fusion= ON

M-Compatibility模式暂不支持GUC参数enable_expr_fusion打开。

支持CREATE VIEW AS SELECT语法

 
CREATE VIEW table_name AS query；
  • 针对以下类型，不支持CREATE VIEW view_name AS query语法中query包含计算操作（如函数调用、使用操作符计算）。
    • BINARY[(n)]
    • BOOLEAN/BOOL
    • VARBINARY(n)
    • CHAR[(n)]
    • VARCHAR(n)
    • TIME[(p)]
    • DATETIME[(p)]
    • TIMESTAMP[(p)]
    • BIT[(n)]
    • NUMERIC[(p[,s])]
    • DECIMAL[(p[,s])]
    • DEC[(p[,s])]
    • FIXED[(p[,s])]
    • FLOAT4[(p, s)]
    • FLOAT8[(p,s)]
    • FLOAT[(p)]
    • REAL[(p, s)]
    • FLOAT[(p, s)]
    • DOUBLE[(p,s)]
    • DOUBLE PRECISION[(p,s)]
    • TEXT
    • TINYTEXT
    • MEDIUMTEXT
    • LONGTEXT
    • BLOB
    • TINYBLOB
    • MEDIUMBLOB
    • LONGBLOB
  • 在query为简单查询场景下，M-Compatibility模式针对上述类型的计算操作进行报错提示，例如: 
    m_db=# CREATE TABLE TEST (salary int(10));
CREATE TABLE

m_db=# INSERT INTO TEST VALUES(8000);
INSERT 0 1

m_db=# CREATE VIEW view1 AS SELECT salary/10 as te FROM TEST;
ERROR:  Unsupported type numeric used with expression in CREATE VIEW statement.

m_db=# CREATE VIEW view2 AS SELECT sec_to_time(salary) as te FROM TEST;
ERROR:  Unsupported type time used with expression in CREATE VIEW statement.
  • 在query为复合查询，子查询等非简单查询场景下，M-Compatibility模式针对上述类型的计算操作与MySQL存在差异，M-Compatibility模式下新创建表的数据类型列精度属性不保留。

索引名可重名范围

CREATE TABLE、CREATE INDEX

MySQL中索引名在一个表下唯一，在不同的表下可以有相同的索引名。M-Compatibility模式中的索引名在同一个SCHEMA下唯一，在同一的SCHEMA下不可用相同的索引名。在M-Compatibility模式下，针对会自动创建索引的约束和key，也会有相同的规则。

视图依赖差异

CREATE VIEW、ALTER TABLE

MySQL中视图存储，只记录目标表的表名、列名、数据库名信息，不记录目标表的唯一标识；GaussDB会将创建视图时的SQL解析，并存储目标表的唯一标识。因此存在如下差异：

  • 修改存在视图依赖的列的数据类型，MySQL中对应的视图不感知目标表的修改，因此可以修改成功；GaussDB视图中的列禁止修改数据类型，因此无法修改该列的数据类型。
  • 重命名存在视图依赖的列，MySQL中对应的视图不感知目标表的修改，因此可以修改成功，但是后续无法查询该视图；GaussDB视图中，每个列精确存储了其对应的表和列的唯一标识，因此表中的列名可以修改成功，视图中的列名不被修改，且后续可以查询该视图。

外键差异

CREATE TABLE
  • GaussDB外键约束对类型不敏感，如果主表和从表对应的字段数据类型存在隐式类型转换就可以建成。MySQL外键类型敏感。如果两个表对应的列类型不同外键无法建成。
  • MySQL不支持通过MODIFY COLUMN或CHANGE COLUMN方式修改表列外键所在列的数据类型或列名等，GaussDB可以。

索引升降序差异

CREATE INDEX

在MySQL 5.7中，ASC | DESC被解析但是被忽略，默认行为为ASC；在MySQL 8.0及GaussDB中，ASC | DESC被解析且生效。

指定字段的默认值

CREATE TABLE、ALTER TABLE
  • MySQL 5.7指定默认值时，仅支持不带括号形式的默认值。MySQL 8.0与GaussDB支持带括号形式的默认值。 
    -- GaussDB
m_db=# DROP TABLE IF EXISTS t1, t2;
DROP TABLE
m_db=# CREATE TABLE t1(a DATETIME DEFAULT NOW());
CREATE TABLE
m_db=# CREATE TABLE t2(a DATETIME DEFAULT (NOW()));
CREATE TABLE

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

mysql> CREATE TABLE t1(a DATETIME DEFAULT NOW());
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2(a DATETIME DEFAULT (NOW()));
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 '(NOW()))' at line 1

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

mysql> CREATE TABLE t1(a DATETIME DEFAULT NOW());
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE t2(a DATETIME DEFAULT (NOW()));
Query OK, 0 rows affected (0.20 sec)
  • MySQL给BLOB、TEXT、JSON数据类型指定默认值时必须给默认值添加括号，GaussDB给上述数据类型指定默认值时可以不添加括号。
  • GaussDB指定默认值时不会校验默认值是否溢出；MySQL指定不带括号形式的默认值时会校验是否溢出，指定带括号形式的默认值时不会校验是否溢出。
  • GaussDB支持使用DATE/TIME/TIMESTAMP开头的时间常量给字段指定默认值，MySQL使用DATE/TIME/TIMESTAMP开头的时间常量给字段指定默认值时必须在默认值外添加括号。 
    -- GaussDB
m_db=# DROP TABLE IF EXISTS t1, t2;
DROP TABLE
m_db=# CREATE TABLE t1(a TIMESTAMP DEFAULT TIMESTAMP '2000-01-01 00:00:00');
CREATE TABLE
m_db=# CREATE TABLE t2(a TIMESTAMP DEFAULT (TIMESTAMP '2000-01-01 00:00:00'));
CREATE TABLE

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

mysql> CREATE TABLE t1(a TIMESTAMP DEFAULT TIMESTAMP '2000-01-01 00:00:00');
ERROR 1067 (42000): Invalid default value for 'a'
mysql> CREATE TABLE t2(a TIMESTAMP DEFAULT (TIMESTAMP '2000-01-01 00:00:00'));
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 '(TIMESTAMP '2000-01-01 00:00:00'))' at line 1

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

mysql> CREATE TABLE t1(a TIMESTAMP DEFAULT TIMESTAMP '2000-01-01 00:00:00');
ERROR 1067 (42000): Invalid default value for 'a'
mysql> CREATE TABLE t2(a TIMESTAMP DEFAULT (TIMESTAMP '2000-01-01 00:00:00'));
Query OK, 0 rows affected (0.19 sec)
父主题： SQL

相关文档