MySQL_删除重复数据

建表，插入数据，当然如果你不想测试一下，也可以直接用后面的删除语句

CREATE TABLE IF NOT EXISTS `tb01` (
	`id` INT UNSIGNED PRIMARY KEY auto_increment,
	`name` VARCHAR (10) NOT NULL
);

INSERT INTO `tb01` (`name`)
VALUES
	('zhangsan'),
	('lisi'),
	('wangwu'),
	('zhangsan'),
	('lisi'),
	('wangwu'),
	('zhaoliu'),
	('wangwu');

查询

mysql> select * from tb01;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhangsan |
|  5 | lisi     |
|  6 | wangwu   |
|  7 | zhaoliu  |
|  8 | wangwu   |
+----+----------+
8 rows in set (0.01 sec)

删除语句

# 用法：
DELETE FROM `表名` WHERE `主键名` NOT IN (SELECT `主键名` from (SELECT `主键名` FROM `表名` GROUP BY `重复字段`) `temp`);

# 实例：
mysql> DELETE FROM `tb01` WHERE `id` NOT IN (SELECT `id` from (SELECT `id` FROM `tb01` GROUP BY `name`) `temp`);
Query OK, 4 rows affected (0.01 sec)

你或许有疑问为什么不这样写呢：

DELETE FROM `tb01` WHERE `id` NOT IN (SELECT `id` FROM `tb01` GROUP BY `name`)

试一下吧

mysql> DELETE FROM `tb01` WHERE `id` NOT IN (SELECT `id` FROM `tb01` GROUP BY `name`);
ERROR 1093 (HY000): You can't specify target table 'tb01' for update in FROM clause

在同一条sql语句中，不能先查出这个表的某个值，又去修改这个表，所以要倒腾一下。

再次查询，重复的已经删除

mysql> select * from tb01;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  7 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)