背景
由于工作流实例以及任务实例过多,准备只保留两天的任务记录
需要的表
t_ds_process_instance 工作流实例表
t_ds_task_instance 任务实例表
t_ds_relation_process_instance 工作流和任务实例关联表 保存着工作流和任务实例id
1.2 sql编写
创建工作流实例临时表保存两天的id
drop table if exists tmp_t_ds_process_instance_1;
create table tmp_t_ds_process_instance_1 as
SELECT id process_instance_id
FROM t_ds_process_instance
WHERE DATE(end_time) < CURDATE() - INTERVAL 2 DAY
union
select id process_instance_id
from t_ds_process_instance
where DATE(start_time) < CURDATE() - INTERVAL 2 DAY;
创建任务实例临时表保存两天的id
drop table if exists tmp_t_ds_process_instance_2;
create table tmp_t_ds_process_instance_2 as
select
a.id as task_instance_id
from t_ds_task_instance a, tmp_t_ds_process_instance_1 b
where a.process_instance_id = b.process_instance_id;
保留两天工作流实例日志
DELETE a from t_ds_process_instance a where exists (
select 1 from tmp_t_ds_process_instance_1 b where a.id = b.process_instance_id
);
保留两天的任务实例日志
delete a from t_ds_task_instance a where exists (
select 1 from tmp_t_ds_process_instance_2 b where a.id = b.task_instance_id
);
删除关联的任务日志
delete a from t_ds_relation_process_instance a where exists ( select 1 from tmp_t_ds_process_instance_1 b where a.parent_process_instance_id = b.process_instance_id
);
delete a from t_ds_relation_process_instance a where exists ( select 1 from tmp_t_ds_process_instance_1 b where a.process_instance_id = b.process_instance_id
);
delete a from t_ds_relation_process_instance a where exists (select 1 from tmp_t_ds_process_instance_2 b where a.parent_task_instance_id = b.task_instance_id
);
删除临时表
drop table if exists tmp_t_ds_process_instance_1;
drop table if exists tmp_t_ds_process_instance_2;