MySQLi - 處理重複資料

表或結果集有時包含重複記錄。有時，這是允許的，但有時需要阻止重複記錄。有時，需要識別重複記錄並將其從表中刪除。本章將介紹如何防止表中出現重複記錄以及如何刪除已存在的重複記錄。

防止表中出現重複記錄

您可以使用表中具有適當欄位的主鍵或唯一索引來阻止重複記錄。讓我們舉一個例子：下表不包含此類索引或主鍵，因此它將允許 first_name 和 last_name 的重複記錄。

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

為了防止在此表中建立具有相同 first 和 last name 值的多個記錄，請將其定義中新增一個主鍵。執行此操作時，還需要將索引列宣告為 NOT NULL，因為主鍵不允許 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)
);

表中存在唯一索引通常會導致發生錯誤，如果您將記錄插入到表中，該記錄會複製定義索引的列或列中的現有記錄。

使用INSERT IGNORE而不是INSERT。如果記錄不重複現有記錄，MySQL 會照常插入它。如果記錄是重複的，IGNORE 關鍵字會告訴 MySQL 靜默地丟棄它，而不會生成錯誤。

以下示例不會出錯，同時也不會插入重複記錄。

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)

使用REPLACE而不是INSERT。如果記錄是新的，則會像使用 INSERT 一樣插入它。如果它是重複的，則新記錄會替換舊記錄 -

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)

應根據您想要執行的重複處理行為選擇 INSERT IGNORE 和 REPLACE。INSERT IGNORE 保留一組重複記錄中的第一個，並丟棄其餘記錄。REPLACE 保留一組重複項中的最後一個，並刪除任何較早的重複項。

另一種強制唯一性的方法是向表新增唯一索引而不是主鍵。

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

計數和識別重複項

以下是計算表中 first_name 和 last_name 重複記錄的查詢。

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

此查詢將返回 person_tbl 表中所有重複記錄的列表。通常，要識別重複的值集，請執行以下操作 -

確定哪些列包含可能重複的值。
在列選擇列表中列出這些列，以及 COUNT(*)。
也在 GROUP BY 子句中列出這些列。
新增一個 HAVING 子句，透過要求組計數大於 1 來消除唯一值。

從查詢結果中消除重複項

您可以將DISTINCT與 SELECT 語句一起使用以找出表中可用的唯一記錄。

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

DISTINCT 的替代方法是新增一個 GROUP BY 子句，該子句命名您正在選擇的列。這具有刪除重複項並僅選擇指定列中唯一值組合的效果 -

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

使用表替換刪除重複項

如果表中存在重複記錄，並且您想從該表中刪除所有重複記錄，則以下是步驟 -

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;

刪除表中重複記錄的一種簡單方法是向該表新增索引或主鍵。即使此表已經可用，您也可以使用此技術刪除重複記錄，並且將來也將安全無虞。

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

列印頁面