MySQL是一个数据库应用程序,以行和列的形式将数据存储在表中。该数据库应用程序可以在表中存储重复的记录,这可能会影响MySQL中数据库的性能。但是,由于各种原因会发生数据重复,因此在 MySQL 中使用数据库时,删除表中的重复值是一项重要的任务。
通常,最好始终在表上使用唯一约束来存储防止重复行的数据。 在本文中,将学习如何从MySQL数据库中删除重复的记录。
让无涯教程借助一个示例来理解它。假设有一个名为" student_contacts" 的表,其中包含许多重复的记录:
现在,将看到如何从表中删除重复的记录。 MySQL可以通过三种方式删除重复记录。
无涯教程可以使用 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的重复记录已被删除。
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语句验证是否删除了重复的行。
还可以使用中间表从表中删除重复的记录。以下是在中间表的帮助下删除重复记录的要点:
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;
请参阅下图以了解上述步骤。
祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)