Handling Duplicates - MySQL - page 552

CREATE TABLE person
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
last_name CHAR ( 20 ),
first_name CHAR ( 20 ),
address CHAR ( 40 ),
PRIMARY KEY ( id )
);
In this case, when you create a row with an id value of NULL , MySQL assigns that column
a unique ID automatically. Another possibility is to assign identifiers externally and use
those IDs as unique keys. For example, citizens in a given country might have unique
taxpayer ID numbers. If so, those numbers can serve as the basis for a unique index:
CREATE TABLE person
(
tax_id INT UNSIGNED NOT NULL ,
last_name CHAR ( 20 ),
first_name CHAR ( 20 ),
address CHAR ( 40 ),
PRIMARY KEY ( tax_id )
);
See Also
If an existing table already contains duplicate rows that you want to remove, see
Recipe 16.4 . Chapter 13 further discusses AUTO_INCREMENT columns.
16.2. Dealing with Duplicates When Loading Rows into a
Table
Problem
You've created a table with a unique index to prevent duplicate values in the indexed
column or columns. But this results in an error if you attempt to insert a duplicate row,
and you want to avoid having to deal with such errors.
Solution
One approach is to just ignore the error. Another is to use an INSERT IGNORE , REPLACE ,
or INSERT … ON DUPLICATE KEY UPDATE statement, each of which modifies MySQL's
duplicate-handling behavior. For bulk-loading operations, LOAD DATA has modifiers that
enable you to specify how to handle duplicates.