ลบข้อมูลซ้ำใน MySQL คงเหลือเพียง 1 แถวด้วย Temporary Table

วิธีการ: ใช้ Temporary Table

ขั้นตอนหลัก 4 ขั้นตอน:

1. สร้าง temp table จากข้อมูล distinct
2. TRUNCATE ตารางต้นฉบับ
3. INSERT ข้อมูลกลับจาก temp table
4. DROP temp table

ไวยากรณ์

CREATE TABLE temp_table AS
SELECT DISTINCT col1, col2 FROM original_table;

TRUNCATE TABLE original_table;

INSERT INTO original_table (col1, col2)
SELECT col1, col2 FROM temp_table;

DROP TABLE temp_table;

ตัวอย่าง: ตาราง demo39 มีข้อมูลซ้ำ

CREATE TABLE demo39 (
    user_id INT,
    user_name VARCHAR(20)
);

INSERT INTO demo39 VALUES
(10, 'John'), (10, 'John'),
(11, 'David'), (11, 'David');

ข้อมูลก่อนลบซ้ำ

+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 10      | John      |
| 10      | John      |
| 11      | David     |
| 11      | David     |
+---------+-----------+

รันคำสั่งลบซ้ำ

CREATE TABLE temp_table AS
SELECT DISTINCT user_id, user_name FROM demo39;

TRUNCATE TABLE demo39;

INSERT INTO demo39 (user_id, user_name)
SELECT user_id, user_name FROM temp_table;

DROP TABLE temp_table;

ผลลัพธ์: คงเหลือ 1 แถวต่อค่า

+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 10      | John      |
| 11      | David     |
+---------+-----------+

วิธีอื่นๆ (MySQL 8.0+)

1. ใช้ ROW_NUMBER() + CTE

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, user_name ORDER BY user_id) AS rn
    FROM demo39
)
DELETE FROM demo39
WHERE (user_id, user_name) IN (
    SELECT user_id, user_name FROM ranked WHERE rn > 1
);

2. ใช้ JOIN กับตารางเอง (Self-JOIN)

DELETE t1 FROM demo39 t1
INNER JOIN demo39 t2
WHERE t1.user_id = t2.user_id
  AND t1.user_name = t2.user_name
  AND t1.id > t2.id;  -- ต้องมี PK/unique column

3. ALTER IGNORE TABLE (MySQL 5.7 ลบใน 8.0)

ALTER IGNORE TABLE demo39 ADD UNIQUE INDEX idx_unique (user_id, user_name);

⚠️ ไม่แนะนำ: ถูกลบใน MySQL 8.0 และอาจสูญเสียข้อมูลโดยไม่เตือน

สรุปการเลือกวิธี