mysql删除重复数据只保留一条_mysql中删除重复记录，只保留一条

表结构如下：

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;

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 就是不想重复的字段

注意：这个方法创建的新表后面会多一个字段，删除即可