表结构如下:
mysql> desc tbl;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| word | varchar(255) | NO | | - | |
| state | char(1) | NO | | i | |
+-------+--------------+------+-----+---------+----------------+
删除重复记录的语句如下:
delete t1 from tbl t1 ,tbl t2 where t1.word=t2.word and t1.id>t2.id;
原理是tbl表自身与自身做笛卡尔积,如果两条记录的word字段相同,则删除id字段较大的那条记录。也就是说,对于word字段相同的记录,仅保留id字段最小的那一条,这样就达到了删除重复记录的目的^_^