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 |
- |
5 |
UPDATE supports ORDER BY and LIMIT. |
UPDATE |
- |
6 |
The syntax is compatible with SELECT INTO. |
SELECT |
|
7 |
The syntax is compatible with REPLACE INTO. |
REPLACE |
|
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 non-null constraint, an error is reported. If no non-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 |
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. |
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. |
27 |
IGNORE feature |
INSERT |
The execution process in MySQL is different from that in GaussDB. Therefore, the number and information of generated warnings may be different. |
28 |
HAVING syntax |
SELECT |
In GaussDB, HAVING can only reference columns in the GROUP BY clause or columns used by aggregate functions. However, MySQL supports more: it allows HAVING to reference SELECT columns in the list and columns in external subqueries. |
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