MySQL 查询并删除多条重复记录

重复记录的出现,通常是用于程序后端验证不严谨导致出现的垃圾数据,想要查询并删除,可通过以下实现方式

查询重复记录

查询指定字段相同且总数大于一的数据

  1. 先指明可能重复的字段,可以是多个
  2. 再根据分组查询结果,通过 HAVING 进行判断
1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	msan.memberSurvey_id, 
	msan.question_id, 
	msan.option_id 
FROM 
	pt_member_survey_answer msan 
GROUP BY 
	msan.memberSurvey_id, 
	msan.question_id, 
	msan.option_id 
HAVING 
	COUNT(*) > 1

根据上述查询结果将重复数据全部查出

  1. 上述查询会得到组合字段中出现重复的情况
  2. 根据该情况即可查询出涉及到的具体数据条数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT
	msa.*
FROM	
	pt_member_survey_answer msa
WHERE
	(msa.memberSurvey_id, msa.question_id, msa.option_id) IN (
		SELECT 
			msan.memberSurvey_id, 
			msan.question_id, 
			msan.option_id 
		FROM 
			pt_member_survey_answer msan 
		GROUP BY 
			msan.memberSurvey_id, 
			msan.question_id, 
			msan.option_id 
		HAVING 
			COUNT(*) > 1
	)
ORDER BY
	msa.memberSurvey_id ASC,
	msa.question_id ASC,
	msa.option_id ASC

删除重复记录

查询涉及到重复情况的数据id

1
2
3
4
5
6
7
8
9
SELECT 
	msan.id AS 'id' 
FROM 
	pt_member_survey_answer msan 
GROUP BY 
	msan.memberSurvey_id, 
	msan.question_id, 
	msan.option_id 
HAVING COUNT(*) > 1

删除查询到的数据id

  1. 注意此处对上述查询结果直接获取的话,会抛出 You can't specify target table for update in FROM clause 异常
  2. 所以需要对查询结果进行一次 SELECT 包装,才可以
  3. 删除查询结果只能查询出可能重复的情况,如果同一个情况存在两条或更多的重复数据,一次删除无法完全清空,可能需要执行多次删除语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELETE FROM
	pt_member_survey_answer
WHERE
	id IN (
		SELECT 
			rs.id 
		FROM (
			SELECT 
				msan.id AS 'id' 
			FROM 
				pt_member_survey_answer msan 
			GROUP BY 
				msan.memberSurvey_id, 
				msan.question_id, 
				msan.option_id 
			HAVING COUNT(*) > 1
		) rs
	)