How do I get rid of duplicate data in a datasheet

In general, we are doing a product, at the beginning may be due to poor design or program writing is not rigorous, a field on the value of duplication, but must be removed, this time a little bit of trouble, directly add a unique key is certainly not, because it will report an error.

Now, we're going to use a workaround, but we might lose some data:)

Here, we set up a table whose structure is as follows:

mysql> desc `user`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type　　　　　　 | Null | Key | Default | Extra　　　　　|
+-------+------------------+------+-----+---------+----------------+
| id　　| int(10) unsigned | NO　 | PRI | NULL　　| auto_increment |
| name　| char(10)　　　　 | NO　 |　　 |　　　　 |　　　　　　　　|
| extra | char(10)　　　　 | NO　 |　　 |　　　　 |　　　　　　　　|
+-------+------------------+------+-----+---------+----------------+

The data in the original table is assumed to have the following:

mysql> SELECT * FROM `user`;
+----+-------+--------+
| id | name　| extra　|
+----+-------+--------+
|　1 | user1 | user1　|
|　2 | user2 | user2　|
|　3 | user3 | user3　|
|　4 | user4 | user4　|
|　5 | user5 | user5　|
|　6 | user3 | user6　|
|　7 | user6 | user7　|
|　8 | user2 | user8　|
|　9 | USER2 | user9　|
| 10 | USER6 | user10 |
+----+-------+--------+

1, the original data export

mysql>SELECT * INTO OUTFILE '/tmp/user.txt' FROM `user`;

2, empty the data table

mysql>TRUNCATE TABLE `user`;

3, create a unique index, and modify the ' name ' field type is BINARY CHAR case-sensitive

mysql> ALTER TABLE `user` MODIFY `name` CHAR(10) BINARY NOT NULL DEFAULT '';
mysql> ALTER TABLE `user` ADD UNIQUE KEY ( `name` );

Now let's look at the new table structure:

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type　　　　　　 | Null | Key | Default | Extra　　　　　|
+-------+------------------+------+-----+---------+----------------+
| id　　| int(10) unsigned | NO　 | PRI | NULL　　| auto_increment |
| name　| char(10)　　　　 | NO　 | UNI |　　　　 |　　　　　　　　|
| extra | char(10)　　　　 | NO　 |　　 |　　　　 |　　　　　　　　|
+-------+------------------+------+-----+---------+----------------+

4, the data guide back, here, there are two options: New duplicate records replace old records, only keep the latest records or new records skipped, only keep the oldest records

mysql> LOAD DATA INFILE '/tmp/user.txt' REPLACE INTO TABLE `user`;
Query OK, 10 rows affected (0.00 sec)
Records: 8　Deleted: 2　Skipped: 0　Warnings: 0
mysql> SELECT * FROM USER;
+----+-------+--------+
| id | name　| extra　|
+----+-------+--------+
|　1 | user1 | user1　|
|　8 | user2 | user8　|
|　6 | user3 | user6　|
|　4 | user4 | user4　|
|　5 | user5 | user5　|
|　7 | user6 | user7　|
|　9 | USER2 | user9　|
| 10 | USER6 | user10 |
+----+-------+--------+

The above is replaced by the way, you can see that the import process to delete two data, the results verify that the new duplicate records replace the old records, only keep the latest records.

Now, take a look at the Ignore way:

mysql> LOAD DATA INFILE '/tmp/user.txt' IGNORE INTO TABLE `user`;
Query OK, 6 rows affected (0.01 sec)
Records: 8　Deleted: 0　Skipped: 2　Warnings: 0
mysql> SELECT * FROM USER;
+----+-------+--------+
| id | name　| extra　|
+----+-------+--------+
|　1 | user1 | user1　|
|　2 | user2 | user2　|
|　3 | user3 | user3　|
|　4 | user4 | user4　|
|　5 | user5 | user5　|
|　7 | user6 | user7　|
|　9 | USER2 | user9　|
| 10 | USER6 | user10 |
+----+-------+--------+

See, it's true. The new record skips, leaving only the oldest records.