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

DDL

Table 1 DDL syntax compatibility

Description

Syntax Description

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.
  • The maximum number of columns supported by the primary keys of MySQL is different from those of GaussDB.

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.
  • In GaussDB, 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.
  • 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.

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.
  • If the character set or collation is specified repeatedly, only the last one takes effect.

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

ALTER TABLE

-

Alter the column name/definition.

ALTER TABLE
  • Currently, the DROP INDEX, DROP KEY, or ORDER BY is not supported.
  • When ALTER TABLE is used to add a column, if the specified field in MySQL is NOT NULL, the NULL value is converted to the default value of the corresponding type and inserted into the column. GaussDB checks the NULL value.

Create a partitioned table.

CREATE TABLE PARTITION and CREATE TABLE SUBPARTITION
  • MySQL supports expressions but does not support multiple partition keys in the following scenarios:
    • The LIST/RANGE partitioning policy is used and the COLUMNS keyword is not specified.
    • The hash partitioning policy is used.
  • MySQL does not support expressions and supports multiple partition keys in the following scenarios:
    • The LIST/RANGE partitioning policy is used and the COLUMNS keyword is specified.
    • The KEY partitioning policy is used.
  • In GaussDB, expressions cannot be used as partition keys.
  • GaussDB supports multiple partition keys only when the LIST or RANGE partitioning policy is used and subpartitions are not specified.
  • In GaussDB partitioned tables, virtual generated columns cannot be used as partition keys.

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.

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

Create a partition.

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

Truncate a partition.

 
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 the table partition. 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.

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.

Add foreign key constraints and modify referencing columns and referenced columns of the foreign key constraints.

CREATE TABLE and ALTER TABLE
  • In GaussDB, the MATCH FULL and MATCH SIMPLE options can be specified when you are creating a foreign key. However, if you specify the MATCH PARTIAL option, an error is reported. In MySQL, the preceding options can be specified, but will not be effective. Their behavior ends up being the same as that of MATCH SIMPLE.
  • In GaussDB, the ON [ UPDATE | DELETE ] SET DEFAULT option can be specified when you are creating a foreign key. In MySQL, if you specify the ON [ UPDATE | DELETE ] SET DEFAULT option when creating a foreign key, an error is reported.
  • When creating a foreign key in GaussDB, you must create a unique index on the referenced column of the referenced table. When creating a foreign key in MySQL, you need to create an index on the referenced column of the referenced table. The index can be not unique.
  • When creating a foreign key in GaussDB, you do not need to create an index on the referencing column of the referencing table. When creating a foreign key in MySQL, you need to create an index on the referencing column of the referencing table. Otherwise, a corresponding index is automatically added. If the foreign key is deleted, this index is not deleted.
  • In GaussDB, referencing tables and referenced tables can be temporary tables. Foreign keys cannot be created between temporary tables and non-temporary tables. In MySQL, temporary tables cannot be used as referencing tables or referenced tables. When a foreign key is created to specify a referenced table, MySQL does not match the temporary table created in the current session.
  • When you are creating a foreign key in GaussDB, it is optional to specify the referenced field name of the referenced table. In this case, the primary key in the referenced table is used as the referenced field of the foreign key. In MySQL, the referenced field of the referenced table must be specified.
  • In GaussDB, the data type of a referencing field or referenced field can be modified regardless of whether foreign_key_checks is disabled. In MySQL, you can change the data type of a referencing field or referenced field only when foreign_key_checks is set to off.
  • In GaussDB, you can delete referencing fields from a referencing table. In this case, related foreign key constraints are deleted cascadingly. Attempts to delete referencing field in a referencing table will fail in MySQL.
  • In GaussDB, if foreign_key_checks is set to on and a referenced table and a referencing table are in different schemas, when the schema that contains the referenced table is deleted, foreign key constraints on the referencing table are deleted cascadingly. In MySQL, if foreign_key_checks is set to on, the deletion fails.

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_PERSISTENT, 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.

Support syntaxes that change table names.

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

Or 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.

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.

