Help Center> GaussDB> Service Overview> Compatibility with MySQL Databases> Primary/Standby Instances> MySQL Compatibility in M-Compatible Mode> SQL> DDL
Updated on 2024-05-31 GMT+08:00
View PDF

DDL

Description

Syntax

Difference

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

ALTER TABLE and CREATE TABLE
  • GaussDB: 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.
  • GaussDB: Foreign keys can be used as partition keys.
  • The index name, constraint name, and key name are unique in a schema in GaussDB and unique in a table in MySQL.

Support auto-increment columns.

ALTER TABLE and CREATE TABLE
  • It is recommended that an auto-increment column in GaussDB be the first column of an index. Otherwise, an alarm is generated during table creation. The auto-increment column in MySQL must be the first column of the index. Otherwise, an error is reported during table creation. In GaussDB, an error occurs when some operations (such as ALTER TABLE EXCHANGE PARTITION) are performed on a table that contains auto-increment columns.
  • For AUTO_INCREMENT = value, value must be a positive number less than 2^127 in GaussDB. In MySQL, value can be 0.
  • In GaussDB, 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.
  • In GaussDB, 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 GaussDB, 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.
  • In GaussDB, when auto-increment columns are batch inserted into a local temporary table, no auto-increment value is reserved. In normal scenarios, auto-increment values are not discontinuous. In MySQL, the auto-increment result of an auto-increment column in a temporary table is the same as that in an ordinary table.
  • 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.
  • GaussDB does not allow the value of auto_increment_offset to 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 in GaussDB 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.
  • The return value of the last_insert_id function in GaussDB is a 128-bit integer.
  • When GaussDB performs auto-increment 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 in GaussDB are out of range, an error occurs. MySQL automatically changes the value to a boundary value.

Support prefix indexes.

CREATE INDEX, ALTER TABLE, and CREATE TABLE
  • GaussDB: 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.
  • GaussDB: The primary key index does not support prefix keys. The prefix length cannot be specified when a primary key is created or added.

Specify character sets and collation rules.

ALTER SCHEMA, ALTER TABLE, CREATE SCHEMA, and CREATE TABLE
  • When you specify a database-level character set, except binary character sets, the character set of a new database or schema cannot be different from that specified by server_encoding of the database.
  • When you specify a table-level or column-level character set and collation, MySQL allows you to specify a character set and collation that are different from the database-level character set and collation. In GaussDB, the table-level and column-level character sets and collations support only the binary character sets and collations or can be the same as the database-level character sets and collations.

Add columns before the first column of a table or after a specified column during table modification.

ALTER TABLE

-

Compatible with the column name modification and the definition syntax.

ALTER TABLE

Currently, the DROP INDEX, DROP KEY, or ORDER BY is not supported.

Compatible with the syntax for creating partitioned tables.

CREATE TABLE PARTITION and CREATE TABLE SUBPARTITION
  • For partition_key, the partition key of the partitioning policy supports only one column and does not support expressions.
  • For subpartition_key, the partition key of the partitioning policy supports only one column and does not support expressions.
  • The generated column cannot be used as the partition key of a partitioned table.

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

CREATE TABLE and ALTER TABLE

-

Specify index-level comments during index creation.

CREATE INDEX

-

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

ALTER TABLE PARTITION

Differences in ALTER TABLE EXCHANGE PARTITION:

  • For auto-increment columns, after the ALTER EXCHANGE PARTITION is executed in MySQL, the auto-increment columns are reset. In GaussDB, the auto-increment columns are not reset, and the auto-increment columns increase based on the old auto-increment value.
  • 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.

Modify the partition key information of a partitioned table.

ALTER TABLE

MySQL allows you to modify the partition key information of a partitioned table, but GaussDB does not.

Compatible with 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.
  • 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.

Compatible with the subpartition addition syntax.

 
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})}

The syntax of the original partitioned table is retained.

The following syntax cannot be used to add multiple partitions:

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

Only the original syntax for adding multiple partitions is supported.

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

Compatible with the TRUNCATE subpartition syntax.

 
ALTER TABLE [ IF EXISTS ]  table_name  
    truncate_clause;

For truncate_clause, the supported subitems are different:

  • M-compatible mode:
    TRUNCATE PARTITION  { { ALL | partition_name [, ...] } | FOR (  partition_value [, ...] )  } [ UPDATE GLOBAL INDEX ]
  • MySQL:
    TRUNCATE PARTITION {partition_names | ALL}

