Help Center/ GaussDB/ MySQL Compatibility(Distributed)/ MySQL Compatibility in MySQL-Compatible Mode/ SQL/ DDL
Updated on 2024-12-06 GMT+08:00
View PDF
Share

DDL

Table 1 DDL syntax compatibility

No.

Description

Syntax

Difference

1

Create primary keys and UNIQUE indexes during table creation and modification.

ALTER TABLE and CREATE TABLE
  • GaussDB does not support the UNIQUE INDEX|KEY index_name syntax. An error will be reported when the UNIQUE INDEX|KEY index_name syntax is used. However, MySQL supports these functions.
  • When a constraint is created as a global secondary index and USING BTREE is specified in the SQL statement, the underlying index is created as UB-tree.
  • When the table joined with the constraint is Ustore and USING BTREE is specified in the SQL statement, the underlying index is created as UB-tree.

2

Support prefix indexes.

CREATE INDEX
  • The prefix length cannot exceed 2676. The actual length of the key value is restricted by the internal page. If a column contains multi-byte characters or an index has multiple keys, an error may be reported when the index line length exceeds the threshold.
  • In the CREATE INDEX syntax, the following keywords cannot be used as prefix keys for column names: COALESCE, EXTRACT, GREATEST, LEAST, LNNVL, NULLIF, NVL, NVL2, OVERLAY, POSITION, REGEXP_LIKE, SUBSTRING, TIMESTAMPDIFF, TREAT, TRIM, XMLCONCAT, XMLELEMENT, XMLEXISTS, XMLFOREST, XMLPARSE, XMLPI, XMLROOT, and XMLSERIALIZE.
  • Prefix keys are not supported in primary key and unique key indexes.

3

Specify character sets and collation rules.

ALTER SCHEMA, ALTER TABLE, CREATE SCHEMA, and CREATE TABLE

-

4

Create a partitioned table.

CREATE TABLE PARTITION

-

5

Specify table-level and column-level comments during table creation and modification.

CREATE TABLE and ALTER TABLE

-

6

Specify index-level comments during index creation.

CREATE INDEX

-

7

Exchange the partition data of an ordinary table and a partitioned table.

ALTER TABLE PARTITION

Differences in ALTER TABLE EXCHANGE PARTITION:

  • If MySQL tables or partitions use tablespaces, data in partitions and ordinary tables cannot be exchanged. If GaussDB tables or partitions use different tablespaces, data in partitions and ordinary tables can still be exchanged.
  • MySQL does not verify the default values of columns. Therefore, data in partitions and ordinary tables can be exchanged even if the default values are different. GaussDB verifies the default values. If the default values are different, data in partitions and ordinary tables cannot be exchanged.
  • After the DROP COLUMN operation is performed on a partitioned table or an ordinary table in MySQL, if the table structure is still consistent, data can be exchanged between partitions and ordinary tables. In GaussDB, data can be exchanged between partitions and ordinary tables only when the deleted columns of ordinary tables and partitioned tables are strictly aligned.
  • MySQL and GaussDB use different hash algorithms. Therefore, data stored in the same hash partition may be inconsistent. As a result, the exchanged data may also be inconsistent.
  • MySQL partitioned tables do not support foreign keys. If an ordinary table contains foreign keys or other tables reference foreign keys of an ordinary table, data in partitions and ordinary tables cannot be exchanged. GaussDB partitioned tables support foreign keys. If the foreign key constraints of two tables are the same, data in partitions and ordinary tables can be exchanged. If a GaussDB partitioned table does not contain foreign keys, an ordinary table is referenced by other tables, and the partitioned table is the same as the ordinary table, data in the partitioned table can be exchanged with that in the ordinary table.

8

Support auto-increment columns.

