Xóa dòng trùng trong MySQL
Xem thửTình huống mở đầu
Chiến dịch email của TechShop gửi bản tin cho 7 dòng đăng ký, nhưng khách Ann kêu nhận ba bản giống nhau trong một buổi sáng. Bạn mở bảng subscribers và thấy ann@x.com xuất hiện ở ba dòng khác nhau, id 1, 3 và 5. Nguyên nhân là form đăng ký bị bấm nhiều lần và bảng chưa có ràng buộc duy nhất. Sếp yêu cầu: dọn trong ngày, mỗi email chỉ còn một dòng và giữ dòng cũ nhất.
Hãy đoán trước khi đọc tiếp: nếu chỉ có hai bảng này, một câu DELETE có tự tìm ra "dòng nào là bản sao" được không?
Ôn nhanh bài trước: DELETE c FROM carts c JOIN customers u ... làm bảng nào mất dòng?
Đáp án: chỉ bảng carts, bảng đứng giữa DELETE và FROM. Bài này dùng đúng cú pháp đó, nhưng cho chính bảng ghép với chính nó.
Mục tiêu bài học
Sau bài này bạn có thể:
- Tìm các nhóm dòng trùng bằng
GROUP BY ... HAVING COUNT(*) > 1. - Xóa bản sao và giữ dòng id nhỏ nhất (hoặc lớn nhất) bằng self-join
DELETE, hoặc bằngROW_NUMBER(). - Thêm
UNIQUE KEYsau khi dọn để trùng không tái phát, và hiểu vì sao phải thêm sau chứ không trước. - Nhận ra ba bẫy: lỗi 1093, dòng
NULLkhông bao giờ "trùng" khi so bằng dấu=, và so sánh không phân biệt hoa thường.
Bức tranh lớn
Xuyên suốt bài, mỗi dòng của subscribers là một phiếu đăng ký. Bản sao là phiếu nộp lại. Nhân viên dọn kiểm từng phiếu, nếu thấy một phiếu cùng email nộp trước đó thì bỏ phiếu mới hơn đi. Dự án xuyên suốt là danh sách nhận tin của TechShop.
Hình dưới trả lời một câu hỏi: dọn dòng trùng gồm những bước nào, theo thứ tự nào?
Đang tải sơ đồ…
Cách đọc hình: đọc từ trên xuống dưới. Ô xanh dương là bước tìm trùng chỉ để xem, hai ô tím là bước chọn và xóa, ô xanh lá cuối cùng là bước chặn tái phát. Bước 4 không thể đảo lên trước bước 3 vì sẽ thất bại, như phần sau chứng minh.
Chỗ ẩn dụ không còn đúng: nhân viên thật nhìn tên người nộp, còn MySQL chỉ so sánh đúng cột bạn chọn. Hai phiếu khác tên nhưng cùng email vẫn là bản sao nếu bạn định nghĩa trùng theo email.
Khái niệm cốt lõi
Bài này có bốn khái niệm.
1. Định nghĩa trùng theo cột nào
Bản sao không tự lộ ra. Bạn phải chọn cột (hoặc tổ hợp cột) làm khóa trùng, ở đây là email. GROUP BY email HAVING COUNT(*) > 1 liệt kê các nhóm có từ hai dòng trở lên.
2. Self-join DELETE giữ id nhỏ nhất
Ghép bảng với chính nó, t1 là ứng viên bị xóa còn t2 là bản sao cũ hơn. Điều kiện t1.email = t2.email AND t1.id > t2.id chọn mọi dòng có một dòng cũ hơn cùng email. Dòng cũ nhất của nhóm không có bản sao nào cũ hơn nên luôn được giữ.
3. ROW_NUMBER là cách khác, linh hoạt hơn
Từ MySQL 8.0, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) đánh số các dòng trong từng nhóm. Dòng có số thứ tự lớn hơn 1 là bản sao. Muốn giữ dòng mới nhất, đổi thành ORDER BY id DESC.
4. UNIQUE KEY để không tái phát
Sau khi dọn xong, ALTER TABLE ... ADD UNIQUE KEY biến việc trùng từ chuyện lặng lẽ thành lỗi 1062 ngay lúc chèn. Làm bước này khi dữ liệu còn trùng sẽ thất bại.
Ví dụ đầu tiên
Dựng bảng với bảy dòng. Ba dòng ann, hai dòng binh, và hai dòng chỉ có một bản.
CREATE TABLE subscribers (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(40),
name VARCHAR(20)
);
Bước 1, tìm các nhóm trùng. Dự đoán trước khi xem: có mấy nhóm, mỗi nhóm bao nhiêu bản?
SELECT email, COUNT(*) AS copies, MIN(id) AS keep_id
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;
Kết quả [Đã chạy: MySQL 8.0.44]:
+------------+--------+---------+
| email | copies | keep_id |
+------------+--------+---------+
| ann@x.com | 3 | 1 |
| binh@x.com | 2 | 2 |
+------------+--------+---------+
Bước 2, xem các cặp mà self-join sẽ tạo. Mỗi hàng là một dòng t1 sắp bị xóa và một dòng t2 cũ hơn cùng email:
SELECT t1.id AS victim, t2.id AS keeper
FROM subscribers t1
JOIN subscribers t2 ON t1.email = t2.email AND t1.id > t2.id
ORDER BY t1.id, t2.id;
+--------+--------+
| victim | keeper |
+--------+--------+
| 3 | 1 |
| 5 | 1 |
| 5 | 3 |
| 6 | 2 |
+--------+--------+
Bốn cặp nhưng chỉ ba dòng khác nhau ở cột victim: dòng 5 xuất hiện hai lần vì cả dòng 1 và dòng 3 đều cũ hơn nó. Bước 3, xóa:
DELETE t1
FROM subscribers t1
JOIN subscribers t2 ON t1.email = t2.email AND t1.id > t2.id;
SELECT ROW_COUNT() AS deleted;
SELECT id, email, name FROM subscribers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+------------+------+
| id | email | name |
+----+------------+------+
| 1 | ann@x.com | Ann |
| 2 | binh@x.com | Binh |
| 4 | chi@x.com | Chi |
| 7 | dung@x.com | Dung |
+----+------------+------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
DELETE t1 | Chỉ bảng bí danh t1 mất dòng | t2 chỉ dùng để tìm bản sao cũ hơn |
FROM subscribers t1 JOIN subscribers t2 | Ghép bảng với chính nó dưới hai bí danh | Cần hai bí danh để phân biệt hai vai: bị xóa và cũ hơn |
ON t1.email = t2.email | Chỉ ghép các dòng cùng email | Đây là định nghĩa trùng của bài |
AND t1.id > t2.id | Chỉ giữ cặp mà t2 cũ hơn t1 | Dòng cũ nhất không có cặp nên không bao giờ bị xóa |
SELECT ROW_COUNT() | Trả số dòng bị xóa | 3, dù self-join có 4 cặp |
Xem từng bước
Animation dưới đây xét từng dòng theo đúng thứ tự trên. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.
Đang tải Animation…
Bảng trace bằng chữ [Đã chạy: khớp các kết quả pairs và dedupe ở trên]:
| Id | Dòng cũ hơn cùng email | Kết quả | |
|---|---|---|---|
| 1 | ann@x.com | không có | Giữ |
| 2 | binh@x.com | không có | Giữ |
| 3 | ann@x.com | id 1 | Xóa |
| 4 | chi@x.com | không có | Giữ |
| 5 | ann@x.com | id 1 và id 3 | Xóa |
| 6 | binh@x.com | id 2 | Xóa |
| 7 | dung@x.com | không có | Giữ |
Bạn vừa làm được: biến một bảng có bản sao thành bảng mỗi email đúng một dòng, và giải thích được vì sao dòng được giữ là dòng cũ nhất.
Concept Check
Dùng bảy dòng của bài.
Câu 1. Nếu đổi t1.id > t2.id thành t1.id < t2.id, những id nào còn lại?
- A. 1, 2, 4, 7, vì điều kiện đổi chiều không làm thay đổi dòng bị xóa.
- B. 3, 5, 6, vì chỉ các dòng trùng mới còn lại.
- C. 4, 5, 6, 7, vì giữ dòng mới nhất của mỗi email.
- D. Không còn dòng nào, vì mọi dòng đều thỏa điều kiện.
Câu 2. Self-join ở bước 2 cho 4 cặp, vậy ROW_COUNT() sau DELETE là bao nhiêu?
- A. 4, vì mỗi cặp xóa một dòng.
- B. 3, vì dòng id 5 nằm trong hai cặp nhưng chỉ bị xóa một lần.
- C. 7, vì tất cả các dòng đều tham gia phép ghép.
- D. 2, vì chỉ hai nhóm email có bản sao.
Câu 3. Trên bảng còn bản sao, chạy ALTER TABLE subscribers ADD UNIQUE KEY uq_email (email) thì sao?
- A. Thành công, vì MySQL tự xóa bản sao cũ trước khi tạo khóa.
- B. Thành công nhưng chỉ áp dụng cho các dòng chèn sau này.
- C. Lỗi 1062
Duplicate entry, vì dữ liệu hiện có đã vi phạm khóa. - D. Lỗi cú pháp, vì không thể thêm khóa duy nhất cho cột chuỗi.
Đáp án và giải thích:
- Câu 1: C. Điều kiện nhỏ hơn xóa mọi dòng có bản sao mới hơn, nên giữ dòng id lớn nhất của mỗi email: 5, 6, cộng với 4 và 7 đã duy nhất. A, B và D không khớp với bảng cặp.
- Câu 2: B. Dòng 5 có hai dòng cũ hơn cùng email nên xuất hiện ở hai cặp, nhưng một dòng chỉ bị xóa một lần. A đếm theo cặp, C và D sai số.
- Câu 3: C. Bài đã chạy lệnh này và nhận lỗi 1062. A và B tưởng MySQL tự dọn hoặc bỏ qua dữ liệu cũ, D bịa ra giới hạn.
Làm cùng mình
Mục tiêu: viết đúng câu xóa bản sao, rồi kiểm chứng nó giữ dòng cũ nhất.
Bước 1. Vẽ ra giấy
Chép bảy dòng ra giấy. Với mỗi dòng, ghi dòng cũ hơn cùng email (nếu có) và gạch chéo những dòng sẽ bị xóa. Ghi dự đoán các id còn lại.
Bước 2. Điền chỗ trống
DELETE
-- TODO 1: bí danh của bản sao cần xóa
FROM subscribers t1
JOIN subscribers t2
-- TODO 2: cùng email và t1 có id lớn hơn t2
;
Gợi ý cho chỗ trống thứ nhất: t1. Gợi ý cho chỗ trống thứ hai: mệnh đề ON t1.email = t2.email AND t1.id > t2.id.
Bước 3. Chạy và đối chiếu
Chạy lệnh rồi SELECT lại bảng.
Chạy xong bạn sẽ thấy [Đã chạy] bốn dòng còn lại có id 1, 2, 4, 7. So với danh sách bạn dự đoán ở Bước 1.
Bước 4. Vẽ lại ra giấy
Viết một câu: nếu quên bí danh sau DELETE mà viết DELETE t1, t2, điều gì xảy ra với dòng cũ nhất của mỗi nhóm.
Thử phá nó đi
Ba bẫy người mới thường gặp khi dọn dòng trùng [Đã chạy: MySQL 8.0.44]:
| Code lỗi | Hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|---|
DELETE ... WHERE id NOT IN (SELECT MIN(id) ... GROUP BY email) | Lỗi 1093 | Subquery đọc chính bảng đang xóa | Bọc thêm một lớp bảng suy ra, hoặc dùng self-join |
Dọn bằng dấu = khi cột có NULL | Các dòng NULL vẫn còn | NULL = NULL không phải là đúng, nên không có cặp nào | Dùng <=> nếu muốn coi NULL là trùng |
So sánh ann@x.com với Ann@X.com | Hai dòng bị coi là trùng | Collation mặc định không phân biệt hoa thường | Thêm COLLATE utf8mb4_bin nếu cần phân biệt |
Lỗi 1093 và cách sửa:
DELETE FROM subscribers
WHERE id NOT IN (SELECT MIN(id) FROM subscribers GROUP BY email);
ERROR 1093 (HY000): You can't specify target table 'subscribers' for update in FROM clause
DELETE FROM subscribers
WHERE id NOT IN (SELECT keep_id FROM (SELECT MIN(id) AS keep_id FROM subscribers GROUP BY email) k);
SELECT ROW_COUNT() AS deleted;
SELECT id FROM subscribers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
| 7 |
+----+
Cách sửa dùng NOT IN cho cùng kết quả như self-join ở bảng không có NULL. Nhưng với NULL hai cách khác nhau. Bảng sn có hai dòng ann@x.com viết hoa khác nhau, và hai dòng email NULL. Truy vấn nhóm cho thấy GROUP BY gom hai dòng NULL vào một nhóm:
SELECT email, COUNT(*) AS copies FROM sn GROUP BY email ORDER BY email;
+------------+--------+
| email | copies |
+------------+--------+
| NULL | 2 |
| ann@x.com | 2 |
| binh@x.com | 1 |
+------------+--------+
Nhưng self-join bằng dấu = chỉ tìm ra cặp ann, không tìm ra cặp NULL:
SELECT t1.id AS victim, t2.id AS keeper
FROM sn t1
JOIN sn t2 ON t1.email = t2.email AND t1.id > t2.id
ORDER BY t1.id;
+--------+--------+
| victim | keeper |
+--------+--------+
| 2 | 1 |
+--------+--------+
Dòng 2 bị coi là trùng dòng 1 dù viết hoa khác nhau. Muốn tính cả cặp NULL, đổi = thành toán tử so sánh an toàn với NULL:
SELECT t1.id AS victim, t2.id AS keeper
FROM sn t1
JOIN sn t2 ON t1.email <=> t2.email AND t1.id > t2.id
ORDER BY t1.id;
+--------+--------+
| victim | keeper |
+--------+--------+
| 2 | 1 |
| 5 | 4 |
+--------+--------+
Hiểu lầm quan trọng nhất của bài là tưởng GROUP BY và dấu = đối xử với NULL như nhau. Hình dưới đối chiếu mô hình sai với mô hình đúng.
Đang tải sơ đồ…
Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai, dẫn tới việc tin rằng kết quả của hai cách dọn giống nhau. Chuỗi xanh là cách MySQL làm: GROUP BY gom NULL lại, còn dấu = thì không bao giờ cho kết quả đúng với NULL.
Chuyện phân biệt hoa thường cũng có lối thoát. Thêm COLLATE utf8mb4_bin vào phép so sánh, dòng 2 không còn bị coi là bản sao của dòng 1 [Đã chạy: MySQL 8.0.44]:
SELECT COUNT(*) AS pairs
FROM sn t1
JOIN sn t2 ON t1.email = t2.email COLLATE utf8mb4_bin AND t1.id > t2.id;
+-------+
| pairs |
+-------+
| 0 |
+-------+
Số cặp là 0, vì so sánh nhị phân coi ann@x.com và Ann@X.com là khác nhau.
Phần cuối của quy trình là chặn tái phát. Thêm UNIQUE KEY khi bảng vẫn còn bản sao thì thất bại:
ALTER TABLE subscribers ADD UNIQUE KEY uq_email (email);
ERROR 1062 (23000): Duplicate entry 'ann@x.com' for key 'subscribers.uq_email'
Sau khi dọn, cùng lệnh chạy được. Từ đó, chèn thêm một bản sao bị chặn ngay [Đã chạy: MySQL 8.0.44]:
ALTER TABLE subscribers ADD UNIQUE KEY uq_email (email);
INSERT INTO subscribers (email, name) VALUES ('ann@x.com', 'again');
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+------------+------+
| id | email | name |
+----+------------+------+
| 1 | ann@x.com | Ann |
| 2 | binh@x.com | Binh |
| 4 | chi@x.com | Chi |
| 7 | dung@x.com | Dung |
+----+------------+------+
ERROR 1062 (23000): Duplicate entry 'ann@x.com' for key 'subscribers.uq_email'
Tự tay làm
Dùng lại bảng subscribers bảy dòng của bài.
Cơ bản
Bài 1. Giữ bản mới nhất. Đầu vào: bảy dòng của bài. Viết self-join DELETE giữ dòng có id lớn nhất cho mỗi email. Tiêu chí đạt: ROW_COUNT() là 3 và các id còn lại là 4, 5, 6, 7. Gợi ý: chỉ cần đổi chiều một phép so sánh.
Vận dụng
Bài 2. Dọn rồi khóa lại. Đầu vào: bảy dòng của bài. Viết trọn quy trình: xóa bản sao giữ id nhỏ nhất, thêm UNIQUE KEY uq_email, rồi thử chèn lại chi@x.com để thấy lỗi. Tiêu chí đạt: lệnh INSERT cuối báo lỗi 1062. Gợi ý: thứ tự dọn rồi mới thêm khóa.
Thử thách (tùy chọn)
Bài 3. Trùng theo hai cột. Đầu vào: bảng subs(id, email, list) với năm dòng: (1, a, news), (2, a, promo), (3, a, news), (4, b, news), (5, b, news). Hai dòng là bản sao khi cùng email và cùng list. Viết lệnh giữ id nhỏ nhất của mỗi cặp. Tiêu chí đạt: các id còn lại là 1, 2, 4. Gợi ý: thêm một vế AND vào ON.
Tự kiểm tra
Trả lời bằng lời của bạn, không nhìn lại bài.
- (Mục tiêu 1, nhớ lại) Truy vấn nào liệt kê các nhóm email có từ hai dòng trở lên?
- (Mục tiêu 2, giải thích) Vì sao điều kiện
t1.id > t2.idluôn để lại dòng nhỏ nhất của mỗi nhóm? - (Mục tiêu 2, dự đoán) Self-join cho 4 cặp trong bài, nhưng
ROW_COUNT()là 3. Vì sao? - (Mục tiêu 3, giải thích) Vì sao phải dọn bản sao trước rồi mới
ADD UNIQUE KEY? - (Mục tiêu 4, tìm lỗi) Bảng có hai dòng email
NULL, bạn chạy self-joinDELETEbằng dấu=và hai dòng vẫn còn. Giải thích và cách sửa.
Điều cần nhớ
- Xác định cột trùng trước, rồi tìm nhóm bằng
GROUP BY ... HAVING COUNT(*) > 1. - Self-join
DELETE t1 ... JOIN t2 ON cùng khóa AND t1.id > t2.idgiữ id nhỏ nhất. Đổi chiều thì giữ id lớn nhất. - Từ 8.0,
ROW_NUMBER() OVER (PARTITION BY ...)kèm điều kiện số thứ tự lớn hơn 1 là cách thứ hai. - Dọn xong mới thêm
UNIQUE KEY. Cẩn thận vớiNULL, hoa thường và lỗi 1093.
Cheat sheet:
| Muốn | Viết | Lưu ý |
|---|---|---|
| Liệt kê nhóm trùng | GROUP BY c HAVING COUNT(*) > 1 | Gom NULL thành một nhóm |
| Giữ dòng cũ nhất | DELETE t1 ... ON t1.c = t2.c AND t1.id > t2.id | Dấu = bỏ qua NULL |
| Giữ dòng mới nhất | Đổi thành t1.id < t2.id | Hoặc ORDER BY id DESC trong ROW_NUMBER |
Coi NULL là trùng | t1.c <=> t2.c | So sánh an toàn với NULL |
| Chặn tái phát | ADD UNIQUE KEY uq (c) | Chỉ thành công sau khi đã dọn |
Câu hỏi để dành cho ngày mai: nếu bảng khác có khóa ngoại trỏ tới subscribers.id, xóa các dòng trùng có làm hỏng chúng không, và MySQL có thể tự dọn các dòng con đó không?
Đào sâu
Phần này dành cho bạn tò mò.
- Cơ chế bên trong. Cách
ROW_NUMBERđánh số từng nhóm rồi chọn số lớn hơn 1. Kết quả dưới đây là số thứ tự mà phép xóa dựa vào [Đã chạy: MySQL 8.0.44]:
SELECT s.id, s.email, r.rn
FROM subscribers s
JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM subscribers) r ON r.id = s.id
ORDER BY s.id;
+----+------------+----+
| id | email | rn |
+----+------------+----+
| 1 | ann@x.com | 1 |
| 2 | binh@x.com | 1 |
| 3 | ann@x.com | 2 |
| 4 | chi@x.com | 1 |
| 5 | ann@x.com | 3 |
| 6 | binh@x.com | 2 |
| 7 | dung@x.com | 1 |
+----+------------+----+
Xóa theo cách này cho cùng kết quả với self-join:
DELETE s
FROM subscribers s
JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM subscribers) r ON r.id = s.id
WHERE r.rn > 1;
SELECT ROW_COUNT() AS deleted;
SELECT id FROM subscribers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+
| id |
+----+
| 1 |
| 2 |
| 4 |
| 7 |
+----+
- So sánh với lựa chọn khác. PostgreSQL thường dùng cột nội bộ
ctidđể xóa bản sao khi bảng không có khóa. MySQL luôn cần một cột phân biệt nhưid. Bảng không có cột nào phân biệt thì phải tạo bảng mới bằngSELECT DISTINCT. - Hiểu lầm nâng cao. Dọn bằng
DELETEchỉ là lần vá đầu tiên. Nếu ứng dụng vẫn chèn dòng trùng, mọi nỗ lực sẽ lặp lại. Hãy dùngINSERT ... ON DUPLICATE KEY UPDATEhoặc bắt lỗi 1062 ở ứng dụng. - Giới hạn của bài. Bài này chưa nói đến chuẩn hóa dữ liệu trước khi so sánh (cắt khoảng trắng, đưa về chữ thường), dòng con trỏ tới bản sao qua khóa ngoại, và dọn trên bảng cực lớn.
- Đọc thêm. MySQL 8.0 Reference Manual, mục Window Function Descriptions: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html và mục Comparison Functions and Operators: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html
Bài tiếp theo
Bài sau là ràng buộc ON DELETE CASCADE: khi dòng cha bị xóa, MySQL tự xử lý các dòng con trỏ tới nó thế nào. Mini-dự án nối tiếp: xóa một khách của TechShop mà không để lại đơn hàng mồ côi.
Lời giải và đáp án
Làm cùng mình, Bước 2. Hai chỗ trống:
TODO 1: t1
TODO 2: ON t1.email = t2.email AND t1.id > t2.id
Lệnh đầy đủ và kết quả ở mục Ví dụ đầu tiên: bốn dòng còn lại id 1, 2, 4, 7. Nếu viết DELETE t1, t2, cả dòng bản sao lẫn dòng cũ hơn khớp với nó đều bị xóa, nên dòng cũ nhất của mỗi nhóm có bản sao cũng mất.
Bài 1.
DELETE t1
FROM subscribers t1
JOIN subscribers t2 ON t1.email = t2.email AND t1.id < t2.id;
SELECT ROW_COUNT() AS deleted;
SELECT id, email FROM subscribers ORDER BY id;
+---------+
| deleted |
+---------+
| 3 |
+---------+
+----+------------+
| id | email |
+----+------------+
| 4 | chi@x.com |
| 5 | ann@x.com |
| 6 | binh@x.com |
| 7 | dung@x.com |
+----+------------+
Đổi > thành < thì dòng bị xóa là dòng có một bản sao mới hơn. Id 1, 3 (nhóm ann) và id 2 (nhóm binh) bị xóa, còn lại 4, 5, 6, 7.
Bài 2.
DELETE t1
FROM subscribers t1
JOIN subscribers t2 ON t1.email = t2.email AND t1.id > t2.id;
ALTER TABLE subscribers ADD UNIQUE KEY uq_email (email);
INSERT INTO subscribers (email, name) VALUES ('chi@x.com', 'Chi again');
ERROR 1062 (23000): Duplicate entry 'chi@x.com' for key 'subscribers.uq_email'
Dọn trước nên ADD UNIQUE KEY thành công, và INSERT lại chi@x.com bị từ chối với lỗi 1062.
Bài 3.
DELETE t1
FROM subs t1
JOIN subs t2 ON t1.email = t2.email AND t1.list = t2.list AND t1.id > t2.id;
SELECT ROW_COUNT() AS deleted;
SELECT id, email, list FROM subs ORDER BY id;
+---------+
| deleted |
+---------+
| 2 |
+---------+
+----+-------+-------+
| id | email | list |
+----+-------+-------+
| 1 | a | news |
| 2 | a | promo |
| 4 | b | news |
+----+-------+-------+
Hai dòng là bản sao khi cả email lẫn list đều giống. Dòng 3 là bản sao của dòng 1 và dòng 5 là bản sao của dòng 4. Dòng 2 cùng email a nhưng khác list nên được giữ.
Tự kiểm tra.
SELECT email, COUNT(*) FROM subscribers GROUP BY email HAVING COUNT(*) > 1.- Dòng nhỏ nhất của nhóm không có dòng nào cùng khóa mà id nhỏ hơn, nên nó không bao giờ là
t1trong một cặp. - Dòng id 5 cùng email với cả dòng 1 và dòng 3, nên xuất hiện ở hai cặp, nhưng chỉ bị xóa một lần.
- Khóa duy nhất cần dữ liệu hiện có không vi phạm. Nếu còn bản sao,
ADD UNIQUE KEYthất bại với lỗi 1062. NULL = NULLkhông đúng, nên self-join bằng=không ghép cặp nào cho các dòngNULL. Đổi sang<=>, hoặc dọn riêng nhómNULL.