Help Center> GaussDB> Service Overview> Compatibility with MySQL Databases> Distributed Instances> MySQL Compatibility in MySQL-Compatible Mode> SQL> DML
Updated on 2024-05-14 GMT+08:00
View PDF

DML

No.

Introduction

Syntax Description

Difference

1

DELETE supports ORDER BY and LIMIT.

DELETE

-

2

UPDATE supports ORDER BY and LIMIT.

UPDATE

-

3

The syntax is compatible with REPLACE INTO.

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) DISTRIBUTE BY HASH(f1);
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 type when NOT NULL exists. 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 int, f2 BIT(3) NOT NULL) DISTRIBUTE BY HASH(f1);
CREATE TABLE
gaussdb=# REPLACE INTO test VALUES(1, f2);
ERROR:  null value in column "f2" violates not-null constraint
DETAIL:  Failing row contains (1, null).

4

SELECT supports multi-partition query.

SELECT

-

5

UPDATE supports multi-partition update.

UPDATE

-

6

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 the number of columns 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 line breaks 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 line break 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.
  • Columns can only be specified by column name instead of user variables.
  • The LOAD DATA SET expression does not support the calculation of a specified column name.
  • If no implicit conversion function exists between the return value type of the SET expression and the corresponding column type, an error is reported.
  • LOAD DATA does not support the INSERT or DELETE trigger.
  • 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 of the file to be imported.

7

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