MySQL - Xử lý các bản sao

Nói chung, các bảng hoặc tập hợp kết quả đôi khi chứa các bản ghi trùng lặp. Hầu hết các trường hợp nó được cho phép nhưng đôi khi nó được yêu cầu dừng các bản ghi trùng lặp. Yêu cầu xác định các bản ghi trùng lặp và loại bỏ chúng khỏi bảng. Chương này sẽ mô tả cách ngăn chặn sự xuất hiện của các bản ghi trùng lặp trong một bảng và cách loại bỏ các bản ghi trùng lặp đã tồn tại.

Ngăn trùng lặp xuất hiện trong bảng

Bạn có thể sử dụng một PRIMARY KEY hoặc một UNIQUE Lập chỉ mục trên bảng với các trường thích hợp để ngăn các bản ghi trùng lặp.

Hãy để chúng tôi lấy một ví dụ - Bảng sau không chứa chỉ mục hoặc khóa chính như vậy, vì vậy nó sẽ cho phép các bản ghi trùng lặp cho first_name và last_name.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Để ngăn nhiều bản ghi có cùng giá trị họ và tên được tạo trong bảng này, hãy thêm PRIMARY KEYtheo định nghĩa của nó. Khi bạn làm điều này, cũng cần phải khai báo các cột được lập chỉ mụcNOT NULL, vì một PRIMARY KEY không cho phép NULL giá trị -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Sự hiện diện của một chỉ mục duy nhất trong một bảng thường gây ra lỗi nếu bạn chèn một bản ghi vào bảng sao chép một bản ghi hiện có trong cột hoặc các cột xác định chỉ mục.

Sử dụng INSERT IGNORE lệnh hơn là INSERTchỉ huy. Nếu một bản ghi không trùng lặp một bản ghi hiện có, thì MySQL sẽ chèn nó như bình thường. Nếu bản ghi là một bản sao, thìIGNORE từ khóa cho MySQL biết để loại bỏ nó một cách âm thầm mà không tạo ra lỗi.

Ví dụ sau đây không xảy ra lỗi và đồng thời nó cũng sẽ không chèn các bản ghi trùng lặp.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Sử dụng REPLACEchứ không phải lệnh INSERT. Nếu bản ghi là mới, nó sẽ được chèn giống như với INSERT. Nếu là bản sao, bản ghi mới sẽ thay thế bản cũ.

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

Các lệnh INSERT IGNORE và REPLACE phải được chọn theo hành vi xử lý trùng lặp mà bạn muốn thực hiện. Lệnh INSERT IGNORE giữ tập hợp đầu tiên của các bản ghi được sao chép và loại bỏ phần còn lại. Lệnh REPLACE giữ tập hợp các bản sao cuối cùng và xóa mọi bản sao trước đó.

Một cách khác để thực thi tính duy nhất là thêm UNIQUE lập chỉ mục chứ không phải là TỪ KHÓA CHÍNH cho một bảng.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Đếm và xác định các bản sao

Sau đây là truy vấn để đếm các bản ghi trùng lặp với first_name và last_name trong một bảng.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

Truy vấn này sẽ trả về danh sách tất cả các bản ghi trùng lặp trong bảng person_tbl. Nói chung, để xác định các tập hợp giá trị bị trùng lặp, hãy làm theo các bước dưới đây.

Xác định cột nào chứa các giá trị có thể bị trùng lặp.
Liệt kê các cột đó trong danh sách lựa chọn cột, cùng với COUNT(*).
Liệt kê các cột trong GROUP BY cũng như mệnh đề.
Thêm một HAVING mệnh đề loại bỏ các giá trị duy nhất bằng cách yêu cầu số lượng nhóm lớn hơn một.

Loại bỏ trùng lặp khỏi kết quả truy vấn

Bạn có thể dùng DISTINCT cùng với câu lệnh SELECT để tìm ra các bản ghi duy nhất có sẵn trong bảng.

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Một thay thế cho lệnh DISTINCT là thêm mệnh đề GROUP BY đặt tên cho các cột bạn đang chọn. Điều này có tác dụng loại bỏ các bản sao và chỉ chọn các kết hợp giá trị duy nhất trong các cột được chỉ định.

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Loại bỏ các bản sao bằng cách sử dụng thay thế bảng

Nếu bạn có các bản ghi trùng lặp trong một bảng và bạn muốn xóa tất cả các bản ghi trùng lặp khỏi bảng đó, thì hãy làm theo quy trình dưới đây.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Một cách dễ dàng để loại bỏ các bản ghi trùng lặp khỏi bảng là thêm một CHỈ SỐ hoặc một KHÓA CHÍNH vào bảng đó. Ngay cả khi bảng này đã có sẵn, bạn có thể sử dụng kỹ thuật này để loại bỏ các bản ghi trùng lặp và bạn cũng sẽ an toàn trong tương lai.

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);