MySQL处理重复 防止重复数据实例

有时表或结果集包含重复的记录,有时它是允许的,但有时它需要停止重复的记录,有时它需要识别重复的记录从表中删除,本章将介绍如何防止发生在一个表中重复的记录如何删除已经存在的重复记录.

防止重复表中的发生:可以使用PRIMARY KEY或UNIQUE索引的表上相应的字段来防止重复的记录,让我们举一个例子,下面的表中不包含这样的索引或主键,所以它会允许记录first_name和last_name重复,代码如下:

1. CREATETABLEperson_tbl
2. (
3. first_nameCHAR(20),
4. last_nameCHAR(20),
5. sexCHAR(10)
6. );

可以使用PRIMARY KEY或UNIQUE索引的表上相应的字段防止重复的记录,让我们举一个例子,下面的表中不包含这样的索引或主键,所以它会允许重复first_name和last_name记录,代码如下:

1. CREATETABLEperson_tbl
2. (
3. first_nameCHAR(20)NOTNULL,
4. last_nameCHAR(20)NOTNULL,
5. sexCHAR(10)
6. PRIMARYKEY(last_name,first_name)
7. );

表中的唯一索引的存在通常会导致错误的发生,如果表中插入一条记录,重复定义索引的列或列中的现有记录.

使用INSERT IGNORE而不是INSERT,如果记录不重复现有的记录,MySQL将插入它像往常一样,如果记录是重复IGNORE关键字告诉MySQL静静地抛弃它,而不会产生错误.

下面的示例中没有错误,同时也不会插入重复的记录,代码如下:

1. mysql>INSERTIGNOREINTOperson_tbl(last_name,first_name)
2. ->VALUES('Jay','Thomas');
3. QueryOK,1rowaffected(0.00sec)
4. mysql>INSERTIGNOREINTOperson_tbl(last_name,first_name)
5. ->VALUES('Jay','Thomas');
6. QueryOK,0rowsaffected(0.00sec)

使用替换而不是INSERT,如果记录是新的它INSERT插入,如果它是一个重复的,新的记录将取代旧的,代码如下:

1. mysql>REPLACEINTOperson_tbl(last_name,first_name)
2. ->VALUES('Ajay','Kumar');
3. QueryOK,1rowaffected(0.00sec)
4. mysql>REPLACEINTOperson_tbl(last_name,first_name)
5. ->VALUES('Ajay','Kumar');
6. QueryOK,2rowsaffected(0.00sec)

使用REPLACE而不是INSERT,如果记录是新的它INSERT插入,如果它是一个重复的,新的记录将取代旧的,强制唯一性的另一种方法是添加一个UNIQUE索引,而不是一个PRIMARY KEY表,代码如下:

1. CREATETABLEperson_tbl
2. (
3. first_nameCHAR(20)NOTNULL,
4. last_nameCHAR(20)NOTNULL,
5. sexCHAR(10)
6. UNIQUE(last_name,first_name)
7. );

计算和确定重复,以下是查询数first_name和last_name表中的重复记录,代码如下:

1. mysql>SELECTCOUNT(*)asrepetitions,last_name,first_name
2. ->FROMperson_tbl
3. ->GROUPBYlast_name,first_name
4. ->HAVINGrepetitions>1;

这个查询将返回一个列表的所有person_tbl表中重复的记录,在一般情况下,识别重复的值,请执行以下操作.

确定哪一列包含的值可能会重复,在列选择列表中随着COUNT（*）列出的那些列,以及在GROUP BY子句中列出的列,新增的HAVING子句消除了独特的值要求的组数大于1.

消除重复查询结果:可以使用DISTINCT与SELECT语句一起找出表中唯一的纪录,代码如下:

1. mysql>SELECTDISTINCTlast_name,first_name
2. ->FROMperson_tbl
3. ->ORDERBYlast_name;

DISTINCT是添加一个GROUP BY子句中命名的列,选择的另一种方法,这具有除去重复和只选择的独特的组合中的指定的列的值的效果,代码如下:

1. mysql>SELECTlast_name,first_name
2. ->FROMperson_tbl
3. ->GROUPBY(last_name,first_name);

删除重复使用表更换:如果在一个表中有重复的记录,想从该表中删除所有的重复记录,看看下面程序的例子,代码如下:

1. mysql>CREATETABLEtmpSELECTlast_name,first_name,sex
2. ->FROMperson_tbl;
3. ->GROUPBY(last_name,first_name);
4. mysql>DROPTABLEperson_tbl;
5. mysql>ALTERTABLEtmpRENAMETOperson_tbl;

从一个表中删除重复记录的一个简单的方法是添加KEY,表索引或PRIMAY,如果该表已经是可用的,那么使用此方法删除重复的记录,代码如下:

1. mysql>ALTERIGNORETABLEperson_tbl
2. ->ADDPRIMARYKEY(last_name,first_name);

下面总结一下mysql删除重复记录的一些方法,我最常用的方法是,代码如下:

1. //删除id重复的数据，适合id是手工主键
2. deletepersonasafrompersonasa,
3. (
4. select*,min(id)frompersongroupbyidhavingcount(1)>1
5. )asb
6. wherea.id=b.id
8. //查找重复的，并且除掉最小的那个
9. //phpfensi.com
10. deletetb_personasafromtb_personasa,
11. (
12. select*,min(id)fromtb_persongroupbynamehavingcount(1)>1
13. )asb
14. wherea.name=b.nameanda.id>b.id;

好了下面再总结一些.

1.查询需要删除的记录,会保留一条记录,代码如下:

1. selecta.id,a.subject,a.RECEIVERfromtest1aleftjoin(selectc.subject,c.RECEIVER,max(c.id)asbidfromtest1cwherestatus=0GROUPBYRECEIVER,SUBJECThavingcount(1)>1)bona.id<b.bidwherea.subject=b.subjectanda.RECEIVER=b.RECEIVERanda.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 peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

4.删除表中多余的重复记录,重复记录是根据单个字段（peopleId）来判断,只留有rowid最小的记录,代码如下:

1. deletefrompeople
2. wherepeopleIdin(selectpeopleIdfrompeoplegroupbypeopleIdhavingcount(peopleId)>1)
3. androwidnotin(selectmin(rowid)frompeoplegroupbypeopleIdhavingcount(peopleId)>1)

5.删除表中多余的重复记录,多个字段,只留有rowid最小的记录,代码如下:

1. deletefromvitaea
2. where(a.peopleId,a.seq)in(selectpeopleId,seqfromvitaegroupbypeopleId,seqhavingcount(*)>1)
3. androwidnotin(selectmin(rowid)fromvitaegroupbypeopleId,seqhavingcount(*)>1)