sql - 在 MySQL 中，如何编写 INSERT-INTO-VALUES 查询，以便在具有相同主键值的记录已存在时将其静默丢弃？

Question

我必须导入大量可能包含重复项的记录。该表是一个具有复合主键且无外键的 MyISAM 表。如何指定如果要插入的记录的主键值组合已存在于表中，它将仅丢弃特定的插入而不抛出错误或插入重复项？

Answer 1

您可能需要在 INSERT 中使用 IGNORE 关键字:

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued. (Source)

示例:

CREATE TABLE my_table (
   id int, name varchar(10), value int, PRIMARY KEY (id, name)
);
Query OK, 0 rows affected (0.03 sec)

INSERT IGNORE INTO my_table (id, name, value) VALUES (1, 'a', 100),
                                                     (1, 'b', 200),
                                                     (1, 'b', 300);
Query OK, 2 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

SELECT * FROM my_table;
+----+------+-------+
| id | name | value |
+----+------+-------+
|  1 | a    |   100 |
|  1 | b    |   200 |
+----+------+-------+
2 rows in set (0.00 sec)