Some ways to eliminate duplicate rows in MySQL

/*
Some methods of eliminating duplicate rows in MySQL ---Chu Minfei
---2010-08-12 22:49:44.660
--Please indicate the source when quoting or reprinting: http://blog.csdn.NET/feixianxxx
*/
----------------All fields are repeated------------------------
 --1 Use table replacement to remove duplicates create table test_1(id int,value int);
 insert test_1 select 1,2 union all select 1,2 union all select 2,3;
 --Create an empty temporary table with the same structure as the source table create table tmp like test_1;
 --Insert unique records into the temporary table insert tmp select distinct * from test_1;
 --Delete the original table drop table test_1;
 --Change the temporary table name to the target table rename table tmp to test_1;
 --showmysql> select * from test_1;
+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
| 2 | 3 |
+------+-------+
 --2. Add auto_increment attribute column (this method can only be used for MyISAM or BDB engine tables)
 create table test_1(id int,value int) engine=MyISAM;
 insert test_1 select 1,2 union all select 1,2 union all select 2,3;
 alter table test_1 add id2 int not null auto_increment,
 add primary key(id,value,id2);
 select * from test_1;
+----+-------+-----+
| id | value | id2 |
+----+-------+-----+
| 1 | 2 | 1 |
| 1 | 2 | 2 |
| 2 | 3 | 1 |
+----+-------+-----+
  delete from test_1 where id2<>1;
  alter table test_1 drop id2;
  select * from test_1;
  +----+-------+
| id | value |
+----+-------+
| 1 | 2 |
| 2 | 3 |
+----+-------+
-------------------Some fields are repeated---------------------
--1. Add index by create table test_2 (id int, value int);
 insert test_2 select 1,2 union all select 1,3 union all select 2,3;
 Alter IGNORE table test_2 add primary key(id);
 select * from test_2;
 +----+-------+
| id | value |
+----+-------+
| 1 | 2 |
| 2 | 3 |
+----+-------+
 We can see that the record 1 3 has disappeared. We can also use the Unique constraint here because there may be NULL values ​​in the column, but here NULL can be multiple.
 --2.Join table deletion create table test_2(id int,value int);
 insert test_2 select 1,2 union all select 1,3 union all select 2,3;
 delete A from test_2 a join (select MAX(value) as v ,ID from test_2 group by id) b
 on a.id=b.id and a.value<>bv;
 select * from test_2;
 +------+-------+
| id | value |
+------+-------+
| 1 | 3 |
| 2 | 3 |
+------+-------+
--3. Using Increment_auto can also be the second method to remove duplicates from all fields above --4. Methods that are prone to errors --Some friends may think of the subquery method. Let's try it out. create table test_2 (id int, value int);
 insert test_2 select 1,2 union all select 1,3 union all select 2,3;
 delete a from test_2 a where exists(select * from test_2 where a.id=id and a.value<value);
 /*ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause*/
 
 Currently, you cannot delete from a table and select from the same table in a subquery.
 
 
 ------------------Delete specific duplicate rows-------------
 --Mainly through order by +limit or direct limit 
 create table test_3(id int,value int);
 insert test_3 select 1,2 union all select 1,3 union all select 1,4 union all select 2,3;
 --This is to keep the record with the smallest ID=1 value and delete the records with other ids delete from test_3 where id=1 order by value desc limit 2;
 select * from test_3;
+------+-------+
| id | value |
+------+-------+
| 1 | 2 |
| 2 | 3 |
+------+-------+
 If you only want to delete any records and keep one, you can remove the order by