Mysql删除重复数据

直接上代码

DELETE FROM table
WHERE repeatColumn IN (
		SELECT repeatColumn FROM
			(SELECT id,repeatColumn
				FROM table
				GROUP BY repeatColumn
				HAVING count(repeatColumn) > 1
			) b
)AND id NOT IN (
	SELECT id FROM
		(
			SELECT min(id) as id,count(repeatColumn)
			FROM table
			GROUP BY repeatColumn
			HAVING count(repeatColumn) > 1
		) c
);

解释一下：

table：你的表
id：表中主键
repeatColumn：重复列

1.

repeatColumn IN (
		SELECT repeatColumn FROM
			(SELECT id,repeatColumn
				FROM table
				GROUP BY repeatColumn
				HAVING count(repeatColumn) > 1
			) b
)

这里需要查到的是所有重复的值

2.

AND id NOT IN (
	SELECT id FROM
		(
			SELECT min(id) as id,count(repeatColumn)
			FROM table
			GROUP BY repeatColumn
			HAVING count(repeatColumn) > 1
		) c
)

这里查到的是需要保留的最小（最旧）id，当然你也可以保留最大（最新）id

3.
整个逻辑就是：
删掉所有重复值的行，但是保留最小id的行

DELETE FROM table
WHERE 重复列中 有重复的值 AND id NOT IN 最小id们;

另外，mysql中查询一个字段具体是属于哪一个数据库的那一张表用这条语句就能查询出来

#其中 table_schema 是所在库, table_name 是所在表
select table_schema,table_name from information_schema.columns where column_name = '字段名'