DML

Updated on 2024-12-06 GMT+08:00

View PDF

No.	MySQL Function	Syntax Description	GaussDB Implementation 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	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.
7	Support the REPLACE INTO syntax.	REPLACE	Difference between the initial values of the time type. For example: MySQL is not affected by the strict or loose mode. You can insert time 0 into a table. mysql> CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL); Query OK, 1 row affected (0.00 sec) mysql> REPLACE INTO test VALUES(f1, f2, f3); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test; +---------------------+---------------------+------------+ \| f1 \| f2 \| f3 \| +---------------------+---------------------+------------+ \| 0000-00-00 00:00:00 \| 0000-00-00 00:00:00 \| 0000-00-00 \| +---------------------+---------------------+------------+ 1 row in set (0.00 sec) The time 0 can be successfully inserted only when GaussDB is in loose mode. gaussdb=# SET b_format_version = '5.7'; SET gaussdb=# SET b_format_dev_version = 's1'; SET gaussdb=# SET sql_mode = ''; SET gaussdb=# CREATE TABLE test(f1 TIMESTAMP NOT NULL, f2 DATETIME NOT NULL, f3 DATE NOT NULL); CREATE TABLE gaussdb=# REPLACE INTO test VALUES(f1, f2, f3); REPLACE 0 1 gaussdb=# SELECT * FROM test; f1 \| f2 \| f3 ---------------------+---------------------+------------ 0000-00-00 00:00:00 \| 0000-00-00 00:00:00 \| 0000-00-00 (1 row) In strict mode, the error is reported: date/time field value out of range: "0000-00-00 00:00:00". Difference between the initial values of the bit string type. For example: The initial value of the BIT type is an empty string '' in MySQL, that is: mysql> CREATE TABLE test(f1 BIT(3) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> REPLACE INTO test VALUES(f1); Query OK, 1 row affected (0.00 sec) mysql> SELECT f1, f1 IS NULL FROM test; +----+------------+ \| f1 \| f1 is null \| +----+------------+ \| \| 0 \| \| \| 0 \| +----+------------+ 2 rows in set (0.00 sec) If the initial value of the BIT type is NULL in GaussDB, an error is reported. gaussdb=# CREATE TABLE test(f1 BIT(3) NOT NULL); CREATE TABLE gaussdb=# REPLACE INTO test VALUES(f1); ERROR: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null).
8	SELECT supports multi-partition query.	SELECT	-
9	UPDATE supports multi-partition update.	UPDATE	-
10	Import data by using LOAD DATA.	LOAD DATA	The execution result of the LOAD DATA syntax is the same as that in MySQL 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. If single quotation marks are specified as separators, escape characters, and newline characters in the syntax, lexical parsing errors occur. 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. If there is no implicit conversion function between the return value type of the SET expression and the corresponding column type, an error is reported. 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.
11	Compatible with INSERT IGNORE.	INSERT IGNORE	GaussDB displays the error information after the downgrade. MySQL records the error information after the downgrade to the error stack and runs the show warnings; command to view the error information. Time type difference. For example: The default values of date, datetime, and timestamp in GaussDB are 0. gaussdb=# CREATE TABLE test(f1 DATE NOT NULL, f2 DATETIME NOT NULL, f3 TIMESTAMP NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL, NULL, NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). WARNING: null value in column "f3" violates not-null constraint DETAIL: Failing row contains (null, null, null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 \| f2 \| f3 ------------+---------------------+--------------------- 1970-01-01 \| 1970-01-01 00:00:00 \| 1970-01-01 00:00:00 (1 row) The default values of date, datetime, and timestamp in MySQL are 0. mysql> CREATE TABLE test(f1 DATE NOT NULL, f2 DATETIME NOT NULL, f3 TIMESTAMP NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL, NULL, NULL); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+----------------------------+ \| Level \| Code \| Message \| +---------+------+----------------------------+ \| Warning \| 1048 \| Column 'f1' cannot be null \| \| Warning \| 1048 \| Column 'f2' cannot be null \| \| Warning \| 1048 \| Column 'f3' cannot be null \| +---------+------+----------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM test; +------------+---------------------+---------------------+ \| f1 \| f2 \| f3 \| +------------+---------------------+---------------------+ \| 0000-00-00 \| 0000-00-00 00:00:00 \| 0000-00-00 00:00:00 \| +------------+---------------------+---------------------+ 1 row in set (0.00 sec) GaussDB does not support the MySQL bit type. Therefore, the INSERT IGNORE error downgrade is not supported when the NOT NULL constraint of the bit type is ignored and the length of the inserted bit type is different from that defined. Bit type in GaussDB gaussdb=# CREATE TABLE test(f1 BIT(10) NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL); ERROR: Un-support feature DETAIL: ignore null for insert statement is not supported in column f1. gaussdb=# INSERT IGNORE INTO test VALUES('1010'); ERROR: bit string length 4 does not match type bit(10) CONTEXT: referenced column: f1 Bit type in MySQL mysql> CREATE TABLE test(f1 BIT(10) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> INSERT IGNORE INTO test VALUES('1010'); Query OK, 1 row affected, 1 warning (0.01 sec) If the precision is specified for the time type in MySQL, the precision is displayed when the zero value is inserted. It is not displayed in GaussDB. For example: Time precision specified in GaussDB gaussdb=# CREATE TABLE test(f1 TIME(3) NOT NULL, f2 DATETIME(3) NOT NULL, f3 TIMESTAMP(3) NOT NULL); CREATE TABLE gaussdb=# INSERT IGNORE INTO test VALUES(NULL,NULL,NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null, null). WARNING: null value in column "f3" violates not-null constraint DETAIL: Failing row contains (null, null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 \| f2 \| f3 ----------+---------------------+--------------------- 00:00:00 \| 1970-01-01 00:00:00 \| 1970-01-01 00:00:00 (1 row) Time precision specified in MySQL mysql> CREATE TABLE test(f1 TIME(3) NOT NULL, f2 DATETIME(3) NOT NULL, f3 TIMESTAMP(3) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test VALUES(NULL,NULL,NULL); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> SELECT * FROM test; +--------------+-------------------------+-------------------------+ \| f1 \| f2 \| f3 \| +--------------+-------------------------+-------------------------+ \| 00:00:00.000 \| 0000-00-00 00:00:00.000 \| 0000-00-00 00:00:00.000 \| +--------------+-------------------------+-------------------------+ 1 row in set (0.00 sec) The execution process in MySQL is different from that in GaussDB. Therefore, the number of generated warnings may be different. For example: Number of warnings generated in GaussDB gaussdb=# CREATE TABLE test(f1 INT, f2 INT not null); CREATE TABLE gaussdb=# INSERT INTO test VALUES(1,0),(3,0),(5,0); INSERT 0 3 gaussdb=# INSERT IGNORE INTO test SELECT f1+1, f1/f2 FROM test; WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (2, null). WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (4, null). WARNING: division by zero CONTEXT: referenced column: f2 WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (6, null). INSERT 0 3 Number of warnings generated in MySQL mysql> CREATE TABLE test(f1 INT, f2 INT not null); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test VALUES(1,0),(3,0),(5,0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT IGNORE INTO test SELECT f1+1, f1/f2 FROM test; Query OK, 3 rows affected, 4 warnings (0.00 sec) Records: 3 Duplicates: 0 Warnings: 4 The differences between MySQL's and GaussDB's INSERT IGNORE in triggers are as follows: INSERT IGNORE used in a GaussDB trigger gaussdb=# CREATE TABLE test1(f1 INT NOT NULL); CREATE TABLE gaussdb=# CREATE TABLE test2(f1 INT); CREATE TABLE gaussdb=# CREATE OR REPLACE FUNCTION trig_test() RETURNS TRIGGER AS $$ gaussdb$# BEGIN gaussdb$# INSERT IGNORE INTO test1 VALUES(NULL); gaussdb$# RETURN NEW; gaussdb$# END; gaussdb$# $$ LANGUAGE plpgsql; CREATE FUNCTION gaussdb=# CREATE TRIGGER trig2 BEFORE INSERT ON test2 FOR EACH ROW EXECUTE PROCEDURE trig_test(); CREATE TRIGGER gaussdb=# INSERT INTO test2 VALUES(NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null). CONTEXT: SQL statement "INSERT IGNORE INTO test1 VALUES(NULL)" PL/pgSQL function trig_test() line 3 at SQL statement INSERT 0 1 gaussdb=# SELECT * FROM test1; f1 ---- 0 (1 rows) gaussdb=# SELECT * FROM test2; f1 ---- (1 rows) INSERT IGNORE used in a MySQL trigger mysql> CREATE TABLE test1(f1 INT NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE test2(f1 INT); Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER \|\| mysql> CREATE TRIGGER trig2 BEFORE INSERT ON test2 FOR EACH ROW -> BEGIN -> INSERT IGNORE into test1 values(NULL); -> END\|\| Query OK, 0 rows affected (0.01 sec) mysql> DELIMITER ; mysql> INSERT INTO test2 VALUES(NULL); ERROR 1048 (23000): Column 'f1' cannot be null mysql> INSERT IGNORE INTO test2 VALUES(NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test1; +----+ \| f1 \| +----+ \| 0 \| +----+ 1 row in set (0.00 sec) mysql> SELECT * FROM test2; +------+ \| f1 \| +------+ \| NULL \| +------+ 1 row in set (0.00 sec) The implementation mechanism of Boolean and serial in GaussDB is different from that in MySQL. Therefore, the default zero value in GaussDB is different from that in MySQL. For example: Behavior in GaussDB gaussdb=# CREATE TABLE test(f1 SERIAL, f2 BOOL NOT NULL); NOTICE: CREATE TABLE will create implicit sequence "test_f1_seq" for serial column "test.f1" CREATE TABLE gaussdb=# INSERT IGNORE INTO test values(NULL,NULL); WARNING: null value in column "f1" violates not-null constraint DETAIL: Failing row contains (null, null). WARNING: null value in column "f2" violates not-null constraint DETAIL: Failing row contains (null, null). INSERT 0 1 gaussdb=# SELECT * FROM test; f1 \| f2 ----+---- 0 \| f (1 row) Behavior in MySQL mysql> CREATE TABLE test(f1 SERIAL, f2 BOOL NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql> INSERT IGNORE INTO test values(NULL,NULL); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> SELECT * FROM test; +----+----+ \| f1 \| f2 \| +----+----+ \| 1 \| 0 \| +----+----+ 1 row in set (0.00 sec)

Parent topic: SQL

Previous topic: DDL

Next topic: DCL

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot