MySQL如何消除重復(fù)行的方法分析

/*  MySQL?消除重復(fù)行的一些方法  ---Chu?Minfei  ---2010-08-12?22:49:44.660  --引用轉(zhuǎn)載請注明出處:http://blog.csdn.NET/feixianxxx  */  ----------------全部字段重復(fù)------------------------  ?--1使用表替換來刪除重復(fù)項  ?create?table?test_1(id?int,value?int);  ?insert?test_1?select?1,2?union?all?select?1,2?union?all?select?2,3;  ?--建立一個和源表結(jié)構(gòu)一樣的空的臨時表  ?create?table?tmp?like?test_1;  ?--向臨時表插入不重復(fù)的記錄  ?insert?tmp?select?distinct?*?from?test_1;  ?--刪除原表  ?drop?table?test_1;  ?--更改臨時表名為目標表  ?rename?table?tmp?to?test_1;  ?--顯示  ?mysql&gt;?select?*?from?test_1;  +------+-------+  |?id??|?value?|  +------+-------+  |??1?|???2?|  |??2?|???3?|  +------+-------+  ?--2.添加auto_increment屬性列(這個方法只能用于MyISAM或者BDB引擎的表)  ?create?table?test_1(id?int,value?int)?engine=MyISAM;  ?insert?test_1?select?1,2?union?all?select?1,2?union?all?select?2,3;  ?alter?table?test_1?add?id2?int?not?null?auto_increment,  ?add?primary?key(id,value,id2);  ?select?*?from?test_1;  +----+-------+-----+  |?id?|?value?|?id2?|  +----+-------+-----+  |?1?|???2?|??1?|  |?1?|???2?|??2?|  |?2?|???3?|??1?|  +----+-------+-----+  ??delete?from?test_1?where?id21;  ??alter?table?test_1?drop?id2;  ??select?*?from?test_1;  ??+----+-------+  |?id?|?value?|  +----+-------+  |?1?|???2?|  |?2?|???3?|  +----+-------+  -------------------部分字段重復(fù)---------------------  --1.加索引的方式  ?create?table?test_2(id?int,value?int);  ?insert?test_2?select?1,2?union?all?select?1,3?union?all?select?2,3;  ?Alter?IGNORE?table?test_2?add?primary?key(id);  ?select?*?from?test_2;  ?+----+-------+  |?id?|?value?|  +----+-------+  |?1?|???2?|  |?2?|???3?|  +----+-------+  ?我們可以看到?1?3?這條記錄消失了?  ?我們這里也可以使用Unique約束?因為有可能列中有NULL值，但是這里NULL就可以多個了..  ?--2.聯(lián)合表刪除  ?create?table?test_2(id?int,value?int);  ?insert?test_2?select?1,2?union?all?select?1,3?union?all?select?2,3;  ?delete?A?from?test_2?a?join?(select?MAX(value)?as?v?,ID?from?test_2?group?by?id)?b  ?on?a.id=b.id?and?a.valueb.v;  ?select?*?from?test_2;  ?+------+-------+  |?id??|?value?|  +------+-------+  |??1?|???3?|  |??2?|???3?|  +------+-------+  --3.使用Increment_auto也可以就是上面全部字段去重的第二個方法  --4.容易錯誤的方法  --有些朋友可能會想到子查詢的方法，我們來試驗一下  ?create?table?test_2(id?int,value?int);  ?insert?test_2?select?1,2?union?all?select?1,3?union?all?select?2,3;  ?delete?a?from?test_2?a?where?exists(select?*?from?test_2?where?a.id=id?and?a.value<value></value>