用例执行每次产生一条record日志信息,累积下来执行的日志信息海量,需要定期清理日志信息仅剩下最新的一条即可
数据库表结构简化版
id | in_id | case_id | info |
---|---|---|---|
即删除重复记录,保存Id最大的一条
DELETE FROM
logrecord
WHERE id in
(SELECT id FROM logrecord GROUP BY in_id, case_id HAVING COUNT( * ) >1))
AND id not in
(select max(id) as id from logrecord GROUP BY in_id, case_id having count(* )>1))
直接报错,错误信息:
Error Code: 1093. You can't specify target table 'logrecord' for update in FROM clause 0.015 sec
啥原因呢?
不能对进行查询操作的表进行update或delete等操作,也就说我们的where条件中进行了子查询,并且子查询也是针对需要进行update操作的表的,mysql不支持这种查询修改的方式
怎么解决?
通过”绕”的方式进行实现,通过一个子查询的方式获取到id,而不是直接进行获取
DELETE FROM
logrecord
WHERE id in
(SELECT id FROM
(SELECT id FROM logrecord GROUP BY in_id, case_id HAVING COUNT( * ) >1) as a)
AND id not in
(SELECT id FROM
(select max(`id`) as id from logrecord GROUP BY in_id, case_id having count(* )>1) as m)
执行没有报错,但是出现另外一个问题,仅仅是删除一条数据,而并不是一开始需求的仅剩下最新的一条数据
这次又是啥原因呢?
看到竟然有人说到,“每次只能删除一条记录哦,如果有2条以上重复记录,执行多次就行了!“,这不是瞎扯么!!!
再次读一下我们刚刚执行的sql语句,第一个条件中获取的仅是某个id,这样肯定是仅仅删除除了max id中的某个id信息
修改如下
DELETE FROM
logrecord
WHERE (in_id, case_id) in
(SELECT in_id, case_id FROM
(SELECT in_id, case_id FROM logrecord GROUP BY in_id, case_id HAVING COUNT( * ) >1) as a)
AND id not in
(SELECT id FROM
(select max(`id`) as id from logrecord GROUP BY in_id, case_id having count(* )>1) as m)
可实现需求,搞定!