关注我们

MySQL - 删除重复记录

MySQL是一个数据库应用程序，以行和列的形式将数据存储在表中。该数据库应用程序可以在表中存储重复的记录，这可能会影响MySQL中数据库的性能。但是，由于各种原因会发生数据重复，因此在 MySQL 中使用数据库时，删除表中的重复值是一项重要的任务。

通常，最好始终在表上使用唯一约束来存储防止重复行的数据。 在本文中，将学习如何从MySQL数据库中删除重复的记录。

让无涯教程借助一个示例来理解它。假设有一个名为" student_contacts" 的表，其中包含许多重复的记录:

现在，将看到如何从表中删除重复的记录。 MySQL可以通过三种方式删除重复记录。

1.使用删除联接删除重复记录

无涯教程可以使用 MySQL中的DELETE JOIN语句，该语句允许快速删除重复的记录。以下语句从表中删除重复的行，并保留最大的id:

复制代码

DELETE S1 FROM student_contacts AS S1
INNER JOIN student_contacts AS S2 
WHERE S1.id

此查询两次引用了Student_contacts表。因此，将使用表别名 S1 和 S2 。执行该语句后，将获得以下输出:

以上输出表明已从表中删除 5条记录。可以通过执行以下查询以返回表的重复记录来验证这一点。

复制代码

SELECT name, email, COUNT(name)
FROM student_contacts
GROUP BY name
HAVING COUNT(name) > 1;

它将返回如下输出，显示一个空集。这意味着重复的记录已成功从表中删除。

无涯教程还可以使用 SELECT语句进行验证。在下图中，可以看到表中没有重复的记录。

假设要删除重复的记录并在表中保留最低的ID。在这种情况下，将使用以下语句:

复制代码

DELETE S1 FROM student_contacts AS S1
INNER JOIN student_contacts AS S2 
WHERE S1.id > S2.id AND S1.email = S2.email;

注意，在执行查询之前，需要再次创建一个包含重复记录的表。执行该语句后，将获得以下输出:

也可以使用SELECT语句来验证它。在下图中，可以看到具有较高ID的重复记录已被删除。

2.使用ROW_NUMBER()函数删除重复记录

T ROW_NUMBER()函数返回分区中每一行的序号，从1到分区中存在的行数。

可以使用下面的语句，该语句使用 ROW_NUMBER()函数为每一行分配一个顺序号。如果此查询找到表重复项的 name 列，为行号分配大于一的行。

复制代码

SELECT id, name, ROW_NUMBER() 
OVER (PARTITION BY name ORDER BY name) AS row_num 
FROM student_contacts;

执行后，将得到如下输出:

如果只想获取重复的id行，请使用以下语句:

复制代码

SELECT id FROM (SELECT id, ROW_NUMBER() 
OVER (PARTITION BY name ORDER BY name) AS row_num 
FROM student_contacts) AS temp_table WHERE row_num>1;

该语句重新运行以下输出:

现在，无涯教程可以借助 DELETE 语句从 student_contacts 表中删除重复的记录。请参阅以下语句:

复制代码

DELETE FROM student_contacts WHERE id IN(
    SELECT id FROM (SELECT id, ROW_NUMBER() 
       OVER (PARTITION BY name ORDER BY name) AS row_num 
    FROM student_contacts) AS temp_table WHERE row_num>1
);

执行后，将得到如下图所示的输出，可以看到该语句已从表中删除了5条记录。您可以使用SELECT语句验证是否删除了重复的行。

3.使用中间表删除重复的行

还可以使用中间表从表中删除重复的记录。以下是在中间表的帮助下删除重复记录的要点:

1.创建一个具有与原始表相同结构的新表，将使用该表删除重复的记录。

复制代码

mysql> CREATE TABLE new_table_name LIKE source_table_name;

2.将原始表的唯一(不同)行插入到新创建的表中。

复制代码

mysql> INSERT INTO new_table_name
SELECT * FROM source_table_name
GROUP BY column; //It is the name of a column that contains duplicate values.

3.删除原始表，然后将新创建的表重命名为与原始表相同的表。

复制代码

mysql> DROP TABLE source_table_name;
mysql> ALTER TABLE new_table_name RENAME TO source_table_name;

借助以下查询来理解上述步骤，这些查询使用中间表删除重复记录:

第1步:

复制代码

mysql> CREATE TABLE student_contacts_temp LIKE student_contacts;

第2步:

复制代码

mysql> INSERT INTO student_contacts_temp
SELECT * FROM student_contacts 
GROUP BY email; //It is the name of column that contains duplicate values.

第3步:

复制代码

mysql> DROP TABLE student_contacts;
mysql> ALTER TABLE student_contacts_temp RENAME TO student_contacts;

请参阅下图以了解上述步骤。

祝学习愉快！(内容编辑有误？请选中要编辑内容 -> 右键 -> 修改 -> 提交！)

技术教程推荐

JavaScript核心原理解析 -〔周爱民〕

Spark性能调优实战 -〔吴磊〕

Web漏洞挖掘实战 -〔王昊天〕

朱涛 · Kotlin编程第一课 -〔朱涛〕

遗留系统现代化实战 -〔姚琪琳〕

好记忆不如烂笔头。留下您的足迹吧 :)