Trong bài đăng này, tôi đang chia sẻ trình diễn về cách xóa tất cả các hàng ngoại trừ một hàng trong MySQL.
Trước đó vài ngày, tôi đang làm việc trong một trong các báo cáo sản xuất của chúng tôi và nhận thấy rằng có rất nhiều bản ghi trùng lặp.
Bảng này có hơn 2,00,00,000 bản ghi và tôi đã xóa bản sao dựa trên hai cột varchar và được yêu cầu lưu trữ một bản ghi cho mỗi nhóm.
Tôi đã chuẩn bị một bản demo với một giải pháp khác vì tôi cũng phải quan tâm đến hiệu suất.
Các giải pháp là:
Sử dụng Tự - Tham gia để kiểm tra mọi hàng tiếp theo với hàng hiện tại và tạo cờ xóa cho các hàng trùng lặp.
Hoán đổi bảng - sao chép các bản ghi duy nhất trong bảng tạm thời và khôi phục trở lại bảng ban đầu sau khi xóa các bản ghi trùng lặp.
Nhưng điều này không hiệu quả với tôi vì giải pháp này yêu cầu một lượng lớn hoạt động DML sẽ làm giảm hiệu suất.
After some research, I found that MySQL has a feature called ALTER IGNORE.
Sử dụng ALTER IGNORE, bạn có thể áp dụng khóa duy nhất trên các cột cần thiết để xóa các bản sao. Khi bạn thực thi lệnh DDL này, nó sẽ tạo ra một ràng buộc khóa duy nhất và xóa tất cả các bản ghi trùng lặp khỏi cơ sở dữ liệu.
Đây là một lựa chọn tuyệt vời trong MySQL và cũng phù hợp với tôi.
Hãy để tôi chứng minh điều này:
Đầu tiên, tạo bảng demo và dữ liệu cho kiểu dữ liệu varchar:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE DATABASE Employee; CREATE TABLE Employee.tbl_DuplicateEmployee_VarcharDatatype ( EmpID INTEGER ,EmpName VARCHAR(250) ); INSERT INTO Employee.tbl_DuplicateEmployee_VarcharDatatype VALUES (1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR'); |
ĐẶT hoạt động của bảng ALTER cũ:
1 | SET SESSION old_alter_table=1; |
Thực thi ALTER BỎ QUA:
1 2 3 4 | ALTER IGNORE TABLE Employee.tbl_DuplicateEmployee_VarcharDatatype ADD UNIQUE INDEX idx_tbl_DuplicateEmployee_VarcharDatatype (EmpID,EmpName); |
Tìm kết quả:
1 2 | SELECT *FROM Employee.tbl_DuplicateEmployee_VarcharDatatype; |
Như bạn có thể thấy trong kết quả ở trên, tất cả các bản ghi trùng lặp đã bị xóa.
Nếu cột của bạn có kiểu dữ liệu BLOB, bạn không thể chèn trực tiếp bất kỳ ràng buộc khóa duy nhất nào vào đó.
Nếu bạn có cột kiểu dữ liệu BLOB như TEXT, vì vậy tạm thời, bạn phải tạo một cột MD5 và sử dụng cột MD5 này trong ALTER IGNORE.
Dưới đây là minh họa để xóa các bản ghi trùng lặp BLOB.
Đầu tiên, tạo một bảng demo và dữ liệu cho kiểu dữ liệu TEXT:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE Employee.tbl_DuplicateEmployee_BLOBDataType ( EmpID INTEGER ,EmpName TEXT ); INSERT INTO Employee.tbl_DuplicateEmployee_BLOBDataType VALUES (1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR') ,(1,'ABC'),(2,'XYZ'),(3,'PQR'); |
Tạo cột MD5 cho EmpID (Số nguyên) + EmpName (Văn bản):
1 2 3 4 | ALTER TABLE Employee.tbl_DuplicateEmployee_BLOBDataType ADD MD5Column VARCHAR(50); UPDATE Employee.tbl_DuplicateEmployee_BLOBDataType SET MD5Column = MD5(CONCAT(EmpID,EmpName)); |
Thực thi ALTER BỎ QUA:
1 | ALTER IGNORE TABLE Employee.tbl_DuplicateEmployee_BLOBDataType ADD UNIQUE INDEX idx_tbl_DuplicateEmployee_BLOBDataType(MD5Column); |
Bây giờ kiểm tra kết quả:
1 2 | SELECT *FROM Employee.tbl_DuplicateEmployee_BLOBDataType; |
Xóa cột MD5:
1 | ALTER TABLE Employee.tbl_DuplicateEmployee_BLOBDataType DROP COLUMN MD5Column; |