# DDL 表1DDL语法兼容介绍 | 概述 | 详细语法说明 | 差异 | |:---|:---|:---| | 建表和修改表时支持创建主键、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必须为\[1 , 2^127^-1\]的正整数。MySQL可以设置为0（实际为1），GaussDB不可为0。 - GaussDB中当自增值已经达到字段数据类型的最大值时，若sql_mode为严格模式，继续自增将产生错误，若sql_mode为宽松模式，仍可自增为最大值。GaussDB自增计数器和表字段值转换使用类型转换规则与表达式计算相同，对于最大值溢出的处理逻辑是合理的。MySQL自增列为BIGINT UNSIGNED时，达到最大值后自增会产生报错，部分场景在达到最大值-2时就会产生报错，但并无统一的标准说明。MySQL自增列为BIGINT以及更小的整型类型时，达到最大值后自增仍为最大值。 - 不支持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不允许auto_increment_offset的值大于auto_increment_increment的值，会产生错误。MySQL允许，并说明auto_increment_offset会被忽略。 - 在表有主键或索引的情况下，ALTER TABLE命令重写表数据的顺序与MySQL不一定相同，GaussDB按表数据存储顺序重写，MySQL会按主键或索引顺序重写，导致自增值的顺序可能不同。 - GaussDB使用ALTER TABLE命令添加或修改自增列时，第一次预留自增值的数量是表统计信息中的行数，统计信息的行数不一定与MySQL一致，表现为自增值不连续，产生的自增值结果无法保证与MySQL完全一致。 - GaussDB对GUC参数auto_increment_offset和auto_increment_increment设置超出范围的值会产生错误。MySQL会自动修改为边界值。 - 临时表不支持自增列。 - 本地自增列在各个DN独立自增，不支持在插入成功时刷新LAST_INSERT_ID函数和GeneratedKey的值。 - sql_mode设置no_auto_value_on_zero参数，表定义的自增列为非NOT NULL约束，向表中插入数据不指定自增列的值时，GaussDB中自增列不触发自增，插入NULL值；MySQL中自增列触发自增，插入NULL值。 - 批量插入在不同执行计划下，本地自增列自增的顺序、自增值预留数量可能与MySQL存在差异。如："INSERT INTO table VALUES(...),(...),..."由于需要分布到不同的DN，在某些执行计划中，DN获取不到即将插入的行数。 - GaussDB的全局自增列在插入数据时自增时机和更新自增起始值时机比MySQL靠前，在插入报错的场景中更容易产生空洞值。如：在INSERT过程中，违反CHECK约束，MySQL 8.0数据库中的自增列不自增，GaussDB分布式全局自增列自增。 - GaussDB的ALTER TABLE命令新增全局自增列时，各个DN向GTM申请全局自增值，会导致自增值的顺序与MySQL不一致，空洞值也会有差异。 - UPDATE语句更新自增列的值，如果新值非0、NULL或DEFAULT，且大于当前自增最大值，会更新自增起始值，其行为与MySQL8.0相同。 - INSERT INTO ON DUPLICATE KEY语句，单次插入多行数据时，如果违反唯一约束，产生的空洞可能会和MySQL存在差异。 - GaussDB数据库TRUNCATE表时，不支持重置全局自增列的自增器从自增起始值自增，可以使用AUTOINC_RESETVAL()函数重置全局自增列的自增器从自增起始值开始自增。该函数谨慎使用，如果表中数据未清空使用该函数会导致自增列产生重复值。 - INSERT INTO ON DUPLICATE KEY语句当主键冲突更新全局自增列的值时，对于JDBC的getGeneratedKeys() 接口，GaussDB返回自增列的自增值，MySQL返回自增列的更新值。 - GaussDB的全局自增列在插入数据时，自增时机和更新自增起始值时机比MySQL靠前。如果在插入报错时，GaussDB可能已经刷新了last_insert_id的值，下次查询last_insert_id()函数结果，可能会是上次报错语句产生的自增值，和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建表失败，受严格模式控制。 | | 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_definition ,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索引的创建方法和默认规则具体说明参见[CREATE INDEX](https://support.huaweicloud.com/distributed-ref-v10-gaussdb/gaussdb-08-0253.html)，例如：在非分区键上创建唯一索引，会默认创建为GLOBAL索引。 - MySQL无GLOBAL索引的概念。在GaussDB中，当分区表索引为GLOBAL索引时，对表分区进行DROP、TRUNCATE、EXCHANGE等操作不会默认更新GLOBAL索引，进而导致GLOBAL索引失效，导致后续语句无法选中该索引。为了避免这种场景，建议用户在使用分区操作语法时在最后显指定UPDATE GLOBAL INDEX子句，或配置全局GUC参数enable_gpi_auto_update为true（推荐），使得在进行分区操作时自定更新GLOBAL索引。也可以配置全局GUC参数enable_drop_truncate_partition_keep_gpi为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。 | | 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下表名称。 | | 支持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) ``` |