需求：
假设有一个表 test ，包含如下数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
> select id, email from test; ID EMAIL ---------------------- -------------------- 1 aaa 2 bbb 3 ccc 4 bbb 5 ddd 6 eee 7 aaa 8 aaa 9 eee |
我们需要找到所有重复的email并删掉它们，同时只保留其中id最新的那条记录。这里我们以test，表，来说明，“aaa”,”bbb”和“eee”是重复的，所以我们需要删掉id为 ，1，7，2和6的记录。
创表语句：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
create table test ( id int primary key, email varchar(50) ); insert into test (id, email) values (1, 'aaa'), (2, 'bbb'), (3, 'ccc'), (4, 'bbb'), (5, 'ddd'), (6, 'eee'), (7, 'aaa'), (8, 'aaa'), (9, 'eee'); commit ; |
第一种方法
先用email分组（group by），再找到该分组下的最大的id（最新的id，我们叫做 lastId），然后用email字段进行inner join，把join结果中 id小于lastId的删掉即可。
第一步:查找重复的记录
要想删除重复的记录，就必须先找到重复的记录，如下：
|
1 2 3 4 5 6 7 8 9 10 |
> select email from test group by email having count(*) > 1; EMAIL -------------------- aaa bbb eee |
第二步：查找重复的记录，且找到包含最新id的记录
|
1 2 3 4 5 6 7 8 9 10 |
select max(id) as lastId,email from test group by email having count(*) > 1; LASTID EMAIL ---------------------- -------------------- 8 aaa 4 bbb 9 eee |
第三步：使用email字段来inner join ，删除重复的记录
|
1 2 3 4 5 |
delete test from test inner join (select max(id) as lastId, email from test group by email having count(*) > 1) duplic on duplic.email = test.email where test.id < duplic.lastId; |
删除后的结果：
|
1 2 3 4 5 6 7 8 9 10 |
select * from test; +----+-------+ | id | email | +----+-------+ | 3 | ccc | | 4 | bbb | | 5 | ddd | | 8 | aaa | | 9 | eee | +----+-------+ |
第二种方法
用email进行分组，找到每个分组的最大的id，然后把test中id 不等于该值的记录删掉，如下：
|
1 2 3 4 5 |
delete from test where id not in ( select max(id) from test group by email) |
第三种方法（使用临时表）：
直接使用disnct来查询包含最新id的记录并导入到一张临时表中，然后将临时表重命名为原来的表明（当然也可以导回到原表）。因为有时候可能原来已经有了索引，此时test表数据量很大，而重复的数据很多，delete的时候还要不断维护索引，可能导致delete效率很低，还不如直接新建一个表把最新的唯一数据插入到新表中，插入完成后再恢复索引。
如果只是需要删掉重复值的要求，如下：
|
1 2 3 4 5 6 7 8 9 |
create table tempTest ( id int primary key, email varchar(50) ); INSERT INTO tempTest (id, email) SELECT DISTINCT id, email FROM test; |
直接简化下：
|
1 2 3 |
create table tempTest as (select id, email from test group by email); |
如果是要求删掉重复记录且保留最新id的要求，如下
|
1 2 3 4 5 6 7 8 9 10 |
create table tempTest ( id int primary key, email varchar(50) ); INSERT INTO tempTest (id, email) SELECT id, email FROM test where id in (select max(id) from test group by email); |
第四种方法（不推荐）
使用self join，这种方法效率低，如果表数据很多，不推荐这种。
|
1 2 3 4 |
delete a from test a, test b where a.id < b.id and a.email = b.email; |
删除重复记录方法
除了上面的一些删除重复的记录的方法（不仅删除，而且保留最新的一条记录），还有余下的几种，其实和前面的都差不多。
使用临时表+insert ignore
先用distinct将test表中的唯一数据被分到tempTest表，然后清空test表，对emai字段建立唯一索引，然后使用insert ignore方法将tempTest表中的数据拷贝回test表 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# Step 1: Copy distinct values to temporary table CREATE TEMPORARY TABLE tempTest ( SELECT id, email FROM test GROUP BY email ); # Step 2: Remove all rows from original table DELETE FROM test; # Step 3: Add Unique constraint ALTER TABLE test ADD UNIQUE(email); # Step 4: Remove all rows from original table INSERT IGNORE INTO test (SELECT * FROM tempTest); # Step 5: Remove temporary table DROP TABLE tempTest; |
使用ROW_NUMBER()函数来删除重复记录（仅支持mysql 8.x）
与oracle中的ROW_NUMBER()类似，我们对email字段进行分组，然后使用ROW_NUMBER()函数，为每个分组的每条记录的增加一个数字来表示这条是该分组的第几条数据，如果有重复的数据，那么这个数字肯定是大于1的，如下：
|
1 2 3 4 5 6 7 |
DELETE FROM test WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER( PARTITION BY email ORDER BY email) AS row_num FROM test) t WHERE row_num > 1) |
这个也可以改成保留最新的记录，不过个人感觉没必要了。
感想：
公司从oracle切换到mysql，会发现很多函数和功能在mysql中都没有，这不得不承认oracle真的很强大，商用的东西要完善很多，贵有贵的道理。
参考：
转载请注明：大步's Blog » tip：删除表中的重复记录，并保留最新的一条记录
