ลบแถวที่ซ้ำกันใน MySQL

เมื่อมีข้อมูลซ้ำเกิดขึ้นในตารางจะด้วยสาเหตุอะไรก็แล้วแต่ แต่มันมีข้อมูลซ้ำเกิดขึ้นแล้วและเราต้องการลบตัวที่ซ้ำออกไป เราจะทำยังไงมาดูแนวทางกัน

เริ่มต้นเราดูตัวอย่างข้อมูลที่ซ้ำกันก่อน

DROP TABLE IF EXISTS contacts;
 
CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL, 
    email VARCHAR(255) NOT NULL
);
 
INSERT INTO contacts (first_name,last_name,email) 
VALUES ('Carine ','Schmitt','carine.schmitt@verizon.net'),
       ('Jean','King','jean.king@me.com'),
       ('Peter','Ferguson','peter.ferguson@google.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       ('Janine ','Labrune','janine.labrune@aol.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Zbyszek ','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com'),
       ('Julie','Murphy','julie.murphy@yahoo.com'),
       ('Kwai','Lee','kwai.lee@google.com'),
       ('Jean','King','jean.king@me.com'),
       ('Susan','Nelson','susan.nelson@comcast.net'),
       ('Roland','Keitel','roland.keitel@yahoo.com');

มาดูชุดข้อมูลที่ซ้ำกันบ้างว่ามีแถวใดบ้างที่ซ้ำกัน

SELECT 
    email, 
    COUNT(email),
    GROUP_CONCAT(id) AS id_list
FROM contacts
GROUP BY email
HAVING COUNT(email) > 1;

ในแถวที่ซ้ำกันเรา และอยากเก็บไว้แค่ 1 แถว
วิธีที่ 1
ทำการลบด้วยคำสั่งดังนี้

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE c1.id > c2.id AND c1.email = c2.email;

จากคำสั่งข้างบนหมายความว่าเมื่อมีแถวที่ซ้ำกันให้เก็บไว้เฉพาะแถวแรกที่พบ (id ที่ค่าน้อยกว่า) ซึ่งผลลัพธ์ได้ก็ประมาณนี้ (id 6, 12, 13, 14 จะถูกลบไป)

วิธีที่ 2

ALTER IGNORE TABLE contacts 
ADD UNIQUE INDEX idx1(email);

ผลลัพธ์ที่ได้ก็เป็นเช่นคำสั่งด้านบน แต่ขอ หมายเหตุไว้หน่อยเนื่องจาก ALTER IGNORE TABLE ถูกยกเลิกไปแล้วใน MySQL เวอร์ชั่น 5.7.4 เป็นต้นไปวิธีนี้ก็เลยใช้ได้เฉพาะเวอร์ชั่นที่ก่อนหน้าที่ระบุนะครับ

วิธีที่ 3

เนื่องจากหมายเหตุในวิธีที่ 2 ใน MySQL เวอร์ชั่นใหม่ ๆ ก็เลยใช้วิธีแบบนี้แทน

CREATE TABLE contacts_copy LIKE contacts;
ALTER TABLE contacts_copy ADD UNIQUE INDEX idx1(email);

INSERT IGNORE INTO contacts_copy
SELECT * FROM contacts;

DROP TABLE contacts;
ALTER TABLE contacts_copy RENAME TO contacts;

จบปิ๊ง ๆ
ไปลองทดสอบกันได้ที่ https://www.db-fiddle.com/f/sXB3sBDQEdrbdEDFi98Y3j/0
**ส่วนความเจ็บปวดซ้ำ ๆ หน่ะมันลบไม่ได้หรอกนะ