Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.