首页 > 建站教程 > 数据库 >  MySQL 删除重复记录正文

MySQL 删除重复记录

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

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

让我们通过一个示例来理解。假设我们有一个名为 “student_contacts” 的表,其中包含许多重复记录:

MySQL删除重复数据

现在,我们将看到如何从表中删除重复记录。 MySQL可以通过三种方式主要删除重复记录。


1. 使用Delete Join删除重复记录

我们可以使用MySQL中的DELETE JOIN语句来快速删除重复记录。以下语句从表中删除重复行并保留最大的id:

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

这个查询两次引用了student_contacts表 。因此,我们将使用表别名 S1 和 S2 。执行该语句后,我们将获得以下输出:

MySQL删除重复数据

上面的输出表示从表中删除了 五个记录 。我们可以通过执行下面的查询来验证这一点,该查询返回表中的重复记录。

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

它将返回如下输出,显示为空集 。 它意味着重复记录已经成功地从表中删除。

MySQL删除重复数据

我们还可以通过使用SELECT语句来验证它。在下面的图像中,我们可以看到表中没有重复记录。

MySQL删除重复数据

假设我们想要删除重复的记录并且保留表中最低的id。在这种情况下,我们将使用以下语句:

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

请注意,在执行查询之前,我们需要再次创建包含重复记录的表。执行完成后,我们将获得以下输出:

MySQL删除重复数据

我们还可以通过使用SELECT语句来验证它。在下面的图像中,我们可以看到较高id的重复记录已被删除。

MySQL删除重复数据


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

ROW_NUMBER()函数对分区内的每一行返回顺序号,从1到分区中存在的行数。

我们可以使用以下语句,使用ROW_NUMBER()函数为每一行分配一个顺序号。 ROW_NUMBER()函数 . 如果此查询发现表中的 name 列重复,则将分配 大于1的行号 。

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

执行之后,我们将得到以下输出:

MySQL删除重复数据

如果您只想获取重复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;

此语句会返回下面的输出:

MySQL删除重复数据

我们现在可以通过使用 DELETE 语句和 子查询 在 WHERE 子句中的帮助下从 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
);

执行后,我们将得到如下图像的输出,我们可以看到该语句已从表中删除了五条记录。您可以使用SELECT语句来验证是否删除了重复的行。

MySQL删除重复数据


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.


第三步:

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


请查看下面的图像,以了解上述步骤。

MySQL删除重复数据