MySQL重復處理

表或結果集有時含有重復記錄。有時，它是允許的，但有時它被要求停止使用重復記錄。有時，需要識別重復記錄並從表中刪除它們。本章將介紹如何防止在一個表中，以及如何刪除已有的重復記錄。

防止在一個表發生重復記錄

可以使用適當表字段的PRIMARY KEY 或 UNIQUE 來防止重復記錄。讓我們來看看下面的例子：下表中沒有這樣的索引或主鍵，所以這裡允許 first_name 和last_name 記錄重復。

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

為了防止表中被創建的多個記錄具有相同的姓氏和名字的值，添加一個主鍵(PRIMARY KEY)到它的定義。 當要做這一點，也必須聲明索引列是NOT NULL，因為PRIMARY KEY不允許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忽略保持第一套重復記錄，並丟棄剩下的。REPLACE保持最後一組重復的和擦除任何較早的記錄。

另一種方法是強制唯一性是增加唯一(UNIQUE)索引，而不是一個主鍵(PRIMARY KEY)。

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的記錄重復

從查詢結果消除重記錄

可以使用SELECT語句以及DISTINCT一起在一個表中找出可用唯一記錄。

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;

從表中刪除重復記錄的一個簡單的方法就添加索引(INDEX) 或 主鍵(PRIMAY KEY)到該表。即使該表已經提供，可以使用此技術來刪除重復的記錄。

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