Mysql 简明教程
MySQL - Delete Duplicate Records
The MySQL Delete Duplicate Records
MySQL 数据库(包括 MySQL)中的重复记录非常常见。MySQL 数据库以表格的形式存储数据,表格由行和列组成。如果数据库表中的两行或多行具有相同的值,则称记录出现重复。
此冗余可能出于以下原因:
-
行插入了两次。
-
从外部来源导入原始数据时。
-
数据库应用程序中可能存在错误。
无论出于什么原因,删除这样的冗余变得很重要,以减少错误提高数据准确性,或提高数据库的性能效率。
Find Duplicate Values
在删除重复记录之前,我们必须先找出它们是否存在于表中。这可以使用如下方法完成:
-
GROUP BY Clause
-
COUNT() Method
Example
让我们先创建一个名为 “CUSTOMERS” 的表,其中包含重复值:
CREATE TABLE CUSTOMERS(
ID int,
NAME varchar(100)
);
使用以下 INSERT 查询,向 “CUSTOMERS” 表中插入几条记录。在这里,我们已经将 “John” 作为重复记录添加了 3 次:
INSERT INTO CUSTOMERS
VALUES (1,'John'), (2,'Johnson'), (3,'John'), (4,'John');
获得的 CUSTOMERS 表如下:−
现在,我们正在使用 COUNT() 方法和 GROUP BY 子句中所示的以下查询检索表中重复的记录:
SELECT NAME, COUNT(NAME) FROM CUSTOMERS
GROUP BY NAME HAVING COUNT(NAME) > 1;
Delete Duplicate Records
要从数据库表中删除重复记录,我们可以使用 DELETE 命令。但是,这个 DELETE 命令可以用两种方式来删除表中的重复内容:
-
Using DELETE… JOIN
-
Using ROW_NUMBER() Function
Using DELETE… JOIN
为了使用 DELETE… JOIN 命令从表中删除重复记录,我们在自身上执行内连接。这适用于并非完全相同的情况。
例如,假设客户详细信息在客户记录中重复,但序列号不断增加。在这种情况下,即使 ID 不相同,也会复制该记录。
Example
在以下查询中,我们使用先前创建的 CUSTOMERS 表使用 DELETE… JOIN 命令删除重复记录:
DELETE t1 FROM CUSTOMERS t1
INNER JOIN CUSTOMERS t2
WHERE t1.id < t2.id AND t1.name = t2.name;
Output
以下是所获得的输出 −
Query OK, 2 rows affected (0.01 sec)
Verification
我们可以使用以下 SELECT 语句来验证是否已删除重复记录:
SELECT * FROM CUSTOMERS;
我们可以看到在所得表中,该查询删除了重复项,并在表中留下了不同的记录:
Using ROW_NUMBER() Function
在 MySQL 中,ROW_NUMBER() 函数用于给从查询所得结果集中的每一行一个顺序号,从 1 开始。
使用此函数,MySQL 允许你检测重复的行,可以用 DELETE 语句删除这些行。
Example
在这里,我们对 'NAME' 列中有重复值的 CUSTOMERS 表应用 ROW_NUMBER() 函数。我们将使用以下查询根据 'NAME' 列在分区内分配行号:
SELECT id, ROW_NUMBER()
OVER (PARTITION BY name ORDER BY name) AS row_num
FROM CUSTOMERS;
以下是所获得的输出 −
现在,使用以下语句,删除重复行(行号大于 1 的行):
DELETE FROM CUSTOMERS WHERE id IN(
SELECT id FROM (SELECT id, ROW_NUMBER()
OVER (PARTITION BY name ORDER BY name) AS row_num
FROM CUSTOMERS) AS temp_table WHERE row_num>1
);
我们得到了如下输出 −
Query OK, 2 rows affected (0.00 sec)
为了验证是否已删除重复记录,请使用以下 SELECT 查询:
SELECT * FROM CUSTOMERS;
产生的结果如下 −