DML

1

DELETE supports deleting data from a specified partition (or subpartition).

DELETE

-

2

UPDATE supports ORDER BY and LIMIT.

UPDATE

-

3

Support the SELECT INTO syntax.

SELECT

• In GaussDB, you can use SELECT INTO to create a table based on the query result. MySQL does not support this function.
• In GaussDB, the SELECT INTO syntax does not support the query result that is obtained after the set operation of multiple queries is performed.

4

Support the REPLACE INTO syntax.

REPLACE

5

SELECT supports multi-partition query.

SELECT

-

6

UPDATE supports multi-partition update.

UPDATE

-

7

Import data by using LOAD DATA.

LOAD DATA

When LOAD DATA is used to import data, GaussDB differs from MySQL in the following aspects:

• The execution result of the LOAD DATA syntax is the same as that in M* strict mode. The loose mode is not adapted currently.
• The IGNORE and LOCAL parameters are used only to ignore the conflicting rows when the imported data conflicts with the data in the table and to automatically fill default values for other columns when the number of columns in the file is less than that in the table. Other functions are not supported currently.
• If the keyword LOCAL is specified and the file path is a relative path, the file is searched from the binary directory. If the keyword LOCAL is not specified and the file path is a relative path, the file is searched from the data directory.
• LOAD DATA can only be used to import files from the server.
• The [(col_name_or_user_var [, col_name_or_user_var]...)] parameter cannot be used to specify a column repeatedly.
• The newline character specified by [FIELDS TERMINATED BY 'string'] cannot be the same as the separator specified by [LINES TERMINATED BY'string'].
• If the data written to a table by running LOAD DATA cannot be converted to the data type of the table, an error is reported.
• The LOAD DATA SET expression does not support the calculation of a specified column name.
• LOAD DATA applies only to tables but not views.
• The default newline character of the file in Windows is different from that in Linux. LOAD DATA cannot identify this scenario and reports an error. You are advised to check the newline character at the end of lines in the file to be imported.

8

INSERT supports the VALUES reference column syntax.

INSERT INTO tabname VALUES(1,2,3) ON DUPLICATE KEY UPDATE b = VALUES(column_name)

The format of table-name.column-name is not supported by VALUES() in the ON DUPLICATE KEY UPDATE clause in GaussDB, but is supported in MySQL.

9

LIMIT differences

DELETE, SELECT, and UPDATE

The LIMIT clauses of each statement in GaussDB are different from those in MySQL.

The maximum parameter value of LIMIT (of the BIG INT type) in GaussDB is 9223372036854775807. If the actual value exceeds the number, an error is reported. In MySQL, the maximum value of LIMIT (of the unsigned LONGLONG type) is 18446744073709551615. If the actual value exceeds the number, an error is reported.

You can set a small value in LIMIT, which is rounded off during execution. The value cannot be a decimal in MySQL.

In GaussDB, limit 0 is not allowed in the DELETE statement. In MySQL, limit 0 is allowed in the DELETE statement.

10

Difference in using backslashes (\)

INSERT

The usage of backslashes (\) can be determined by parameters in GaussDB and MySQL, but their default usages are different.

In MySQL, the NO_BACKSLASH_ESCAPES parameter is used to determine whether backslashes (\) in character strings and identifiers are parsed as common characters or escape characters. By default, backslashes (\) are parsed as escape characters in character strings and identifiers. If set sql_mode is set to 'NO_BACKSLASH_ESCAPES', backslashes (\) cannot be parsed as escape characters in character strings and identifiers.

In GaussDB, the standard_conforming_strings parameter is used to determine whether backslashes (\) in character strings and identifiers are parsed as common characters or escape characters. The default value is on, indicating that backslashes (\) are parsed as common text in common character string texts according to the SQL standard. If set standard_conforming_strings is set to off, backslashes (\) can be parsed as escape characters in character strings and identifiers.

11

If the inserted value is less than the number of columns, MySQL reports an error while GaussDB supplements null values.

INSERT

In GaussDB, if the column list is not specified and the inserted value is less than the number of columns, values are assigned based on the column sequence when the table is created by default. If a column has a NOT NULL constraint, an error is reported. If no NOT NULL constraint exists and a default value is specified, the default value is added to the column. If no default value is specified, null is added.

12

The columns sorted in ORDER BY must be included in the columns of the result set.

SELECT

