How MySQL eliminates duplicate lines

This article is about how MySQL eliminates duplicate lines. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

SQL statement

/* MySQL some ways to eliminate duplicate rows---Chu Minfei---2010-08 - 12 22:49: 44.660--Quotation Please indicate the source: http://blog.csdn.NET/feixianxxx<$/--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(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 select distinct * from test_1;--drop table test_1;--change temporary table name to target table rename table tmp to test_1;--display mysql> 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 id21; alter table test_1 drop id2; select * from test_1; +----+-------+| id | value |+----+-------+| 1 | 2 || 2 | 3| +---+-----+----------------------(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 disappears. We can also use the Unique constraint here because it is possible to have NULL values in the column, but here NULL can be multiple.. - -2. join table delete 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 www.example.com = www.example.com and a. valueb. v; select * from test_2;+----+----+ a.id value b.id 1||||| 3 || 2 | 3| +------+-------+--3. Use Increment_auto, which is the second method to duplicate all the above fields--4. The error-prone method--some friends may think of a subquery method. Let's try 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 www.example.com =id and a.value