How To Delete Duplicate Rows in MySQL
Trong bài viết này sẽ hướng dẫn bạn các cách để xóa dữ liệu trùng lặp trong MySQL.
Ở bài viết trước, chúng ta đã biết cách tìm ra các dữ liệu trùng lặp. Và khi đã tìm được dữ liệu trùng lặp thì chúng ta sẽ xóa chúng đi để làm sạch database.
Chuẩn bị dữ liệu
Đoạn code dưới sẽ tạo bảng contacts và thêm vào một vài dữ liệu để làm mẫu:
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','[email protected]'),
('Jean','King','[email protected]'),
('Peter','Ferguson','[email protected]'),
('Janine ','Labrune','[email protected]'),
('Jonas ','Bergulfsen','[email protected]'),
('Janine ','Labrune','[email protected]'),
('Susan','Nelson','[email protected]'),
('Zbyszek ','Piestrzeniewicz','[email protected]'),
('Roland','Keitel','[email protected]'),
('Julie','Murphy','[email protected]'),
('Kwai','Lee','[email protected]'),
('Jean','King','[email protected]'),
('Susan','Nelson','[email protected]'),
('Roland','Keitel','[email protected]');
Chú ý: bạn có thể chạy lại đoạn code trên sau khi bạn thực hiện các lệnh DELETE.
Tìm dữ liệu trùng lặp email:
SELECT
email, COUNT(email)
FROM
contacts
GROUP BY
email
HAVING
COUNT(email) > 1;
Chúng ta có 4 kết quả trùng lặp
A) Sử dụng DELETE JOIN để xóa dữ liệu trùng
Truy vấn sau sẽ xóa các dữ liệu trùng và giữ lại dữ liệu có id lớn nhất:
DELETE t1 FROM contacts t1
INNER JOIN
contacts t2
WHERE
t1.id < t2.id AND t1.email = t2.email;
Kết quả:
Query OK, 4 rows affected (0.10 sec)
Bây giờ thử tìm lại các email trùng lặp:
SELECT
email,
COUNT(email)
FROM
contacts
GROUP BY
email
HAVING
COUNT(email) > 1;
Kết quả trả về rỗng, như vậy không có dữ liệu trùng lặp email
Kiểm tra lại lần nữa:
SELECT
*
FROM
contacts;
Các rows với id 2, 4, 7, và 9 đã được xóa.
Trong trường hợp khi xóa dữ liệu trùng mà vẫn muốn giữ lại id bé nhất thì sử dụng query sau:
DELETE t1 FROM contacts t1
INNER JOIN
contacts t2
WHERE
t1.id > t2.id AND t1.email = t2.email;
Bạn có thể thực hiện lại việc tạo dữ liệu mẫu của bảng contacts và test lại truy vấn trên. Kết quả như hình dưới:
B) Sử dụng bảng trung gian để xóa dữ liệu trùng lặp.
Dưới đây là các bước mà các bạn có thể sử dụng bảng trung gian (intermediate table) để xóa dữ liệu trùng lặp:
- Tạo mới 1 bảng với cùng structure với bảng mà bạn muốn xóa dữ liệu trùng lặp (tạm gọi là bảng gốc).
- Thêm mới đơn nhất dữ liệu từ bảng gốc sang bảng trugn gian.
- Xóa bảng gốc và đổi tên bảng trung gian về tên của bảng gốc.
Các bước thực hiện như sau:
Bước 1
CREATE TABLE source_copy LIKE source;
Bước 2
INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
Bước 3
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
Ví dụ, xóa các bản ghi trùng lặp email:
-- step 1
CREATE TABLE contacts_temp
LIKE contacts;
-- step 2
INSERT INTO contacts_temp
SELECT *
FROM contacts
GROUP BY email;
-- step 3
DROP TABLE contacts;
ALTER TABLE contacts_temp
RENAME TO contacts;
C) Sử dụng hàm ROW_NUMBER() để xóa dữ liệu trùng lặp
Lưu ý hàm
ROW_NUMBER()chỉ được hỗ trợ từ MySQL 8.02 do đó hãy check lại version của MySQL trước khi sử dụng.
Lệnh sau sẽ sử dụng hàm ROW_NUMBER() để gán liên tiếp số nguyên cho mỗi row. Nếu email trùng lặp thì số row sẽ lớn hơn 1.
SELECT
id,
email,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY email) AS row_num
FROM
contacts
Lệnh sau trả về danh sách id của các rows trùng lặp:
SELECT
id
FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY email) AS row_num
FROM
contacts
) t
WHERE
row_num > 1;
Cuối cùng, có thể xóa các dữ liệu trùng bằng lệnh DELETE cùng với subquery trong mệnh đề WHERE:
DELETE FROM contacts
WHERE
id IN (
SELECT
id
FROM (
SELECT
id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY email) AS row_num
FROM
contacts
) t
WHERE row_num > 1
);
Kết quả với message:
4 row(s) affected