In GaussDB, when used with the GROUP BY clause, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement. When used with the DISTINCT keyword, the columns to be sorted in ORDER BY must be included in the columns of the result set retrieved by the SELECT statement.

13

Do not use ON DUPLICATE KEY UPDATE to modify constraint columns.

INSERT

-

14

Duplicate column names are allowed in the SELECT result.

SELECT

-

15

NATURAL JOIN in GaussDB is different from that in MySQL.

SELECT

In GaussDB, NATURAL [ [LEFT | RIGHT] OUTER] JOIN allows you not to specify LEFT | RIGHT. If LEFT | RIGHT is not specified, NATURAL OUTER JOIN is NATURAL JOIN. You can use JOIN consecutively.

16

If the foreign key data type is timestamp or datetime, an error is reported for attempts to update or delete the foreign table.

UPDATE/DELETE

If the foreign key data type is timestamp or datetime, an error is reported for attempts to update or delete the foreign table, but in MySQL the table can be updated or deleted.

17

Compatibility in terms of nature join and using

SELECT

• In GaussDB, join sequence is strictly from left to right. MySQL may adjust the sequence.
• In GaussDB and MySQL, columns involving join in the left or right table cannot be ambiguous during natural join or using. (Generally, ambiguity is caused by duplicate names of columns in the left or right temporary table.) The join sequence differs in two databases, which may lead to different behaviors.
  • Behavior in GaussDB:

    m_regression=# CREATE TABLE t1(a int,b int);
    CREATE TABLE
    m_regression=# CREATE TABLE t2(a int,b int);
    CREATE TABLE
    m_regression=# CREATE TABLE t3(a int,b int);
    CREATE TABLE
    m_regression=# SELECT * FROM t1 JOIN t2;
     a | b | a | b 
    ---+---+---+---
    (0 rows)
    m_regression=# SELECT * FROM t1 JOIN t2 natural join t3; -- Failed. Duplicate contents exist in columns a and b of the temporary table obtained by t1 join t2. Therefore, there is ambiguity in nature join.
    ERROR:  common column name "a" appears more than once in left table

  • Behavior in MySQL:

    mysql> SELECT * FROM t1 JOIN t2 NATURAL JOIN t3;
    Empty set (0.00 sec)
    mysql> SELECT * FROM (t1 join t2) NATURAL JOIN t3;
    ERROR 1052 (23000): Column 'a' in from clause is ambiguous

18

The WITH clause is compatible with MySQL 8.0.

SELECT, INSERT, UPDATE, and DELETE

-

19

Compatibility in terms of join

SELECT

Commas (,) cannot be used as a way of join in GaussDB, but can be used in MySQL.

GaussDB does not support use index for join.

20

If the column expression in the SELECT statement is a function expression or arithmetic expression, the column name in the query result is ?column?.

SELECT

In GaussDB, if the column expression in the SELECT statement is a function expression or arithmetic expression, the column name in the query result is ?column?. In MySQL, the name is the corresponding expression.

21

SELECT export file (into outfile)

SELECT ... INTO OUFILE ...

In the file exported by using the SELECT INTO OUTFILE syntax, the display precision of values of the FLOAT, DOUBLE, and REAL types in GaussDB is different from that in MySQL. The syntax does not affect the import using COPY the values after import.

22

UPDATE/INSERT/REPLACE ... SET specifies the schema name and table name.

UPDATE/INSERT/REPLACE ... SET

The three-segment format for UPDATE/REPLACE SET is database.table.column in MySQL, and is table.column.filed in GaussDB, where filed indicates the attribute in the specified composite type.

For INSERT ... SET, MySQL supports column, table.column, and database.table.column. GaussDB supports only column and does not support table.column and database.table.column.

23

The execution sequence of UPDATE SET is different from that of MySQL.

UPDATE ... SET

In MySQL, UPDATE SET is performed in sequence. The results of UPDATE at the front affect subsequent results of UPDATE, and the same column can be set for multiple times. In GaussDB, all related data is obtained first, and then UPDATE is performed on the data at a time. The same column cannot be set for multiple times.

24

IGNORE feature

UPDATE/DELETE/INSERT

The execution process in MySQL is different from that in GaussDB. Therefore, the number and information of generated warnings may be different.

25

SHOW COLUMNS syntax

SHOW

• User permission verification is different from that of MySQL.
  • In GaussDB, you need the USAGE permission on the schema of a specified table and table-level or column-level permissions on the specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed.
  • In MySQL, you need table-level or column-level permissions on a specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed.