Index name of a primary key

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

The index name created after being specified by a primary key in GaussDB is the index name specified by a user. In MySQL, the index name is PRIMARY.

Delete dependent objects.

 
DROP drop_type name CASCADE;

In GaussDB, CASCADE needs to be added to delete dependent objects. In MySQL, CASCADE is not required.

The NOT NULL constraint does not allow NULL values to be inserted.

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

In MySQL loose mode, NULL is converted and data is successfully inserted. In MySQL strict mode, NULL values cannot be inserted. GaussDB does not support this feature. NULL values cannot be inserted in loose or strict mode.

The CHECK constraint takes effect.

CREATE TABLE

The CREATE TABLE that contains the CHECK constraint takes effect in MySQL 8.0. MySQL 5.7 parses the syntax but the syntax does not take effect. GaussDB synchronizes this function of MySQL 8.0, and the GaussDB CHECK constraint can reference other columns, but MySQL cannot.

A maximum of 32767 CHECK constraints can be added to a table in GaussDB.

The algorithm and lock options of an index do not take effect.

CREATE INDEX ...

DROP INDEX ...

Currently, the index options algorithm_option and lock_option in the CREATE/DROP INDEX statement in M-compatible mode are supported only in syntax. No error is reported during creation, but they do not take effect.

The storage of hash partitions and level-2 partitions in CREATE TABLE in GaussDB is different from that in MySQL.

CREATE TABLE

In GaussDB, the hash functions used by hash partitioned tables and level-2 partitioned tables in the CREATE TABLE statement are different from those used in MySQL. Therefore, the storage of hash partitioned tables and level-2 partitioned tables is different from that in MySQL.

Partitioned table index

CREATE INDEX

GaussDB partitioned table indexes are classified into local and global indexes. A local index is bound to a specific partition, and a global index corresponds to the entire partitioned table.

For details about how to create local and global indexes and the default rules, see "SQL Syntax > SQL Statement > C > CREATE INDEX " in Developer Guide. For example, if a unique index is created on a non-partition key, a global index is created by default.

MySQL does not have global indexes. In GaussDB, if the partitioned table index is a global index, the global index is not updated by default when operations such as DROP, TRUNCATE, and EXCHANGE are performed on table partitions. As a result, the global index becomes invalid and cannot be selected in subsequent statements. To avoid this problem, you are advised to explicitly specify the UPDATE GLOBAL INDEX clause at the end of the partition syntax or set the global GUC parameter enable_gpi_auto_update to true (recommended) so that global indexes can be automatically updated during partition operations.

The escape character (\) is not supported in COMMENT.

CREATE TABLE ... COMMENT 'string'

CREATE INDEX ...COMMENT 'string'

GaussDB: The escape character (\) is not supported in COMMENT.

If the table is partitioned by key in the CREATE/ALTER TABLE statement, algorithms cannot be specified. Input parameters of some partition definition do not support expressions.

CREATE TABLE and ALTER TABLE

GaussDB: If the table is partitioned by key in the CREATE/ALTER TABLE statement, algorithms cannot be specified.

The syntaxes that do not support expressions as input parameters are as follows:
  • PARTITION BY HASH()
  • PARTITION BY KEY()
  • VALUES LESS THAN()

Partitioned tables do not support LINEAR/KEY hash.

CREATE TABLE ... PARTITION ...

GaussDB: Partitioned tables do not support LINEAR/KEY hash.

The CHECK and AUTO_INCREMENT syntaxes cannot be used in the same column.

CREATE TABLE

The column using CHECK does not take effect in MySQL 5.7. When both CHECK and AUTO_INCREMENT are used on the same column, only AUTO_INCREMENT takes effect. However, GaussDB reports an error.

Delete dependent tables.

DROP TABLE

In GaussDB, CASCADE must be added to delete dependent tables. In MySQL, CASCADE is not required.

SET does not support FOREIGN_KEY_CHECKS.

SET

Currently, GaussDB does not support SET FOREIGN_KEY_CHECKS=0|1.

Options related to table definition.

CREATE TABLE ... and ALTER TABLE ...
  • GaussDB does not support the following options: 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_PRESISTENT, and STATS_SAMPLE_PAGES.
  • The following options do not report errors in GaussDB and do not take effect: ENGINE and ROW_FORMAT.

