How to remove duplicate data from a data table

Generally, when we create a product, the values in a field may be duplicated at the beginning due to poor design consideration or program writing, but it must be removed. It is a little troublesome at this time.UNIQUE KEYIt must not work, because an error will be reported.
Now, we will adopt a work und, but some data may be lost :)

Here, we set a table with the following structure:

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   |     |         |                |+-------+------------------+------+-----+---------+----------------+

Assume that the data in the original table has the following items:

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. Export the original data

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

2. Clear Data Tables

mysql>TRUNCATE TABLE `user`;

3. Create a unique index and change the 'name' field typeBINARY CHARCase Sensitive

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

Now let's take a 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. Import the data back. There are two options:Replace the old record with the new record. Only the latest record is retained.OrThe new record is skipped and only the oldest record is retained

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: 0mysql> 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 usesREPLACEYou can see that two pieces of data are deleted during the import process, and the result is indeedReplace the old record with the new record. Only the latest record is retained..
Now, let's see how to useIGNOREMethod:

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: 0mysql> 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 |+----+-------+--------+

Yes.The new record is skipped and only the oldest record is retained.

 

This article is from the "programming path" blog, please be sure to keep this source http://huangby.blog.51cto.com/5199904/1293273