mysql删除重复数据失败_Mysql删除重复数据

一、准备测试表

CREATE TABLE `ts` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`name` VARCHAR(30) DEFAULT NULL,

`age` INT(11) DEFAULT NULL,

`sex` VARCHAR(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

二、准备测试数据

insert into ts(name,age,sex) values('张三',10,'男');

insert into ts(name,age,sex) values('张三',10,'男');

insert into ts(name,age,sex) values('张三',10,'女');

insert into ts(name,age,sex) values('李四',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('李四',20,'女');

insert into ts(name,age,sex) values('王五',20,'男');

insert into ts(name,age,sex) values('王五',20,'女');

INSERT INTO ts(NAME,age,sex) VALUES('王五',11,'女');

INSERT INTO ts(NAME,age,sex) VALUES('王五',10,'女');

INSERT INTO ts(NAME,age,sex) VALUES('郭靖',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('郭靖',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('欧阳锋',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('黄蓉',20,'女');

INSERT INTO ts(NAME,age,sex) VALUES('郭富城',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('黄药师',20,'男');

INSERT INTO ts(NAME,age,sex) VALUES('梅超风',20,'女');

select * from ts;

三、需求，删除重复数据，条件name和age一样，就视为重复数据，对于重复的数据只需要保留一条

方法一、通过delete的方法

DELETE FROM ts  WHERE id IN (SELECT id FROM (SELECT  MAX(id) AS id,COUNT(*) AS COUNT FROM ts GROUP BY NAME,age HAVING COUNT >1 ORDER BY COUNT DESC) AS tab );

备注：因为每次只删除重复数据的最大id,所以如果重复数据有多条的情况，需要执行多次delete

select * from ts;

方式二、通过查询的方法，先建好一张和原表一模一样的表，包括主键，约束，索引都一样的表，然后通过sql 把符合的结果查询出来导到这个新表中，最后rename table.

create table ts1 like ts;

-- 查询的结果为不重复数据，union 重复的数据取主键最小的一条

INSERT INTO ts1

select * from (

select t.* from ts  t,-- 重复数据，取主键最小的一条

(select  min(id) id, name,age , count(*) COUNT from ts group by name,age having count >1 order by count desc) b where t.id=b.id and t.name=b.name and t.age=b.age

union

SELECT t.* FROM ts  t, -- 不重复的数据，当然也要取出来了

(SELECT  MAX(id) AS id,COUNT(*) AS COUNT FROM ts GROUP BY NAME,age HAVING COUNT =1 ) b where t.id=b.id) tm order by id

SELECT * FROM ts1;

得到的结果为,正好满足条件

RENAME TABLE  ts TO ts_tmp;

RENAME TABLE ts1 TO ts;

SELECT * FROM ts;