from http://hi.baidu.com/hexie007/blog/item/24946ea999d2e1f01f17a2b6.html
表结构如下：
mysql> desc test1; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | ID | int(10) unsigned | NO | PRI | NULL | auto_increment | | SENDERNAME | varchar(32) | YES | | NULL | | | RECEIVERNAME | varchar(64) | YES | | NULL | | | SENDER | varchar(64) | NO | | NULL | | | RECEIVER | varchar(64) | NO | | NULL | | | SUBJECT | varchar(512) | NO | | NULL | | | CONTENT | text | NO | | NULL | | | PRIORITY | int(11) | NO | MUL | NULL | | | STATUS | int(11) | NO | MUL | NULL | | | CREATETIME | datetime | NO | | NULL | | | SENDTIME | datetime | YES | | NULL | | +--------------+------------------+------+-----+---------+----------------+
subject和RECEIVER 需要做uniq key,但设计时未做，后面的数据就有很多重复的记录。
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;
一个比较巧的方法 from http://www.php18.com/archives/Mysql_3012.html
Mysql删除重复的记录（只保留一条）
原理很简单：
把不重复的记录复制到一个新table, 之后删除旧table, 再把新table改回旧table的名不就行了？
create table new_table_name select *, count(distinct title_name) from old_table_name group by title_name
new_table_name就是新表名
old_table_name 为原表名
title_name 就是不想重复的字段
注意：这个方法创建的新表后面会多一个字段，删除即可
0 条评论。