• When the LIKE and WHERE clauses involve string comparison operations, the collation is different from that in MySQL.
  • utf8_general_ci is used in MySQL.
  • The collation_connection of the current client is used as the collation in GaussDB.
    In GaussDB, you are advised not to select columns other than the returned fields in the WHERE clause. Otherwise, unexpected errors may occur.

    -- Expected error
    m_db=# SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
    ERROR:  Column "b" does not exist.
    LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `b`='pri';
                                             ^
    
    -- Unexpected error
    m_db=# SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
    ERROR:  input of anonymous composite types is not implemented
    LINE 1: SHOW FULL COLUMNS FROM t02 WHERE `c`='pri';
                                                 ^

26

SHOW CREATE DATABASE syntax

SHOW

User permission verification is different from that of MySQL.

• In GaussDB, you need the USAGE permission on a specified schema.
• In MySQL, you need database-level permissions (except GRANT OPTION and USAGE), table-level permissions (except GRANT OPTION), or column-level permissions.

27

SHOW CREATE TABLE syntax

SHOW

• User permission verification is different from that of MySQL.
  • In GaussDB, you need the USAGE permission on the schema where a specified table is located and table-level permissions on the specified table.
  • Table-level permissions (except GRANT OPTION) of the specified table are required in MySQL.
• The returned statements for table creation are different from those in MySQL.
  • In GaussDB, indexes are returned as CREATE INDEX statements. In MySQL, indexes are returned as CREATE TABLE statements. In GaussDB, the range of optional parameters supported by the CREATE INDEX syntax is different from that supported by the CREATE TABLE syntax. Therefore, some indexes cannot be created in CREATE TABLE statements.
  • In GaussDB, the ENGINE and ROW_FORMAT options of CREATE TABLE are adapted only for the syntax but do not take effect. Therefore, they are not displayed in the returned statements for table creation.
• These statements are compatible with MySQL only after the compatibility parameter m_format_dev_version is set to 's2'. The compatibility parameter takes effect by changing the positions of column comments, table comments, ON COMMIT option for global temporary tables, primary key and unique constraints (where the USING INDEX TABLESPACE option is no longer displayed), and index comments.

28

SHOW CREATE VIEW syntax

SHOW

• User permission verification is different from that of MySQL.
  • In GaussDB, you need the USAGE permission on the schema where a specified view is located and table-level permissions on the specified view.
  • In MySQL, you need the table-level SELECT and table-level SHOW VIEW permissions on the specified view.
• The returned statements for view creation are different from those in MySQL. If a view is created in the format of SELECT * FROM tbl_name, * is not expanded in GaussDB but expanded in MySQL.
• The character_set_client and collation_connection fields in the returned result are different from those in MySQL.
  • The session values of system variables character_set_client and collation_connection are displayed during view creation in MySQL
  • Related metadata is not recorded in GaussDB and NULL is displayed.

29

SHOW PROCESSLIST syntax

SHOW

In GaussDB, the field content and case in the query result of this command are the same as those in the information_schema.processlist view. In MySQL, the field content and case may be different.

• In GaussDB, common users can access only their own thread information. Users with the SYSADMIN permission can access thread information of all users.
• In MySQL, common users can access only their own thread information. Users with the PROCESS permission can access thread information of all users.

30

SHOW [STORAGE] ENGINES

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.engines view. In MySQL, they may be different from those in the view. The query results of this command are different in MySQL and GaussDB because the databases have different storage engines.

31

SHOW [SESSION] STATUS

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.session_status view. In MySQL, they may be different from those in the view. Currently, GaussDB supports only Threads_connected and Uptime.

32

SHOW [GLOBAL] STATUS

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.global_status view. In MySQL, they may be different from those in the view. Currently, GaussDB supports only Threads_connected and Uptime.

33

SHOW INDEX

SHOW

• User permission verification is different from that of MySQL.
  • In GaussDB, you need the USAGE permission on a specified schema and table-level or column-level permissions on a specified table.
  • In MySQL, you need table-level (except GRANT OPTION) or column-level permission on the specified table.

• Temporary tables in GaussDB are stored in independent temporary schemas. When using the FROM or IN db_name condition to display the index information of a specified temporary table, you must specify db_name as the schema where the temporary table is located. Otherwise, the system displays a message indicating that the temporary table does not exist. This is different from MySQL in some cases.

34

SHOW SESSION VARIABLES

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.session_variables view. In MySQL, they may be different from those in the view.

