MySQL處理重復 防止重復數據實例

本文章來給大家介紹一下關於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或UNIQUE索引的表上相應的字段防止重復的記錄。讓我們舉一個例子，下面的表中不包含這樣的索引或主鍵，所以它會允許重復first_name和last_name記錄

 代碼如下 復制代碼 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)

使用替換而不是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)

使用REPLACE而不是INSERT。如果記錄是新的它INSERT插入。如果它是一個重復的，新的記錄將取代舊的：

強制唯一性的另一種方法是添加一個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。

消除重復查詢結果：
可以使用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;

從一個表中刪除重復記錄的一個簡單的方法是添加KEY，表索引或PRIMAY。如果該表已經是可用的，那麼使用此方法刪除重復的記錄。

 代碼如下 復制代碼

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

下面總結一下mysql刪除重復記錄的一些方法

我最常用的方法是

 

 代碼如下 復制代碼

//刪除id重復的數據，適合id是手工主鍵
delete person as a from person as a,
(
    select *,min(id) from person group by id having count(1) > 1
) as b
where a.id = b.id

//查找重復的，並且除掉最小的那個

 
delete tb_person as a from tb_person as a,
(
select *,min(id) from tb_person  group by name having count(1) > 1
) as b
 where a.name = b.name and a.id > b.id;

 

好了下面再總結一些

1. 查詢需要刪除的記錄，會保留一條記錄。

 

 代碼如下 復制代碼 select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where  a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid
 

2. 刪除重復記錄，只保留一條記錄。注意，subject,RECEIVER 要索引，否則會很慢的。

 代碼如下 復制代碼  
delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as  bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;

 

3. 查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷

 代碼如下 復制代碼  
select * from people
where peopleId in (select  peopleId  from  people  group  by  peopleId  having  count(peopleId) > 1)

 

4. 刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄

 代碼如下 復制代碼

 
delete from people
where peopleId  in (select  peopleId  from people  group  by  peopleId   having  count(peopleId) > 1)
and rowid not in (select min(rowid) from  people  group by peopleId  having count(peopleId )>1)
 

5.刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄

 代碼如下 復制代碼

 
delete from vitae a
where (a.peopleId,a.seq) in  (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

更多詳細內容請查看：http://www.111cn.net/database/mysql/47531.htm