数据表常会有一些重复的记录，下面介绍两种删除数据表中重复记录的sql语句

一般写法

DELETE FROM tablename WHERE id NOT IN (SELECT MAX(id) FROM tablename GROUP BY col1,col2,...)

上面这种写法在MYSQL会报错 “You can’t specify target table ‘site_link’ for update in FROM clause”；

mysql 写法

DELETE FROM site_link WHERE id NOT IN ( SELECT temp.id FROM( SELECT MAX(m.id) as id FROM site_link m GROUP BY m.col1，m.col2，...) temp )

注解：
tablename:表名
col1：相同字段1
col2：相同字段2

mysql测试案例

原始表

执行语句
DELETE FROM site_link WHERE id NOT IN ( SELECT temp.id FROM( SELECT MAX(m.id) as id FROM site_link m GROUP BY m.link ) temp )

执行sql后

评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作