MySQL eliminates repeated rows.

MySQL eliminates repeated rows.

SQL statement

/* Some methods to remove duplicate rows in MySQL --- Chu Minfei --- 22:49:44. 660 -- reference reprinted please indicate the source: Using ---------------------- -- 1 Use table replacement to delete duplicate items 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 table tmp like test_1, a temporary table with the same null structure as the source table; -- insert non-repeated 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; -- display mysql> select * from test_1; + ------ + ------- + | id | value | + ------ + ------- + | 1 | 2 | 2 | 3 | + ------ + ------- + -- 2. 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. create table test_2 (id int, va Lue 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. you can also use the Unique constraint here because the column may have a NULL value, but here there are multiple NULL values .. -- 2. joint table deletion create table test_2 (id int, value int); insert test_2 select 1, 2 union all select 1, 3 union all selec T 2, 3; delete A from test_2 a join (select MAX (value) as v, ID from test_2 group by id) B on. id = B. id and. value <> B. v; select * from test_2; + ------ + ------- + | id | value | + ------ + ------- + | 1 | 3 | 2 | 3 | + ------ + ------- + -- 3. using Increment_auto can also be the second method for removing duplicates from all the above fields -- 4. an easy-to-error method-some friends may think of the subquery method. Let's test 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. id = id and. value <value);/* ERROR 1093 (HY000): You can't specify target table 'A' for update in FROM clause */Currently, You cannot delete a table, at the same time, select from the same table in the subquery. ------------------ Delete a specific duplicate row ------------ -- mainly through order by + limit or directly 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 the record with the smallest ID = 1 value to be retained, delete 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 record and keep one record order by can be removed.