activity数据表清理

墨阳明

已于 2023-07-14 12:09:06 修改

阅读量1.6k

点赞数 2

文章标签：数据库

于 2023-07-14 11:59:48 首次发布

本文链接：https://blog.csdn.net/qq_33082703/article/details/131720474

版权

文章介绍了如何通过添加create_time字段并更新相关表格，然后依据时间戳按年份删除不再需要的历史数据。提供的SQL语句用于在Activity数据库中同步创建时间并删除特定年份的数据，涉及表包括act_ge_bytearray,act_hi_identitylink,act_ru_execution等。

摘要由CSDN通过智能技术生成

activity使用长了会累积很多的流程数据，历史表数据就会变得很多，认真看了数据说明后发现使用sql语句就能直接清理掉历史数据。
其原理就是在要删除的表中新增一个create_time字段，然后通过PROC_INST_ID_或者TASK_ID_来同步出对应的时间，最后再来按年份删除，对应的sql语句请参考。

-- 同步日期,没有create_time的要新增字段
UPDATE act_ge_bytearray as a left join `act_hi_varinst` as b on a.ID_=b.BYTEARRAY_ID_ set a.create_time=b.CREATE_TIME_ where !ISNULL(b.BYTEARRAY_ID_);

UPDATE `act_hi_identitylink` as a left join ( SELECT START_TIME_, PROC_INST_ID_ FROM act_hi_actinst GROUP BY PROC_INST_ID_ ) as b on a.PROC_INST_ID_=b.PROC_INST_ID_  set a.create_time=b.START_TIME_ where ISNULL(a.create_time) and !ISNULL(a.PROC_INST_ID_);

UPDATE `act_hi_identitylink` as a left join ( SELECT START_TIME_, TASK_ID_ FROM act_hi_actinst GROUP BY TASK_ID_ ) as b on a.TASK_ID_=b.TASK_ID_  set a.create_time=b.START_TIME_ where ISNULL(a.create_time) and !ISNULL(a.TASK_ID_);


update act_ru_execution AS a Left JOIN act_ru_task AS b ON a.PROC_INST_ID_ = b.PROC_INST_ID_  set a.create_time=b.CREATE_TIME_ where isnull(a.create_time);

update act_ru_variable AS a Left JOIN act_ru_task AS b ON a.PROC_INST_ID_ = b.PROC_INST_ID_  set a.create_time=b.CREATE_TIME_ where isnull(a.create_time);

UPDATE act_ge_bytearray as a left join `act_ru_variable` as b on a.ID_=b.BYTEARRAY_ID_ set a.create_time=b.create_time where !ISNULL(b.BYTEARRAY_ID_);

UPDATE act_ru_identitylink AS a LEFT JOIN act_ru_task AS b ON a.TASK_ID_ = b.ID_ SET a.create_time = b.CREATE_TIME_ WHERE !isnull( a.TASK_ID_ );

UPDATE act_ru_identitylink AS a LEFT JOIN (SELECT CREATE_TIME_,PROC_INST_ID_ from act_ru_task group by PROC_INST_ID_) AS b ON a.PROC_INST_ID_ = b.PROC_INST_ID_ SET a.create_time = b.CREATE_TIME_ WHERE ISNULL( a.create_time) and !ISNULL(a.PROC_INST_ID_);


-- 按次序删除,有外键关联
DELETE FROM act_ru_variable where YEAR(create_time)=2021;
DELETE FROM act_ge_bytearray where YEAR(create_time)=2021;
DELETE FROM act_hi_actinst where YEAR(START_TIME_)=2021;
DELETE FROM act_hi_comment where YEAR(TIME_)=2021;
DELETE FROM act_hi_identitylink where YEAR(create_time)=2021;
DELETE FROM act_hi_procinst where YEAR(START_TIME_)=2021;
DELETE FROM act_hi_taskinst where YEAR(START_TIME_)=2021;
DELETE FROM act_hi_varinst where YEAR(CREATE_TIME_)=2021;

DELETE FROM act_ru_identitylink where YEAR(create_time)=2021;
DELETE FROM act_ru_task where YEAR(CREATE_TIME_)=2021;
DELETE FROM act_ru_execution where YEAR(create_time)=2021;