Skip to content

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.