In MySQL, how to write an INSERT-INTO-VALUES query so that it to be silently discarded if a record with same primary key value already exists?

I have to import a large set of records which can contain duplicates. The table is a MyISAM table with a composite primary key and no foreign keys. How do I specify that if a primary key values combination of a record being inserted already exists in the table, it will just discard the particular insert without throwing an error开发者_开发百科 or inserting a duplicate?

---

You may want to use the IGNORE keyword in your INSERTs:

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)

Example:

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)

---

You need to use the IGNORE keyword:

INSERT IGNORE INTO ...

or

LOAD DATA INFILE ... IGNORE ...

Details of how to use the latter can be found here.