35

SHOW GLOBAL VARIABLES

SHOW

In GaussDB, the field content and case of the query result of this command are the same as those in the information_schema.global_variables view. In MySQL, they may be different from those in the view.

36

SHOW CHARACTER SET

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.character_sets view. In MySQL, they may be different from those in the view.

37

SHOW COLLATION

SHOW

In GaussDB, the field content and case of the query result are the same as those in the information_schema.collations view. In MySQL, they may be different from those in the view.

38

EXCEPT Syntax

SELECT

-

39

SELECT supports the STRAIGHT_JOIN syntax.

SELECT

The execution plans generated in the multi-table JOIN scenarios in GaussDB may be different from those in MySQL.

40

SHOW TABLES

SHOW

• The LIKE behavior is different. For details, see "LIKE" in Operators.
• The WHERE expression behavior is different. For details, see "WHERE" in GaussDB.
• In GaussDB, permissions on tables and databases must be assigned to users separately. The database to be queried must be available to users on the SHOW SCHEMAS. Users must have permissions on both tables and databases. MySQL can be accessed as long as you have table permissions.
• In GaussDB, the verification logic preferentially checks whether a schema exists and then checks whether the current user has the permission on the schema, which is different from that in MySQL.

41

SHOW TABLE STATUS

SHOW

• In GaussDB, the syntax displays data depending on the tables view under information_schema. In MySQL, the tables view specifies tables.
• In GaussDB, permissions on tables and databases must be assigned to users separately. The database to be queried must be available to users on the SHOW SCHEMAS. Users must have permissions on both tables and databases. MySQL can be accessed as long as you have table permissions.
• In GaussDB, the verification logic preferentially checks whether a schema exists and then checks whether the current user has the permission on the schema, which is different from that in MySQL.

42

HAVING syntax

SELECT

In GaussDB, HAVING can only reference columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports more: it allows HAVING to reference SELECT columns in the list and columns in external subqueries.

43

SELECT followed by a row expression

SELECT

In MySQL, SELECT cannot be followed by a row expression, but in GaussDB, SELECT can be followed by a row expression.

Behavior in MySQL:

mysql> SELECT row(1,2);
ERROR 1241 (21000): Operand should contain 1 column(s)

Behavior in GaussDB:

m_db=# SELECT row(1,2);
 row(1,2) 
----------
 (1,2)
(1 row)

44

SELECT FOR SHRAE/FOR UPDATE/LOCK IN SHRAE MODE

SELECT

• The FOR SHARE/FOR UPDATE/LOCK IN SHARE MODE and UNION/EXCEPT/DISTINCT/GROUP BY/HAVING clauses cannot be used together in GaussDB. They can be used together in MySQL 5.7 (except in the FOR SHARE/EXCEPT syntax) and MySQL 8.0.
• When a lock clause is used together with the LEFT/RIGHT [OUTER] JOIN clause, the LEFT JOIN cannot be used to lock the right table, and the RIGHT JOIN clause cannot be used to lock the left table. In MySQL, tables on both sides of JOIN can be locked at the same time.

45

SELECT syntax

SELECT

• In GaussDB, when the table alias in the FROM clause is specified, the table alias can contain the column name. In MySQL 5.7, the table alias cannot contain the column name. In MySQL 8.0, the table alias can contain the column name only when the subquery is specified.

-- GaussDB
m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
m_db=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE
m_db=# INSERT INTO t1 VALUES(1,2);
INSERT 0 1
m_db=# SELECT * FROM t1 t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

m_db=# SELECT * FROM (SELECT * FROM t1) t2(a, b);
 a | b 
---+---
 1 | 2
(1 row)

-- MySQL5.7
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1 t2(a, b);
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 '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
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 '(a, b)' at line 1

-- MySQL8.0
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t1(a INT, b INT);
Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO t1 VALUES(1,2);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM t1 t2(a, b);
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 '(a, b)' at line 1
mysql> SELECT * FROM (SELECT * FROM t1) t2(a, b);
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

• If a query statement does not contain the FROM clause, GaussDB supports the WHERE clause, which is the same as that in MySQL 8.0. MySQL 5.7 does not support the WHERE clause.

-- GaussDB
m_db=# SELECT 1 WHERE true;
 1 
---
 1
(1 row)

-- MySQL5.7
mysql> SELECT 1 WHERE true;
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 'where true' at line 1

-- MySQL8.0
mysql> SELECT 1 WHERE true;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)