表或結(jié)果集有時會包含重復記錄。這種情況一般來說是允許出現(xiàn)的，但有時卻需要終止這些重復記錄。在某些情況下，需要找出重復記錄并將其刪除。下面就來介紹一下如何防止表中出現(xiàn)重復記錄，如何刪除已有的重復記錄。

防止表中出現(xiàn)重復記錄

可以在表中正確的字段內(nèi)使用 PRIMARY KEY 或 UNIQUE 索引來終止重復記錄。比如下面這張表，由于沒有這樣的索引或主鍵，因此 first_name與last_name 就被重復記錄了下來。

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

為了防止表中出現(xiàn)同樣姓名的值，為其添加一個 PRIMARY KEY。同時要注意將索引列聲明為 NOT NULL，這是因為 PRIMARY KEY 不允許出現(xiàn)空值。

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)
);

表中的唯一索引通常會造成錯誤，如果往表中插入一個記錄，復制了定義該索引的一個列（或多個列）中的一個已存記錄，問題就會產(chǎn)生。

不要使用 INSERT ，使用 INSERT IGNORE。如果一個記錄沒有復制一個已存在的記錄，MySQL 就會將它照常插入。如果該記錄與現(xiàn)存的某個記錄重復，IGNORE 關鍵字就會讓 MySQL 默默地將其摒棄，不會產(chǎn)生任何錯誤。

下面這個范例不會產(chǎn)生任何錯誤，不會插入會產(chǎn)生重復的記錄。

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)

應該根據(jù)想要達到的重復處理行為來選擇INSERT IGNORE 和 REPLACE。INSERT IGNORE 會保存重復記錄的第一個，拋棄其余的記錄；REPLACE 則正好相反，保存最后一個記錄，去掉在其之前的所有記錄。

強制唯一性的另一種辦法是為表添加 UNIQUE 索引而不是主鍵。

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

確認重復記錄，并計算重復記錄數(shù)

下面是計算表中姓名記錄重復的查詢：

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

該查詢返回表 person_tbl 中所有的重復記錄。一般來說，要想確認重復記錄，需要采取以下步驟：

• 確定可能產(chǎn)生重復記錄的列。
• 在列選擇列表中顯示所有列，利用 COUNT(*) 。
• 利用 GROUP BY 子句列出列。
• 加入 HAVING 子句排除唯一值。需要讓組計數(shù)大于1。

從查詢結(jié)果中消除重復記錄

使用DISTINCT 和 SELECT 語句來查找表中的重復記錄。

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

另一種辦法是添加 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 或 PRIMARY KEY 。即使該表已經(jīng)存在，你也可以利用這種技巧消除重復記錄，這種做法將來也依然保險。

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