ALTER TABLE and CREATE TABLE
  • Currently, only local auto-increment columns of each DN are supported.
  • It is recommended that the auto-increment column be the first column of a non-global secondary index. Otherwise, an alarm is generated when a table is created, and errors may occur when some operations are performed on a table that contains auto-increment columns, for example, ALTER TABLE EXCHANGE PARTITION. The auto-increment column in MySQL must be the first column of the index.
  • In the syntax AUTO_INCREMENT = value, value must be a positive number less than 2^127. MySQL does not verify the value.
  • An error occurs if the auto-increment continues after an auto-increment value reaches the maximum value of a column data type. In MySQL, errors or warnings may be generated during auto-increment, and sometimes auto-increment continues until the maximum value is reached.
  • GaussDB does not support the innodb_autoinc_lock_mode system variable, but when its GUC parameter auto_increment_cache is set to 0, the behavior of inserting auto-increment columns in batches is similar to that when the MySQL system variable innodb_autoinc_lock_mode is set to 1.
  • When 0s, NULLs, and definite values are imported or batch inserted into auto-increment columns, the auto-increment values inserted after an error occurs in GaussDB may not be the same as those in MySQL.
    • The auto_increment_cache parameter is provided to control the number of reserved auto-increment values.
  • In different execution plans, the auto-increment sequence and reserved auto-increment values may be different from those in MySQL. For example, "INSERT INTO table VALUES(...),(...),..." is distributed to different DNs. Therefore, in some execution plans, DNs cannot obtain the number of rows to be inserted.
    • The auto_increment_cache parameter is provided to control the number of reserved auto-increment values.
  • When auto-increment is triggered by parallel import or insertion of auto-increment columns, the cache value reserved for each parallel thread is used only in the thread. If the cache value is not used up, the values of auto-increment columns in the table are discontinuous. The auto-increment value generated by parallel insertion cannot be guaranteed to be the same as that generated in MySQL.
  • The SERIAL data type of GaussDB is an original auto-increment column, which is different from the AUTO_INCREMENT column. The SERIAL data type of MySQL is the AUTO_INCREMENT column.
  • The value of auto_increment_offset cannot be greater than that of auto_increment_increment. Otherwise, an error occurs. MySQL allows it and states that auto_increment_offset will be ignored.
  • If a table has a primary key or index, the sequence in which the ALTER TABLE command rewrites table data may be different from that in MySQL. GaussDB rewrites table data based on the table data storage sequence, while MySQL rewrites table data based on the primary key or index sequence. As a result, the auto-increment sequence may be different.
  • When the ALTER TABLE command is used to add or modify auto-increment columns, the number of auto-increment values reserved for the first time is the number of rows in the table statistics. The number of rows in the statistics may not be the same as that in MySQL.
  • When auto-increment is performed in a trigger or user-defined function, the return value of last_insert_id is updated. MySQL does not update it.
  • If the values of the GUC parameters auto_increment_offset and auto_increment_increment are out of range, an error occurs. MySQL automatically changes the value to a boundary value.
  • The last_insert_id function is not supported.
  • Currently, local temporary tables do not support auto-increment columns.
  • If sql_mode is set to no_auto_value_on_zero, the auto-increment columns of the table are not subject to NOT NULL constraints. In GaussDB and MySQL, when the value of an auto-increment column is not specified, NULL will be inserted into the auto-increment column, but auto-increment is triggered for the former and not triggered for the latter.

9

Delete the primary key constraints of a table.

ALTER TABLE

-

10

Support the CREATE TABLE ... LIKE syntax.

CREATE TABLE ... LIKE
  • In versions earlier than MySQL 8.0.16, CHECK constraints are parsed but their functions are ignored. In this case, CHECK constraints are not replicated. GaussDB supports replication of CHECK constraints.
  • For the set data type, MySQL supports replication while GaussDB does not during table creation.
  • When a table is created, all primary key constraint names in MySQL are fixed to PRIMARY KEY. GaussDB does not support replication of primary key constraint names.
  • When a table is created, MySQL supports replication of unique key constraint names, but GaussDB does not.
  • When a table is created, MySQL versions earlier than 8.0.16 do not have CHECK constraint information, but GaussDB supports replication of CHECK constraint names.
  • When a table is created, MySQL supports replication of index names, but GaussDB does not.
  • When a table is created across sql_mode, MySQL is controlled by the loose mode and strict mode. The strict mode may become invalid in GaussDB.

    For example, if the source table has the default value "0000-00-00", GaussDB can create a table that contains the default value "0000-00-00" in "no_zero_date" strict mode, which means that the strict mode is invalid. MySQL fails to create the table because it is controlled by the strict mode.

  • MySQL supports cross-database table creation, but GaussDB does not.
  • If the source table is a temporary table, you can create a non-temporary table in MySQL but not in GaussDB.

11

Compatible with syntax for changing table names.

ALTER TABLE[ IF EXISTS ] tbl_name RENAME [TO | AS | =] new_tbl_name;

RENAME {TABLE | TABLES} tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2, ...];
  • The ALTER RENAME syntax in GaussDB supports only the function of changing the table name and cannot be coupled with other function operations.
  • In GaussDB, only the old table name column supports the usage of schema.table_name, and the new and old table names belong to the same schema.
  • GaussDB does not support renaming of old and new tables across schemas. However, if you have the permission, you can modify the names of tables in other schemas in the current schema.
  • The syntax for renaming multiple groups of tables in GaussDB supports renaming of all local temporary tables, but does not support the combination of local temporary tables and non-local temporary tables.

12

Create a partition.

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}

action [, ... ];

action:

move_clause |

exchange_clause |

row_clause |

merge_clause |

modify_clause |

split_clause |

add_clause |

drop_clause |

ilm_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})}
  • The ALTER TABLE table_name ADD PARTITION (partition_definition1, partition_definition1,…); syntax cannot be used to add multiple partitions.
  • Only the original syntax for adding multiple partitions is supported: ALTER TABLE table_name ADD PARTITION (partition_definition1), ADD PARTITION (partition_definition2[y1] ), …;.
Parent topic: SQL