DML
|
No. |
Description |
Syntax |
Difference |
|---|---|---|---|
|
1 |
DELETE supports deleting data from multiple tables. |
DELETE |
|
|
2 |
DELETE supports ORDER BY and LIMIT. |
DELETE |
- |
|
3 |
DELETE supports deleting data from a specified partition (or subpartition). |
DELETE |
- |
|
4 |
UPDATE supports updating data from multiple tables. |
UPDATE |
During multi-table update, if a tuple to be updated is concurrently modified by other sessions, the latest values of all tuples in the session are used for matching again. If the conditions are still met, the tuple is updated. During this process, MySQL updates all target tables consistently. However, GaussDB only rematches tuples of target tables that involve concurrent updates, which may cause data inconsistency. |
|
5 |
UPDATE supports ORDER BY and LIMIT. |
UPDATE |
- |
|
6 |
Support the SELECT INTO syntax. |
SELECT |
|
|
7 |
Support the REPLACE INTO syntax. |
REPLACE |
Difference between the initial values of the time type. For example:
|
|
8 |
SELECT supports multi-partition query. |
SELECT |
- |
|
9 |
UPDATE supports multi-partition update. |
UPDATE |
- |
|
10 |
Import data by using LOAD DATA. |
LOAD DATA |
When LOAD DATA is used to import data, GaussDB differs from MySQL in the following aspects:
|
|
11 |
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. |
|
12 |
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 a DELETE statement, GaussDB does not allow limit to be 0, while MySQL allows limit to be 0. |
|
13 |
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. |
|
14 |
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. |
|
15 |
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. |
|
16 |
Do not use ON DUPLICATE KEY UPDATE to modify constraint columns. |
INSERT |
- |
|
17 |
Duplicate column names are allowed in the SELECT result. |
SELECT |
- |
|
18 |
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. |
|
19 |
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. |
|
20 |
Compatibility in terms of nature join and using |
SELECT |
|
|
21 |
The WITH clause is compatible with MySQL 8.0. |
SELECT, INSERT, UPDATE, and DELETE |
- |
|
22 |
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. |
|
23 |
Displaying column names in the SELECT statement |
SELECT |
|
|
24 |
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. |
|
25 |
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. |
|
26 |
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. After the GUC compatibility parameter m_format_dev_version is set to 's2', the behavior can be the same as that in MySQL only in the single-table scenario. That is, the same column can be set for multiple times and the updated result is referenced. |
|
27 |
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. |
|
28 |
SHOW COLUMNS syntax |
SHOW |
|
|
29 |
SHOW CREATE DATABASE syntax |
SHOW |
User permission verification is different from that of MySQL.
|
|
30 |
SHOW CREATE TABLE syntax |
SHOW |
|
|
31 |
SHOW CREATE VIEW syntax |
SHOW |
|
|
32 |
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.
|
|
33 |
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. |
|
34 |
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. |
|
35 |
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. |
|
36 |
SHOW INDEX |
SHOW |
|
|
37 |
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. In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.session_variables view. |
|
38 |
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. In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.global_variables view. |
|
39 |
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. In GaussDB, when LIKE and WHERE are used to select fields in the query result, the sorting rule is the same as that of the corresponding fields in the information_schema.character_sets view. |
|
40 |
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. In GaussDB, when LIKE and WHERE are used to select fields in the query result in GaussDB, the sorting rule is the same as that of the corresponding fields in the information_schema.collations view. |
|
41 |
EXCEPT Syntax |
SELECT |
- |
|
42 |
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. |
|
43 |
SHOW TABLES |
SHOW |
|
|
44 |
SHOW TABLE STATUS |
SHOW |
|
|
45 |
WITH ROLLUP is supported after GROUP BY. |
SELECT |
WITH ROLLUP and ORDER BY can be used together in GaussDB, but cannot in MySQL. |
|
46 |
The ONLY_FULL_GROUP_BY option in SQL mode is supported. |
SELECT |
If the non-aggregate function column in the SELECT list is inconsistent with the GROUP BY field, when all non-aggregate function columns are in the GROUP BY list or WHERE list and the column in the WHERE clause is equal to a constant, no error is reported. For the column in the WHERE clause, GaussDB supports function column expressions whose input parameter is 1, but MySQL does not support function column expressions. In GaussDB, the field following GROUP BY must be a positive integer. |
|
47 |
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. |
|
48 |
Using SELECT to query system parameters and user variables |
SELECT @variable, SELECT @@variable |
|
|
49 |
Subqueries |
SELECT |
|
|
50 |
SHOW DATABASES |
SHOW |
In GaussDB, fields in the query result use the character set utf8mb4 and collation utf8mb4_bin. |
|
51 |
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) |
|
52 |
SELECT view query, subquery, or UNION involves the carry difference when NUMERIC is converted to TIME or DATETIME. |
SELECT |
In some SELECT scenarios, the results of the TIME/DATETIME type are different from those of MySQL. Difference scenarios involving conversion from NUMERIC to TIME/DATETIME: view query, subquery, and UNION. Differential behavior: The SELECT behavior of GaussDB is unified. When the NUMERIC type is converted to the TIME or DATETIME type, only the maximum precision bit(6) is carried. In MySQL view query, subquery, and UNION scenarios, carry is performed based on the actual precision of a result. Behavior in MySQL: -- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum. Therefore, 11:11:00.00002 is output. mysql> SELECT maketime(11, 11, 2.2/time '08:30:23.01'); +------------------------------------------+ | maketime(11, 11, 2.2/time '08:30:23.01') | +------------------------------------------+ | 11:11:00.00002 | +------------------------------------------+ 1 row in set (0.01 sec) -- In a subquery, carry is performed based on the actual result precision. Therefore, 11:11:00.00003 is output. mysql> SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1; +------------------------------------------+ | maketime(11, 11, 2.2/time '08:30:23.01') | +------------------------------------------+ | 11:11:00.00003 | +------------------------------------------+ 1 row in set (0.00 sec) Behavior in GaussDB: m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
SET
-- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum. Therefore, 11:11:00.00002 is output.
m_db=# SELECT maketime(11, 11, 2.2/time '08:30:23.01');
maketime
----------------
11:11:00.00002
(1 row)
-- In a simple query, carry is performed only on the result with the precision of 6, which is the maximum, and the result precision is 5. Therefore, 11:11:00.00002 is output.
m_db=# SELECT * FROM (SELECT maketime(11, 11, 2.2/time '08:30:23.01')) f1;
maketime
----------------
11:11:00.00002
(1 row) |
|
53 |
Differences of SELECT in calculating and processing date and time functions of the numeric type and subquery |
SELECT |
When date and time functions of the numeric type and subquery are calculated using SELECT, if the GUC parameter m_format_behavior_compat_options is set to enable_precision_decimal, GaussDB converts the value of the date and time type returned by the function to the one of the numeric type and then performs calculation based on the numeric type. The result is also of the numeric type. MySQL truncates the values returned by the date and time functions in scenarios such as subquery condition query and group query. Behavior in MySQL: mysql> select 1.5688 * (select ADDDATE('2020-10-20', interval 1 day) where true group by 1 having true);
+--------------------------------------------------------------------------------+
| 1.5688 * (select ADDDATE('2020-10-20', interval 1 day) where true having true) |
+--------------------------------------------------------------------------------+
| 3168.976 |
Behavior in GaussDB: m_db=# select 1.5688 * (select ADDDATE('2020-10-20', interval 1 day) where true group by 1 having true);
?column?
--------------------
31691361.744799998
(1 row) |
|
54 |
Differences in unsigned types when SELECT nests subqueries |
SELECT |
When SELECT nests subqueries, the unsigned type is not overwritten, which is different from MySQL 5.7. Behavior in MySQL 5.7: mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1 (
-> c10 real(10, 4) zerofill
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(123.45);
Query OK, 1 row affected (0.00 sec)
mysql> desc t1;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| c10 | double(10,4) unsigned zerofill | YES | | NULL | |
+-------+--------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> create table t1_sub_1 as select (select * from t1);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc t1_sub_1;
+--------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| (select * from t1) | double(10,4) | YES | | NULL | |
+--------------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
Behavior in GaussDB: test=# DROP TABLE IF EXISTS t1; DROP TABLE test=# CREATE TABLE t1 ( test(# c10 real(10, 4) ZEROFILL test(# ); CREATE TABLE test=# INSERT INTO t1 VALUES(123.45); INSERT 0 1 test=# DESC t1; Field | Type | Null | Key | Default | Extra -------+--------------------------------+------+-----+---------+------- c10 | double(10,4) unsigned zerofill | YES | | | (1 row) test=# CREATE TABLE t1_sub_1 AS SELECT (SELECT * FROM t1); INSERT 0 1 test=# DESC t1_sub_1; Field | Type | Null | Key | Default | Extra -------+-----------------------+------+-----+---------+------- c10 | double(10,4) unsigned | YES | | | (1 row) |
|
55 |
SELECT FOR SHRAE/FOR UPDATE/LOCK IN SHRAE MODE |
SELECT |
|
|
56 |
SELECT syntax |
SELECT |
-- 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)
-- 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) |
|
57 |
When statements such as UNION and GROUP BY that do not carry the ORDER BY clause are used to merge or aggregate data, the output data sequence may not be the same as that in MySQL because the executor operators are different. |
SELECT |
Take the GROUP BY scenario as an example. If the hashagg operator is used, the sequence is different from the original one. You are advised to add the ORDER BY clause in the scenario where the data sequence needs to be ensured. -- Initialize data. DROP TABLE IF EXISTS test; CREATE TABLE test(id INT); INSERT INTO test VALUES (1),(2),(3),(4),(5); -- GaussDB -- If precision transfer is disabled, the ID sequence is (1 3 2 4 5). m_db=# SET m_format_behavior_compat_options= ''; SET m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2; id | pi ----+------------------- 1 | 3.141592653589793 3 | 3.141592653589793 2 | 3.141592653589793 4 | 3.141592653589793 5 | 3.141592653589793 (5 rows) -- When the precision transfer function is enabled, the ID sequence changes to (5 4 2 3 1) due to the value change. m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal'; SET m_db=# SELECT /*+ use_hash_agg*/ id, pi() FROM test GROUP BY 1,2; id | pi ----+---------- 5 | 3.141593 4 | 3.141593 2 | 3.141593 3 | 3.141593 1 | 3.141593 (5 rows) -- In MySQL, the ID sequence is the original one. mysql> SELECT id, pi() FROM test GROUP BY 1,2; +------+----------+ | id | pi() | +------+----------+ | 1 | 3.141593 | | 2 | 3.141593 | | 3 | 3.141593 | | 4 | 3.141593 | | 5 | 3.141593 | +------+----------+ 5 rows in set (0.00 sec) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
