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

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. Đôi khi, nó được phép nhưng đôi khi nó được yêu cầu để ngăn chặn các bản ghi trùng lặp. Đôi khi, 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 các bản ghi trùng lặp xảy ra trong bảng và cách loại bỏ các bản ghi đã trùng lặp hiện có.

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 là UNIQUELậ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 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 việc tạo nhiều bản ghi có cùng giá trị họ và tên trong bảng này, hãy thêm KHÓA CHÍNH vào đị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ục là KHÔNG ĐẦY ĐỦ, vì KHÓA CHÍNH không cho phép các giá trị NULL -

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 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 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 hơn là INSERT. Nếu một bản ghi không sao chép một bản ghi hiện có, MySQLi sẽ chèn nó như bình thường. Nếu bản ghi là bản sao, từ khóa IGNORE sẽ cho MySQLi biết để loại bỏ nó một cách âm thầm mà không tạo ra lỗi.

Ví dụ sau không xảy ra lỗi và đồng thời nó 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 CHÈN. Nếu bản ghi là mới, nó đượ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)

CHÈN BỎ QUA và THAY THẾ phải được chọn theo hành vi xử lý trùng lặp mà bạn muốn thực hiện. INSERT IGNORE giữ phần đầu tiên của một tập hợp các bản ghi bị trùng lặp và loại bỏ phần còn lại. REPLACE giữ phần cuối cùng của một tập hợp các bản sao và xóa bất kỳ bản sao nào trước đó.

Một cách khác để thực thi tính duy nhất là thêm chỉ mục DUY NHẤT thay vì TỪ KHÓA CHÍNH vào 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 bộ giá trị bị trùng lặp, hãy làm như sau:

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 chọn cột, cùng với COUNT (*).
Liệt kê các cột trong mệnh đề GROUP BY.
Thêm mệnh đề HAVING để 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 một bảng.

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

Một giải pháp thay thế cho 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ì đây là quy trình:

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 phím INDEX hoặc PRIMAY 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);