mysql 處理重復數據

mysql 數據表中的重復記錄，有時候我們允許重復數據的存在，但有時候我們也需要刪除這些重復的數據。

我們可以設置字段屬性防止表中出現重復數據，還可以通過sql語句過濾、刪除或者統計表中的重復數據。

1. 防止表中出現重復數據

讓我們嘗試一個范例：下表中無索引及主鍵，所以該表允許出現多條重復記錄。

create table person_tbl
(
    first_name char(20),
    last_name char(20),
    sex char(10)
);

如果你想設置表中字段 first_name，last_name 數據不能重復，你可以設置雙主鍵模式來設置數據的唯一性， 如果你設置了雙主鍵，那么那個鍵的默認值不能為 null，可設置為 not 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)
);

如果我們設置了唯一索引，那么在插入重復數據時，sql 語句將無法執行成功,并拋出錯。

insert ignore into 與 insert into 的區別就是 insert ignore into 會忽略數據庫中已經存在的數據，如果數據庫沒有數據，就插入新的數據，如果有數據的話就跳過這條數據。這樣就可以保留數據庫中已經存在數據，達到在間隙中插入數據的目的。

以下范例使用了 insert ignore into，執行后不會出錯，也不會向數據表中插入重復數據：

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)

insert ignore into 當插入數據時，在設置了記錄的唯一性后，如果插入重復數據，將不返回錯誤，只以警告形式返回。 而 replace into 如果存在 primary 或 unique 相同的記錄，則先刪除掉。再插入新記錄。

另一種設置數據的唯一性方法是添加一個 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)
);

2. 統計重復數據

以下我們將統計表中 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。

3. 過濾重復數據

如果你需要讀取不重復的數據可以在 select 語句中使用 distinct 關鍵字來過濾重復數據。

mysql> select distinct last_name, first_name
    -> from person_tbl;

你也可以使用 group by 來讀取數據表中不重復的數據：

mysql> select last_name, first_name
    -> from person_tbl
    -> group by (last_name, first_name);

4. 刪除重復數據

如果你想刪除數據表中的重復數據，你可以使用以下的sql語句：

mysql> create table tmp select last_name, first_name, sex from person_tbl  group by (last_name, first_name, sex);
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);