Support the CREATE VIEW AS SELECT syntax.

 
CREATE VIEW table_name AS query;
  • When the precision transfer function is disabled (m_format_behavior_compat_options is not set to enable_precision_decimal), the "query" in the CREATE VIEW view_name AS query syntax cannot contain calculation operations (such as function calling and calculation using operators) for the following types. Only direct column calling is supported (such as SELECT col1 FROM table1). It can be used when the precision transfer function is enabled (m_format_behavior_compat_options is set to enable_precision_decimal).
    • BINARY[(n)]
    • 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.
  • CREATE VIEW AS SELECT. When a UNION is nested with a subquery, MySQL creates a temporary table for the subquery. If the return type of a temporary table is tinytext, text, mediumtext, or longtext, MySQL performs calculation based on the default maximum byte length of the type. However, GaussDB performs calculation based on the actual byte length of the created temporary table. Therefore, the text type of the GaussDB aggregation result may be smaller than that of the MySQL aggregation result. For example, longtext is returned for MySQL, and mediumtext is returned for GaussDB. For example:

    Behavior in MySQL 5.7:

    mysql> CREATE TABLE IF NOT EXISTS tb_1 (id int,col_text2 text);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE IF NOT EXISTS tb_2 (id int,col_text2 text);
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE VIEW v1 AS SELECT * FROM (SELECT cast(col_text2 AS char) c37 FROM tb_1) t1
    -> UNION ALL SELECT * FROM (SELECT cast(col_text2 as char) c37 FROM tb_2) t2;
Query OK, 0 rows affected (0.00 sec)

mysql> DESC v1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c37   | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

    Behavior in GaussDB:

    mysql_regression=# CREATE TABLE IF NOT EXISTS tb_1 (id int,col_text2 text);
CREATE TABLE
mysql_regression=# CREATE TABLE IF NOT EXISTS tb_2 (id int,col_text2 text);
CREATE TABLE
mysql_regression=# CREATE VIEW v1 AS SELECT * FROM (SELECT cast(col_text2 AS char) c37 from tb_1) t1
mysql_regression-# UNION ALL SELECT * FROM (SELECT cast(col_text2 AS char) c37 FROM tb_2) t2;
CREATE VIEW
mysql_regression=# DESC v1;
 Field |    Type    | Null | Key | Default | Extra 
-------+------------+------+-----+---------+-------
 c37   | mediumtext | YES  |     |         | 
(1 row)
  • When the bitstring constant is used to create a view, the constant is converted into hexstring for creation in MySQL, whereas the bitstring constant is used directly to create a view in GaussDB. The bitstring constant is an unsigned value. Therefore, the attribute of the view created in GaussDB is unsigned.
    • Behavior in MySQL 5.7:
    mysql> SELECT version();
+------------------+
| version()        |
+------------------+
| 5.7.44-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE VIEW v1 AS SELECT b'101'/b'101' AS c22;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC v1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| c22   | decimal(5,4) unsigned | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SHOW CREATE VIEW v1;
+------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                  | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`omm`@`%` SQL SECURITY DEFINER VIEW `v1` AS select (0x05 / 0x05) AS `c22` | utf8mb4              | utf8mb4_general_ci   |
+------+--------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
    • Behavior in GaussDB:
    m_db=# DROP VIEW IF EXISTS v1;
DROP VIEW
m_db=# CREATE VIEW v1 AS SELECT b'101'/b'101' AS c22;
CREATE VIEW
m_db=# DESC v1;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 c22   | decimal(5,4) | YES  |     |         | 
(1 row)

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.

Modifying a view definition

CREATE OR REPLACE VIEW and ALTER VIEW

In MySQL, you can modify any attribute of a view. In GaussDB, names and types of columns in non-updatable views cannot be modified and the columns cannot be deleted, but these operations are allowed in updatable views.

In MySQL, after a column in the underlying view of a nested view is modified, the upper-level views can be used as long as the column name exists. In GaussDB, after the name or type of a column in the underlying view of a nested view is modified or the column is deleted, the upper-layer views are unavailable.

ANALYZE partition syntax

 
ALTER TABLE tbl_name ANALYZE PARTITION {partition_names | ALL}
  • In GaussDB, this syntax supports only partition statistics collection.
  • In MySQL, partition_names is case-insensitive. In GaussDB, partition_names with backquotes are case-insensitive, but the one without backquotes are case-sensitive.
  • In GaussDB, ALTER TABLE is displayed if the execution is successful. The execution error is reported based on the existing error code. In MySQL, the execution result is displayed in a table.

Supports the syntax of virtual generated columns.

[GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]
  • Indexes can be created for virtual generated columns in MySQL, but cannot in GaussDB.
  • Virtual generated columns can be used as partition keys in MySQL, but cannot in GaussDB.
  • The CHECK constraint of generated columns in GaussDB is compatible with that in MySQL 8.0. Therefore, the CHECK constraint is effective.
  • In MySQL, ALTER TABLE can be used to modify the stored generated columns that are considered as partition keys. GaussDB does not support this operation.
  • In MySQL, when data in generated columns of an updatable view is updated, the DEFAULT keyword can be specified. In GaussDB, this operation is not supported.
  • IGNORE feature is supported by virtual generated columns in MySQL, but not in GaussDB.
  • Querying a virtual generated column in GaussDB is equivalent to querying the expression of the virtual generated column. (If the data type, character set, or collation defined in the expression is inconsistent with that defined in the column, the expression type is implicitly converted to the type defined in the column) This behavior is to query virtual generated columns that are used for creating tables or views or other behaviors. As a result, the data type of such columns may be different from those in MySQL. For example, when CREATE TABLE AS is used to create a table, if the virtual generated column in the source table is defined as the FLOAT type, the data type of the corresponding column in the target table may be DOUBLE, which is different from that in MySQL.

Create a table and insert data into the table using CREATE TABLE SELECT.

CREATE TABLE [AS] SELECT
  • Partitioned tables cannot be created.
  • REPLACE/IGNORE is not supported.
  • If the SELECT column is not a direct table column, NULL is allowed by default, and there is no default value. For example, the field a in a table created by running create table t1 select unix_timestamp('2008-01-02 09:08:07.3465') as a can be NULL and its default value is not required.
  • To use all functions, you need to set the GUC parameter m_format_behavior_compat_options to enable_precision_decimal. Otherwise, a behavior error will be reported for types related to data type precision due to version compatibility issues. For example, an error is reported in the UNION scenario or when a SELECT column contains a non-direct table column (such as expressions, functions, and constants).
  • When CREATE TABLE AS SELECT is used to create a table, the maximum length of a column name in the table is 63 characters. If the length exceeds 63 characters, the excess part will be truncated. If the length exceeds 64 characters (the maximum) in MySQL, an error is reported.

ALTER TABLE tabname;

ALTER TABLE tabname;

In GaussDB, tablename cannot be empty.

The column_list of a partition key cannot be empty.

CREATE TABLE ... PARTITION ...

In GaussDB, column_list of a partition key cannot be empty.

The maximum length of the UTF-8 character set code is different. As a result, the column length of a created table or view is different.

CREATE TABLE [AS] SELECT; CREATE VIEW [AS] SELECT
  • If the MySQL character set is utf8 and the GaussDB character set is utf8 (utf8mb4), the maximum length of the UTF-8 code of MySQL is 3 bytes, and the maximum length of the UTF-8 (utf8mb4) code of GaussDB is 4 bytes. When the GUC parameter m_format_behavior_compat_options is set to 'enable_precision_decimal', create table as (CTAS) and create view as (CVAS) may create different text types (including binary text).

    The returned character length in the CTAS and CVAS scenarios depends on the maximum length of the character set. For example, if the maximum length of the character set returned by a node is 1024 for both GaussDB and MySQL, the length of the returned characters is 341 (1024/3) for MySQL and 256 (1024/4) for GaussDB. For example:

    Behavior in MySQL 5.7:

    mysql> CREATE TABLE t1 AS SELECT (case when true then min(521.2312) else GROUP_CONCAT(115.0414) end) res1;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| res1  | varchar(341) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

    Behavior in GaussDB:

    mysql_regression=# CREATE TABLE t1 AS SELECT (case when true then min(521.2312) else GROUP_CONCAT(115.0414) end) res1;
INSERT 0 1
mysql_regression=# DESC t1;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 res1  | varchar(256) | YES  |     |         | 
(1 row)

Setting default values of columns

CREATE TABLE and ALTER TABLE
  • For MySQL 5.7, only the default value without parentheses is supported. MySQL 8.0 and GaussDB support default values in parentheses. 
    -- 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

-- MySQL5.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

-- MySQL8.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)
  • In MySQL, when specifying default values for BLOB, TEXT, and JSON data types, you must add parentheses to the default values. In GaussDB, you do not need to add parentheses when specifying default values for the preceding data types.
  • When the default value is specified, GaussDB does not check whether the default value overflows. When the default value without parentheses is specified in MySQL, MySQL checks whether the default value overflows. When the default value with parentheses is specified, MySQL does not check whether the default value overflows.
  • In GaussDB, time constants starting with DATE, TIME, or TIMESTAMP can be used to specify default values for columns. In MySQL, when time constants starting with DATE, TIME, or TIMESTAMP are used to specify default values for columns, parentheses must be added to the default values.
-- 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

-- MySQL5.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

-- MySQL8.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)
Parent topic: SQL