INSERT IGNORE

Syntax Description

INSERT IGNORE is used to insert data into a database table with the same unique index or primary key, if the data already exists, instead of returning an error, otherwise inserting new data.

Unlike MySQL, when inserting duplicate values for unique indexes or primary keys, MatrixOne ignores the error, while MySQL has an alarm message.

Syntax Structure

INSERT IGNORE INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...;

Example

CREATE TABLE user (
    id INT(11) NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT(3) NOT NULL
);
-- Insert a new data, the id does not exist, so enter the new data
mysql> INSERT IGNORE INTO user VALUES (1, 'Tom', 18);
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM USER;
+------+------+
| id | name | age |
+------+------+
| 1 | Tom | 18 |
+------+------+
1 row in set (0.01 sec)

-- Insert a new data, the id exists, so the data is ignored
mysql> INSERT IGNORE INTO user VALUES (1, 'Jane', 16);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM USER;
+------+------+
| id | name | age |
+------+------+
| 1 | Tom | 18 |
+------+------+
1 row in set (0.01 sec)

limit

  • INSERT IGNORE does not support writing NULL to the NOT NULL column.

  • INSERT IGNORE does not support conversion of error data types.

  • INSERT IGNORE does not support handling operations where data inserted in partition tables contain mismatched partition values.

Last updated on