Mysql删除重复数据-白红宇

Mysql删除重复数据

阅读量：5861 次

发布时间：2019-06-19

本文共 3593 字，大约阅读时间需要 11 分钟。

一.简介

假设表结构：

CREATE TABLE `user` (

`user_id` int(11) NOT NULL COMMENT '用户Id',

`brand_id` int(11) NOT NULL COMMENT '品牌Id',

`cellphone` varchar(20) DEFAULT NULL COMMENT '手机号，指定品牌下的网友唯一标识',

`update_date` datetime DEFAULT NULL COMMENT '更新时间',

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk COMMENT='用户信息表'

在该表的n条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复并保留一条呢？方法有以下两种：

1、通过中间表实现；2、在表内用sql实现；【以上两种方法，个人推荐第二种，本博客会详细分析第二种】

二.通过中间表实现

1）使用 create table like 复制出来一个中间表，然后用insert into select 把不重复的表导入到中间表中，然后再用中间表替代旧表。具体实现如下：

create table tmp_user like user;

Insert into tmp_user (user_id, brand_id,cellphone,update_date) select min(`user_id`),brand_id,cellphone,update_date from user group by cellphone ;

drop table users ;

alter table tmp_user rename user;

2）使用 create table select 直接复制出来一个含有数据的中间表然后用中间表替代旧表。具体实现如下：

create table tmp_user(user_id, brand_id,cellphone,update_date) select min(`user_id`),brand_id,cellphone,update_date from user group by cellphone ;

truncate table user;

insert into user select * from tmp_user;

drop table tmp_user;

以上两种方法的区别就是 create table like 和 create table select 的区别。create table like 复制的表结构包含索引而 create table select 不包含索引，没有索引对业务影响很大，这个要特别留意的。还有就是create table like 和 create table select 复制的表没有把表的权限给copy过来。要事后从新设置下。数据量大的时候应该选择 create table select ，先倾倒数据事后再为表建立索引。

至于用中间表的数据更新旧表的策略，要么用drop旧表再rename中间表。要么清空旧表数据再导入中间表数据。数据量大的时候前面方法效率较高。

三.在表内用sql实现

1）找到要删除的数据然后删除这些数据。

具体实现如下，

delete user as a from user as a,(

select min(user_id) as id_temp , cellphone from user group by cellphone having count(cellphone) > 1

) as b

where a.cellphone = b.cellphone and a.user_id <> b.id_temp;

加上 having count(name) > 1 可以避免扫描没有重复的记录，提高效率

2）找到要保留的数据然后用not in 来删除不再这些数据中的记录。

大家很容易就想到如下的sql语句：

delete from user where user_id not in ( select min(user_id) from user group by cellphone ); 但是mysql删除动作不能带有本表的查询动作，意思是你删除user表的东西不能以user表的信息为条件，所以这个语句会报错，执行不了。只要通过创建临时表作为查询条件。

具体实现如下：

delete from user where user_id not in ( select * from ( select min(user_id) from user group by cellphone ) as temp );

ps: count(x)会排除字段x为空的情况，加入cellphone中存在null的值，则使用group by having的时候不要用count(cellphone)，而是使用count( user_id )