MySQL 删除过滤掉重复数据

创新表结构

CREATE TABLE animal (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

添加数据

INSERT INTO animal (name, age) VALUES (‘cat’, ‘12’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘13’);
INSERT INTO animal (name, age) VALUES (‘camel’, ‘25’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘32’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘42’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘12’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘13’);
INSERT INTO animal (name, age) VALUES (‘camel’, ‘25’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘32’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘42’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘12’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘13’);
INSERT INTO animal (name, age) VALUES (‘camel’, ‘25’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘32’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘42’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘12’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘13’);
INSERT INTO animal (name, age) VALUES (‘camel’, ‘25’);
INSERT INTO animal (name, age) VALUES (‘cat’, ‘32’);
INSERT INTO animal (name, age) VALUES (‘dog’, ‘42’);

先看看哪些数据重复了

SELECT name, count( 1 ) FROM animal GROUP BY name HAVING count( 1 ) > 1;

看看需要删除的数据

SELECT * FROM animal WHERE id NOT IN (SELECT t.id FROM ( SELECT MIN( id ) AS id FROM animal GROUP BY name ) t );

开始删除重复数据，仅留一条

DELETE FROM animal WHERE id NOT IN (SELECT t.id FROM ( SELECT MIN( id ) AS id FROM animal GROUP BY name ) t );