MySQL 是一个将数据以行和列的形式存储在表中的数据库应用程序。这个数据库应用程序可以在表中存储重复记录,这可能会影响MySQL中的数据库性能。然而,数据重复是由于各种原因而发生的,删除表中的重复值是在使用MySQL数据库时的一项重要任务。
通常,最好在表上使用唯一约束来存储防止有重复行的数据。 在本文中,我们将学习如何从MySQL数据库中删除重复记录 。
让我们通过一个示例来理解。假设我们有一个名为 “student_contacts” 的表,其中包含许多重复记录:
现在,我们将看到如何从表中删除重复记录。 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 。执行该语句后,我们将获得以下输出:
上面的输出表示从表中删除了 五个记录 。我们可以通过执行下面的查询来验证这一点,该查询返回表中的重复记录。
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()函数删除重复记录
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;
执行之后,我们将得到以下输出:
如果您只想获取重复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 语句和 子查询 在 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语句来验证是否删除了重复的行。
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;
请查看下面的图像,以了解上述步骤。