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

This article will explain in detail how to eliminate duplicate lines in MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Sql statement

/ * some methods for MySQL to eliminate duplicates-Chu Minfei---2010-08-12 2222 Chu Minfei---2010 49create table test_1 44.660-reference reproduced Please indicate source: duplicate all fields in http://blog.csdn.NET/feixianxxx*/---------------- 1 use table substitution to delete duplicates (id int,value int) Insert test_1 select 1 union all select 1 union all select 2 union all select 3;-- create an empty temporary table create table tmp like test_1; with the same structure as the source table-- insert non-duplicate records insert tmp select distinct * from test_1; into the temporary table-- delete the original table drop table test_1;-- change the temporary table name to target table rename table tmp to test_1;-- display mysql > select * table +-+-+ | 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 union all select 1 union all select 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 +-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 | +-+-- some fields are repeated-1. Indexing method create table test_2 (id int,value int); insert test_2 select 1 union all select 2 union all select 1 union all select 2 Magi 3; Alter IGNORE table test_2 add primary key (id); select * from test_2 +-+-+ | id | value | +-+-+ | 1 | 2 | 2 | 3 | +-+-+ We can see that 1 / 3 this record has disappeared, and we can also use the Unique constraint here because it is possible to have null values in the column, but here we can have multiple NULL.. -- 2. Joint table deletion create table test_2 (id int,value int); insert test_2 select 1 union all select 2 union all select 1 union all select 2 as 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.valueb.v; select * from test_2 +-+-+ | id | value | +-+-+ | 1 | 3 | | 2 | 3 | +-3. Using Increment_auto can also be the second way to remove the duplicates of all the fields above-- 4. Error-prone method-some friends may think of the method of sub-query, let's try create table test_2 (id int,value int); insert test_2 select 1 union all select 2 union all select 3 union all select 2 from test_2 where a.id=id and a.value 3; delete a from test_2 a where exists (select * query)

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.