Updated on 2026-05-16 GMT+08:00

DDL

Table 1 DDL syntax compatibility

Overview

Syntax

Difference

Primary keys and UNIQUE indexes can be created 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. MySQL supports the syntax.
  • 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.

Prefix indexes are supported.

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 or unique key indexes.

Character sets and collations can be specified.

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

-

Syntax compatibility for creating partitioned tables

CREATE TABLE PARTITION

-

Table-level and column-level comments can be specified during table creation and modification.

CREATE TABLE and ALTER TABLE

-

Index-level comments can be specified during index creation.

CREATE INDEX

-

Partition data of an ordinary table and a partitioned table can be exchanged.

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 they 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's partitioned tables do not support foreign keys. If an ordinary table contains foreign keys or other tables reference the foreign keys of an ordinary table, data in partitions and ordinary tables cannot be exchanged. GaussDB's 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 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, GaussDB also allows the partitioned table to exchange data with the ordinary table.

Auto-increment columns are supported.

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.
  • If the auto-increment value has reached the maximum value of the field data type, an error is generated when the auto-increment value continues to increase. In MySQL, errors or warnings are generated in some scenarios, and the value is automatically increased to the maximum value in some scenarios.
  • 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 0, NULL, 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.
  • When data is inserted in batches using different execution plans, the auto-increment sequence and the number of reserved auto-increment values may be different from those in MySQL. For example, in the "INSERT INTO table VALUES(...),(...),..." statement, data is distributed to different DNs. In some execution plans, the 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 an auto-increment column, 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 be different from 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 the value.
  • 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.
  • 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.

The primary key constraint of a table can be deleted.

ALTER TABLE

-

The CREATE TABLE ... LIKE syntax is supported.

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.
  • When a table is created, MySQL supports replication of the SET data type, but GaussDB does not.
  • During table creation, all primary key constraint names in MySQL are fixed to PRIMARY KEY, but GaussDB does not support replication.
  • When a table is created, MySQL supports replication of unique key constraint names, but GaussDB does not support replication.
  • 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 support replication.
  • 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", a table with the default value "0000-00-00" can be created in "no_zero_date" strict mode in GaussDB, which means that the strict mode is invalid. In MySQL, the table creation will fail 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. MySQL supports this function, but GaussDB does not.

Compatible syntax for altering table names is supported.

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 of GaussDB supports only the table name change function and cannot be coupled with other functions.
  • In GaussDB, only the old table name column supports the schema.table_name format, 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.

Compatible syntax for adding subpartitions is supported.

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]), ...;