数据库根据指定字段去重

需求：对一张用户表根据name/email/card_num字段去除重复数据；

思路：用group by方法可以查询出'去重'后的数据，将这些数据存储到一张临时表中，然后将临时表的数据存储到指定的表中；

误区及解决方案：group by方法只能获取部分字段（去重指定字段），不能一次获取到完整的数据，但是可以通过max函数获取group by结果集中的id，再根据id集合查询出全部的记录。

测试思路

SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num;

SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T;

SELECT * from users where id in (SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T);

实际方法

create TEMP TABLE tmp_data as SELECT * from users where id in (SELECT ID from (SELECT max(id) as id,name,email,card_num FROM users GROUP BY name,email,card_num) as T);

insert into users_copy1 select * from tmp_data;

检测

select count(*) from users_copy1;

测试结果：1.4w条数据中有2300条数据重复，实际运行结果为0.7s，基本满足现在的需求。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！