Encrypt the CMKs of CEKs in round robin (RR) mode and encrypt the plaintext of CEKs.

ALTER COLUMN ENCRYPTION KEY

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

The encrypted equality query feature adopts a multi-level encryption model. The master key encrypts the column key, and the column key encrypts data. This syntax is used to create a master key object.

CREATE CLIENT MASTER KEY

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Create a CEK that can be used to encrypt a specified column in a table.

CREATE COLUMN ENCRYPTION KEY

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Send keys to the server for caching. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\send_token

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Send keys to the server for caching. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\st

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Destroy the keys cached on the server. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\clear_token

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Destroy the keys cached on the server. This function is used only when the memory decryption emergency channel is enabled. This is a fully-encrypted function.

\ct

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Set the parameters for accessing the external key manager in the fully-encrypted database features.

\key_info KEY_INFO

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Enable third-party dynamic libraries and set related parameters. This is a fully-encrypted function.

\crypto_module_info MODULE_INFO

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

Enable third-party dynamic libraries and set related parameters. This is a fully-encrypted function.

\cmi MODULE_INFO

The M-compatible mode does not support the full encryption. Therefore, this syntax is not supported.

The GENERATED ALWAYS AS statement cannot reference columns generated by GENERATED ALWAYS AS.

Generated Always AS

In GaussDB, the GENERATED ALWAYS AS statement cannot reference columns generated by GENERATED ALWAYS AS, but it can in MySQL.

Currently, ALTER TABLE does not support the RENAME = syntax.

ALTER TABLE

In GaussDB, ALTER TABLE supports only RENAME TO and RENAME AS syntaxes. In MySQL, it supports RENBAME TO, RENAME AS, and RENAME = syntaxes.

Disable the GUC parameter enable_expr_fusion.

SET enable_expr_fusion= ON

In M-compatible mode, the GUC parameter enable_expr_fusion cannot be enabled.

Compatible with the CREATE VIEW AS SELECT syntax.

 
CREATE VIEW table_name AS query;
  • For the following types, the query using the CREATE VIEW view_name AS query syntax cannot contain calculation operations (such as function call and calculation using operators):
    • 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
  • In the simple query scenario, an error message is displayed for the preceding calculation operations in M-compatible mode. For example:
    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 TABLE TEST (salary int(10));
CREATE TABLE
m_db=# INSERT INTO TEST VALUES(8000);
INSERT 0 1
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.
  • In non-simple query scenarios such as composite query and subquery, the calculation operations of the preceding types in M-compatible mode are different from those in MySQL. In M-compatible mode, the data type column precision attribute of the created table is not retained.

Range of index names that can be duplicated

CREATE TABLE, CREATE INDEX

In MySQL, an index name is unique in a table. Different tables can have the same index name. In M-compatible mode, the index name must be unique in the same schema. In M-compatible mode, the same rules apply to constraints and keys that automatically create indexes.

View dependency differences

CREATE VIEW and ALTER TABLE

In MySQL, view storage records only the table name, column name, and database name of the target table, but does not record the unique identifier of the target table. GaussDB parses the SQL statement used for creating a view and stores the unique identifier of the target table. Therefore, the differences are as follows:

  1. In MySQL, you can modify the data type of a column on which a view depends because the view is unaware of the modification of the target table. In GaussDB, such modification is forbidden and the attempt will fail.
  2. In MySQL, you can rename a column on which a view depends because the view is unaware of the modification of the target table, but the view cannot be queried after the operation. In GaussDB, each column precisely stores the unique identifier of the corresponding table and column. Therefore, the column name in the table can be modified successfully without changing the column name in the view. In addition, the view can be queried after the operation.

Foreign key differences

CREATE TABLE

GaussDB foreign key constraints are insensitive to types. If the data types of the fields in the main and child tables are implicitly converted, foreign keys can be created. MySQL are sensitive to foreign key types. If the column types of the two tables are different, foreign keys cannot be created.

MySQL does not allow you to modify the data type or name of a table column where the foreign key of the column is located by running MODIFY COLUMN or CHANGE COLUMN, but GaussDB supports such operation.

Differences in index ascending and descending orders

CREATE INDEX

In MySQL 5.7, ASC | DESC is parsed but ignored, and the default behavior is ASC. In MySQL 8.0 and GaussDB, ASC | DESC is parsed and takes effect.
Parent topic: SQL