详解mysql删除重复数据

语句

delete from
    t_sys_role_menu 
where id in(
	SELECT
		id
		from(
			SELECT
			id
			from
					t_sys_role_menu
			WHERE
					(companyid,menuid,roleid) IN (
							SELECT
									companyid,menuid,roleid
							FROM
									t_sys_role_menu
							GROUP BY
								 companyid,menuid,roleid
							HAVING
									count(*) > 1
					)
			AND id NOT IN (
					SELECT
							min(id)
					FROM
							t_sys_role_menu 
					GROUP BY
							companyid,menuid,roleid
					HAVING
							count(*) > 1
		)
	) AS tmpresult
)

语句分析

模拟测试

CREATE TABLE `t_sys_role_menu` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `companyid` int(11) DEFAULT NULL,
  `menuid` int(11) DEFAULT NULL,
  `roleid` int(11) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

添加数据

其中id为1、4、6、7和2、5都出现了companyid、menuid、roleid字段重复

解析

首先执行3，查询t_sys_role_menu中以companyid、menuid、roleid分组并且结果中这三个值相同数据条数大于1的数据（也就是找到这三个地段中出现多次的数据不包含1次的），然后在通过后面的查询这些重复值中最小的ID（也就是在这些重复值中去除一个最小的ID）

SELECT
	companyid,
	menuid,
	roleid
FROM
	t_sys_role_menu
GROUP BY
	companyid,
	menuid,
	roleid
HAVING
	count(*) > 1

输出

SELECT
		min(id)
FROM
		t_sys_role_menu 
GROUP BY
		companyid,menuid,roleid
HAVING
		count(*) > 1

输出

执行行2，还是查询表t_sys_role_menu通过companyid、menuid、roleid三个字段匹配3模块查询后的结果，并且让ID不等于这个最小的值。

SELECT
			id
			from
					t_sys_role_menu
			WHERE
					(companyid,menuid,roleid) IN (
							SELECT
									companyid,menuid,roleid
							FROM
									t_sys_role_menu
							GROUP BY
								 companyid,menuid,roleid
							HAVING
									count(*) > 1
					)
			AND id NOT IN (
					SELECT
							min(id)
					FROM
							t_sys_role_menu 
					GROUP BY
							companyid,menuid,roleid
					HAVING
							count(*) > 1
		)

输出

执行1，通过执2模块中的语句可以过滤出重复数据并且剔除重复数据中最小的ID，也就是执行完2语句后返回的是表中companyid、menuid、roleid三个字段出现重复的并且id都大于最小的ID
最后执行语句其实为

delete from t_sys_role_menu 
where id in(4,5,6,7)

1模块中的查询看似多余，其实是mysql必须要求的语法，因为如果不加词句如下：

delete from t_sys_role_menu where id in(
SELECT
	id
	from
			t_sys_role_menu
	WHERE
			(companyid,menuid,roleid) IN (
					SELECT
							companyid,menuid,roleid
					FROM
							t_sys_role_menu
					GROUP BY
						 companyid,menuid,roleid
					HAVING
							count(*) > 1
			)
	AND id NOT IN (
			SELECT
					min(id)
			FROM
					t_sys_role_menu 
			GROUP BY
					companyid,menuid,roleid
			HAVING
					count(*) > 1
		)
)

这样执行会报错，原因是因为mysql不允许对正在打开作为条件的表进行修改。
所以需要在条件查询完后将结果存入一个临时数据表中（在外层条件select语句）

delete from t_sys_role_menu where id in(
SELECT id from(
	SELECT
				id
				from
						t_sys_role_menu
				WHERE
						(companyid,menuid,roleid) IN (
								SELECT
										companyid,menuid,roleid
								FROM
										t_sys_role_menu
								GROUP BY
									 companyid,menuid,roleid
								HAVING
										count(*) > 1
						)
				AND id NOT IN (
						SELECT
								min(id)
						FROM
								t_sys_role_menu 
						GROUP BY
								companyid,menuid,roleid
						HAVING
								count(*) > 1
			)
	) temptb
)

输出

受影响的行: 4
时间: 0.006s

查看表数据