本教程将向您展示在MySQL中删除重复行的各种方法。
在上一个教程中,我们向您展示了如何在表中找到重复的值。当确定了表中有重复的行,您可能需要删除它们来清理这些不必要的数据。
准备示例数据
以下脚本创建contacts表,并将示例数据插入到用于演示的contacts表中。
USE testdb;DROP TABLE IF EXISTS contacts;CREATE TABLE contacts ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) DEFAULT NULL, last_name VARCHAR(50) DEFAULT NULL, email VARCHAR(255) NOT NULL);INSERT INTO contacts (first_name,last_name,email) VALUES ('Carine ','Schmitt','carine.schmitt@yiibai.com'), ('Jean','King','jean.king@gmail.com'), ('Peter','Ferguson','peter.ferguson@google.com'), ('Janine ','Labrune','janine.labrune@qq.com'), ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'), ('Janine ','Labrune','janine.labrune@qq.com'), ('Susan','Nelson','susan.nelson@qq.com'), ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.com'), ('Roland','Keitel','roland.keitel@yahoo.com'), ('Julie','Murphy','julie.murphy@yahoo.com'), ('Kwai','Lee','kwai.lee@google.com'), ('Jean','King','jean.king@qq.com'), ('Susan','Nelson','susan.nelson@qq.com'), ('Roland','Keitel','roland.keitel@yahoo.com');
执行DELETE语句后,可以执行此脚本来重新创建测试数据。
以下查询返回contacts表中的重复email值:
SELECT email, COUNT(email)FROM contactsGROUP BY emailHAVING COUNT(email) > 1;
执行上面查询语句,得到以下结果 -
+-------------------------+--------------+| email | COUNT(email) |+-------------------------+--------------+| janine.labrune@qq.com | 2 || roland.keitel@yahoo.com | 2 || susan.nelson@qq.com | 2 |+-------------------------+--------------+3 rows in set
可以看到,表中有重复email行记录。
使用DELETE JOIN语句删除重复的行
MySQL为您提供了可用于快速删除重复行的DELETE JOIN语句。
以下语句删除重复的行并保持最高的ID:
DELETE t1 FROM contacts t1 INNER JOIN contacts t2 WHERE t1.id < t2.id AND t1.email = t2.email;Query OK, 3 rows affected
如上所示,有3行记录已被删除。我们再次执行查找重复的电子邮件的查询:
SELECT email, COUNT(email)FROM contactsGROUP BY emailHAVING COUNT(email) > 1;
该查询返回一个空集合,这意味着重复的行已被删除。
我们来查询验证contacts表中的数据:
SELECT *FROM contacts;
ID为4,7和9的行记录已被删除。
如果要删除重复的行并保留最低的ID,则可以使用以下语句:
DELETE t1 FROM contacts t1 INNER JOIN contacts t2 WHERE t1.id > t2.id AND t1.email = t2.email;
可以再次执行创建contacts表的脚本并测试此查询,以下输出显示删除重复行后的contacts表的数据。
使用直接表删除重复的行
以下是使用直接表删除重复行的步骤:
- 创建一个新表,其结构与要删除重复行的原始表相同。
- 将原始表中的不同行插入直接表。
- 删除原始表并将直接表重命名为原始表。
以下查询说明了以下步骤:
步骤1 -
CREATE TABLE source_copy FROM source;
步骤2 -
INSERT INTO source_copySELECT * FROM sourceGROUP BY col; -- column that has duplicate values
步骤3 -
DROP TABLE source;ALTER TABLE source_copy RENAME TO source;
例如,以下语句从contacts表中删除具有重复电子邮件(email)的行记录:
-- step 1CREATE TABLE contacts_temp LIKE contacts;-- step 2INSERT INTO contacts_temp(email) SELECT email FROM contacts GROUP BY email;-- step 3DROP TABLE contacts;ALTER TABLE contacts_temp RENAME TO contacts;
在本教程中,您已经学习了如何使用DELETE JOIN语句或直接表删除MySQL中的重复行。