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

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

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

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

為了防止表中出現(xiàn)同樣姓名的值，為其添加一個(gè) PRIMARY KEY。同時(shí)要注意將索引列聲明為 NOT NULL，這是因?yàn)?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)
);

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

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

下面這個(gè)范例不會(huì)產(chǎn)生任何錯(cuò)誤，不會(huì)插入會(huì)產(chǎn)生重復(fù)的記錄。

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。如果記錄是一個(gè)新記錄，使用 INSERT 就可以了。如果是一個(gè)重復(fù)記錄，新的記錄將會(huì)替換舊有記錄。

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)

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

強(qiáng)制唯一性的另一種辦法是為表添加 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)
);

確認(rèn)重復(fù)記錄，并計(jì)算重復(fù)記錄數(shù)

下面是計(jì)算表中姓名記錄重復(fù)的查詢：

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

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

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

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

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

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

另一種辦法是添加 GROUP BY 子句，命名選擇的列。消除重復(fù)記錄并只選擇指定列中的唯一值組合。

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

使用表替換去除重復(fù)記錄

下面這種技巧也可以消除表中存在的所有重復(fù)記錄。

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)存在，你也可以利用這種技巧消除重復(fù)記錄，這種做法將來也依然保險(xiǎn)。

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