MySQL 如何删除MySQL表中的所有重复记录？

在实际开发中，我们经常会遇到需要删除MySQL表中所有重复记录的情况。本文将介绍如何使用SQL语句来删除表中的重复记录。

阅读更多：MySQL 教程

示例数据

为了方便演示，我们在demo数据库中创建一个person表，并向其中插入一些实验数据。person表的结构如下所示：

CREATE TABLE `person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `age` INT NOT NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

向表中插入以下数据：

INSERT INTO person(name, age, email) VALUES('张三', 20, 'zhangsan@example.com');
INSERT INTO person(name, age, email) VALUES('李四', 22, 'lisi@example.com');
INSERT INTO person(name, age, email) VALUES('王五', 20, 'wangwu@example.com');
INSERT INTO person(name, age, email) VALUES('赵六', 22, 'zhaoliu@example.com');
INSERT INTO person(name, age, email) VALUES('张三', 20, 'zhangsan@example.com');
INSERT INTO person(name, age, email) VALUES('王五', 20, NULL);

数据插入完成后，使用如下SQL语句查询person表中的所有记录：

SELECT * FROM person;

结果如下所示：

可以看到，person表中存在重复的记录，我们需要使用SQL语句删除这些记录。

方法一：使用DISTINCT关键字

第一种方法是使用DISTINCT关键字来删除person表中的重复记录。DISTINCT可以用于查询不同的值，同时可以结合DELETE关键字删除每个不同值的多余记录。具体步骤如下：

1. 创建一个临时表temp，该表包含不同的name和age组合，该组合是唯一的。SQL语句如下所示：

CREATE TEMPORARY TABLE temp
SELECT DISTINCT name, age FROM person;

2. 使用DELETE关键字删除person表中和temp表中重复的记录。SQL语句如下所示：

DELETE FROM person
WHERE (name, age) NOT IN (SELECT name, age FROM temp);

执行以上SQL语句后，再次查询person表，结果如下所示：

可以看到，person表中的重复记录已经被删除了。

方法二：使用GROUP BY和HAVING子句

第二种方法是使用GROUP BY和HAVING子句来删除表中的重复记录。GROUP BY可以将相同的行分组在一起，而HAVING可以过滤分组后的数据。具体步骤如下：

1. 使用以下SQL语句查询出所有重复的记录：

SELECT name, age, COUNT(*) cnt
FROM person
GROUP BY name, age
HAVING cnt > 1;

执行以上SQL语句后，结果如下所示：

| name | age | cnt| —- | — | — |
| 张三 | 20 | 2 |
| 王五 | 20 | 2 |

可以看到，查询结果中显示了重复的name和age组合，并且标明了重复的次数cnt。

2. 使用DELETE关键字删除重复的记录。SQL语句如下所示：

DELETE FROM person
WHERE (name, age) IN (SELECT name, age
                      FROM (SELECT name, age
                            FROM person
                            GROUP BY name, age
                            HAVING COUNT(*) > 1) t);

执行以上SQL语句后，再次查询person表，结果如下所示：

可以看到，person表中的重复记录已经被删除了。

方法三：使用ROW_NUMBER()函数

第三种方法是使用ROW_NUMBER()函数来删除表中的重复记录。ROW_NUMBER()函数会为每一行数据指定一个行号，根据行号来过滤数据。具体步骤如下：

1. 使用如下SQL语句查询出所有重复的记录：

SELECT name, age, email, ROW_NUMBER() OVER(PARTITION BY name, age ORDER BY id) row_num
FROM person;

执行以上SQL语句后，结果如下所示：

可以看到，查询结果中显示了所有记录的name、age、email和行号row_num。

2. 使用DELETE关键字删除重复的记录。SQL语句如下所示：

DELETE FROM person
WHERE id IN (SELECT id FROM (
                SELECT id, ROW_NUMBER() OVER(PARTITION BY name, age ORDER BY id) row_num
                FROM person) t
            WHERE t.row_num > 1);

执行以上SQL语句后，再次查询person表，结果如下所示：

可以看到，person表中的重复记录已经被删除了。

结论

以上三种方法都可以用来删除MySQL表中的重复记录，具体方法根据自己的需要选择。对于少量数据而言，使用DISTINCT关键字或者GROUP BY和HAVING子句比较方便简单；当数据量比较大时，可以使用ROW_NUMBER()函数，这种方法更为高效。无论采用哪种方法，使用SQL语句进行删除操作时，务必要避免数据丢失，因此在实际使用时一定要慎重！