Tables or result sets sometimes contain duplicate records. Sometimes, it is allowed but sometimes it is required to stop duplicate records. Sometimes, it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.
You are allow use PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Let’s create an example table with id and name as Primary Key:
CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `value` int(11) NOT NULL, PRIMARY KEY (`id`, `name`), );
The presence of Primary Key or Unique Index in a table normally causes an error to occur if you insert a record was duplicates an existing record in the column or columns that define the index.
Use INSERT IGNORE rather than INSERT
If a record doesn’t duplicate an existing record, it will insert as usual. If the record is duplicate, the IGNORE keyword tells MySQL to discard it silently without generating any error.
The INSERT IGNORE example:
INSERT IGNORE INTO `foo` ( `id` , `name`, `value`)
VALUES ('1', 'Jacky', 1001)
Or, you can use REPLACE function. If the record is new, it’s insert just as with INSERT. If it’s a duplicate, the new record will replaces the old one:
REPLACE INTO `foo` ( `id` , `name`, `value`)
VALUES ('1', 'Jacky', 2051)
use either INSERT IGNORE or REPLACE should be chosen according to the duplicate-handling behavior you want to effect. INSERT IGNORE keeps the first of a set of duplicated records and discards the rest. REPLACE keeps the last of a set of duplicates and erase out any earlier ones.