-- ============================================================
|
-- 业务数据清理存储过程（2026-10-23 版）
|
--
|
-- 清理范围：基础数据、采购、销售、设备、仓储、工艺设计、产品设计、
|
-- 生产、质量、协同审核记录、能耗、售后、系统日志/Token/附件
|
-- 保留：系统用户/角色/菜单/字典、基础设施配置、编码规则、
|
-- 流程配置（分类/表单/分组/监听器/表达式/定义扩展/业务附件/免审批配置）、
|
-- 流程定义与引擎元数据（act_re_*/act_ge_*/act_id_*/flw_event_*）
|
-- 注意：TRUNCATE 不可回滚，执行前务必备份
|
--
|
-- 用法：
|
-- CALL sp_clean_business_data_check(); -- 预检：只统计各表行数，不删数据
|
-- CALL sp_clean_business_data(); -- 执行：真正清空
|
--
|
-- 优化点：
|
-- 1. 目标表集中在临时表维护，新增/改名时只改清单，避免大段重复 SQL
|
-- 2. 自动跳过不存在的表（兼容不同环境的库结构漂移）
|
-- 3. 预检模式输出每个分区待清理行数，便于执行前评估
|
-- 4. 统一关闭/恢复 FOREIGN_KEY_CHECKS，杜绝外键阻塞
|
-- ============================================================
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_clean_business_data_core$$
|
DROP PROCEDURE IF EXISTS sp_clean_business_data$$
|
DROP PROCEDURE IF EXISTS sp_clean_business_data_check$$
|
|
-- ------------------------------------------------------------
|
-- 核心过程：p_dry_run = 0 执行；1 仅预检
|
-- ------------------------------------------------------------
|
CREATE PROCEDURE sp_clean_business_data_core(IN p_dry_run TINYINT)
|
BEGIN
|
DECLARE v_done INT DEFAULT 0;
|
DECLARE v_section VARCHAR(64);
|
DECLARE v_table VARCHAR(64);
|
DECLARE v_status VARCHAR(16);
|
DECLARE v_exists INT DEFAULT 0;
|
DECLARE v_rows BIGINT DEFAULT NULL;
|
|
DECLARE cur CURSOR FOR
|
SELECT section, table_name FROM tmp_clean_targets ORDER BY section, table_name;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
|
|
-- 中途出错时兜底：恢复外键检查并清理临时表
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
BEGIN
|
SET FOREIGN_KEY_CHECKS = 1;
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;
|
RESIGNAL;
|
END;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;
|
|
CREATE TEMPORARY TABLE tmp_clean_targets (
|
table_name VARCHAR(64) NOT NULL PRIMARY KEY,
|
section VARCHAR(64) NOT NULL DEFAULT ''
|
) ENGINE = MEMORY;
|
|
CREATE TEMPORARY TABLE tmp_clean_result (
|
section VARCHAR(64) NOT NULL,
|
table_name VARCHAR(64) NOT NULL,
|
status VARCHAR(16) NOT NULL,
|
rows_before BIGINT NULL
|
) ENGINE = MEMORY;
|
|
INSERT INTO tmp_clean_targets (table_name) VALUES
|
-- 01 系统日志 / Token / 消息记录
|
('system_login_log'),
|
('system_operate_log'),
|
('system_notify_message'),
|
('system_oauth2_access_token'),
|
('system_oauth2_refresh_token'),
|
('system_oauth2_code'),
|
('system_oauth2_approve'),
|
('system_sms_code'),
|
('system_sms_log'),
|
('system_mail_log'),
|
('system_social_user'),
|
('system_social_user_bind'),
|
('infra_api_access_log'),
|
('infra_api_error_log'),
|
('infra_job_log'),
|
-- 02 系统附件
|
('system_storage_attachment'),
|
('system_storage_blob'),
|
('storage_attachment'),
|
('storage_blob'),
|
('infra_file'),
|
('infra_file_content'),
|
-- 03 基础数据 - MDM 主数据
|
('mdm_item'),
|
('mdm_item_sku'),
|
('mdm_item_model'),
|
('mdm_item_batch_config'),
|
('mdm_item_migration_map'),
|
('mdm_item_category'),
|
('mdm_brand'),
|
('mdm_unit_measure'),
|
('mdm_warehouse'),
|
-- 04 基础数据 - MES 物料 / BOM
|
('mes_md_item'),
|
('mes_md_item_type'),
|
('mes_md_item_batch_config'),
|
('mes_md_unit_measure'),
|
('mes_md_vendor'),
|
('mes_md_product_bom'),
|
('mes_md_product_sop'),
|
('mes_md_product_sip'),
|
('mes_md_auto_code_record'),
|
-- 05 基础数据 - MES 车间 / 产线 / 班组排班
|
('mes_md_workshop'),
|
('mes_md_workstation'),
|
('mes_md_workstation_machine'),
|
('mes_md_workstation_tool'),
|
('mes_md_workstation_worker'),
|
('mes_pro_line'),
|
('mes_cal_holiday'),
|
('mes_cal_team'),
|
('mes_cal_team_member'),
|
('mes_cal_team_shift'),
|
('mes_cal_plan'),
|
('mes_cal_plan_shift'),
|
('mes_cal_plan_team'),
|
-- 06 基础数据 - MES 设备 / 工具台账
|
('mes_dv_machinery'),
|
('mes_dv_machinery_type'),
|
('mes_dv_subject'),
|
('mes_dv_check_plan'),
|
('mes_dv_check_plan_machinery'),
|
('mes_dv_check_plan_subject'),
|
('mes_dv_metering'),
|
('mes_tm_tool'),
|
('mes_tm_tool_type'),
|
-- 07 基础数据 - MES 工艺 / 参数配置
|
('mes_pro_route'),
|
('mes_pro_route_process'),
|
('mes_pro_route_product'),
|
('mes_pro_route_product_bom'),
|
('mes_pro_process'),
|
('mes_pro_process_content'),
|
('mes_pro_process_param'),
|
('mes_pd_process_param'),
|
('mes_pd_process_param_template'),
|
('mes_pd_process_param_template_detail'),
|
('mes_pro_andon_config'),
|
-- 08 基础数据 - MES 质检基础（方案 / 缺陷 / 指标）
|
('mes_qc_template'),
|
('mes_qc_template_indicator'),
|
('mes_qc_template_item'),
|
('mes_qc_defect'),
|
('mes_qc_indicator'),
|
-- 09 基础数据 - MES 仓库档案
|
('mes_wm_warehouse'),
|
('mes_wm_warehouse_area'),
|
('mes_wm_warehouse_location'),
|
('mes_wm_barcode_config'),
|
-- 10 采购 - ERP
|
('erp_purchase_request'),
|
('erp_purchase_request_items'),
|
('erp_purchase_order'),
|
('erp_purchase_order_items'),
|
('erp_purchase_invoice'),
|
('erp_finance_payment'),
|
('erp_finance_payment_item'),
|
-- 11 采购 - SRM
|
('srm_tender_project'),
|
('srm_tender_material'),
|
('srm_tender_bid'),
|
('srm_bid_open'),
|
('srm_bid_evaluation'),
|
('srm_bid_award'),
|
('srm_supplier_apply'),
|
('srm_supplier_quote'),
|
('srm_supplier_evaluation'),
|
('srm_supplier_certificate'),
|
('srm_delivery_plan'),
|
('srm_purchase_collaboration'),
|
-- 12 销售 - ERP
|
('erp_sale_order'),
|
('erp_sale_order_items'),
|
('erp_finance_receipt'),
|
('erp_finance_receipt_item'),
|
-- 13 销售 - CRM（保留客户/联系人）
|
('crm_clue'),
|
('crm_business'),
|
('crm_business_product'),
|
('crm_contact_business'),
|
('crm_follow_up_record'),
|
('crm_sale_quotation'),
|
('crm_sale_quotation_product'),
|
('crm_contract'),
|
('crm_contract_product'),
|
('crm_receivable'),
|
('crm_receivable_plan'),
|
('crm_invoice'),
|
('crm_sales_target'),
|
('crm_permission'),
|
-- 14 设备 - 点检 / 保养 / 维修 / 计量记录
|
('mes_dv_check_record'),
|
('mes_dv_check_record_line'),
|
('mes_dv_mainten_record'),
|
('mes_dv_mainten_record_line'),
|
('mes_dv_repair'),
|
('mes_dv_repair_line'),
|
('mes_dv_metering_check'),
|
-- 15 仓储 - 业务单据
|
('mes_wm_transaction'),
|
('mes_wm_stock_reserve'),
|
('mes_wm_material_stock'),
|
('mes_wm_sn'),
|
('mes_wm_batch'),
|
('mes_wm_barcode'),
|
('mes_wm_stock_taking_task_result'),
|
('mes_wm_stock_taking_task_line'),
|
('mes_wm_stock_taking_task'),
|
('mes_wm_stock_taking_plan_param'),
|
('mes_wm_stock_taking_plan'),
|
('mes_wm_package_line'),
|
('mes_wm_package'),
|
('mes_wm_item_receipt_detail'),
|
('mes_wm_item_receipt_line'),
|
('mes_wm_item_receipt'),
|
('mes_wm_item_consume_detail'),
|
('mes_wm_item_consume_line'),
|
('mes_wm_item_consume'),
|
('mes_wm_product_produce_detail'),
|
('mes_wm_product_produce_line'),
|
('mes_wm_product_produce'),
|
('mes_wm_product_issue_detail'),
|
('mes_wm_product_issue_line'),
|
('mes_wm_product_issue'),
|
('mes_wm_product_receipt_detail'),
|
('mes_wm_product_receipt_line'),
|
('mes_wm_product_receipt'),
|
('mes_wm_product_sales_detail'),
|
('mes_wm_product_sales_line'),
|
('mes_wm_product_sales'),
|
('mes_wm_misc_receipt_detail'),
|
('mes_wm_misc_receipt_line'),
|
('mes_wm_misc_receipt'),
|
('mes_wm_misc_issue_detail'),
|
('mes_wm_misc_issue_line'),
|
('mes_wm_misc_issue'),
|
('mes_wm_outsource_receipt_detail'),
|
('mes_wm_outsource_receipt_line'),
|
('mes_wm_outsource_receipt'),
|
('mes_wm_outsource_issue_detail'),
|
('mes_wm_outsource_issue_line'),
|
('mes_wm_outsource_issue'),
|
('mes_wm_return_vendor_detail'),
|
('mes_wm_return_vendor_line'),
|
('mes_wm_return_vendor'),
|
('mes_wm_return_issue_detail'),
|
('mes_wm_return_issue_line'),
|
('mes_wm_return_issue'),
|
('mes_wm_return_sales_detail'),
|
('mes_wm_return_sales_line'),
|
('mes_wm_return_sales'),
|
('mes_wm_sales_notice_line'),
|
('mes_wm_sales_notice'),
|
('mes_wm_transfer_detail'),
|
('mes_wm_transfer_line'),
|
('mes_wm_transfer'),
|
('mes_wm_arrival_notice_line'),
|
('mes_wm_arrival_notice'),
|
-- 16 工艺设计 / 产品设计 - 业务单据
|
('mes_pd_project'),
|
('mes_pd_document'),
|
('mes_pd_document_audit'),
|
('mes_pd_archive'),
|
-- 17 生产 - 业务单据
|
('mes_pro_work_order_param_record_detail'),
|
('mes_pro_work_order_param_record'),
|
('mes_pro_work_order_process'),
|
('mes_pro_work_order_bom'),
|
('mes_pro_work_order'),
|
('mes_pro_task_issue'),
|
('mes_pro_task_assignee'),
|
('mes_pro_task_pause_record'),
|
('mes_pro_task'),
|
('mes_pro_feedback'),
|
('mes_pro_mps_merge_item'),
|
('mes_pro_mps'),
|
('mes_pro_card_process'),
|
('mes_pro_card'),
|
('mes_pro_work_record_log'),
|
('mes_pro_work_record'),
|
('mes_pro_andon_record'),
|
('mes_pro_batch_worker'),
|
('mes_pro_batch'),
|
('mes_pro_pallet'),
|
('mes_pro_bag_code_replace'),
|
('mes_pro_bag_code'),
|
-- 18 质量 - 检验单据 / 报告实例
|
('mes_qc_indicator_result_detail'),
|
('mes_qc_indicator_result'),
|
('mes_qc_defect_record'),
|
('mes_qc_ncr'),
|
('mes_qc_iqc_line'),
|
('mes_qc_iqc'),
|
('mes_qc_ipqc_line'),
|
('mes_qc_ipqc'),
|
('mes_qc_oqc_line'),
|
('mes_qc_oqc'),
|
('mes_qc_rqc_line'),
|
('mes_qc_rqc'),
|
('qc_report_instance'),
|
('qc_report_render_record'),
|
-- 19 协同 - 审核记录与运行态（流程配置/定义保留）
|
('bpm_oa_leave'),
|
('bpm_process_instance_copy'),
|
('act_hi_procinst'),
|
('act_hi_taskinst'),
|
('act_hi_actinst'),
|
('act_hi_varinst'),
|
('act_hi_identitylink'),
|
('act_hi_comment'),
|
('act_hi_detail'),
|
('act_hi_attachment'),
|
('act_hi_entitylink'),
|
('act_hi_tsk_log'),
|
('act_ru_task'),
|
('act_ru_execution'),
|
('act_ru_actinst'),
|
('act_ru_variable'),
|
('act_ru_identitylink'),
|
('act_ru_job'),
|
('act_ru_timer_job'),
|
('act_ru_deadletter_job'),
|
('act_ru_suspended_job'),
|
('act_ru_external_job'),
|
('act_ru_history_job'),
|
('act_ru_event_subscr'),
|
('act_ru_entitylink'),
|
('act_evt_log'),
|
('flw_ru_batch'),
|
('flw_ru_batch_part'),
|
-- 20 能耗 - 采集记录与告警日志（告警规则保留）
|
('mes_energy_record'),
|
('mes_energy_alert_log'),
|
-- 21 售后
|
('after_sale_return_item'),
|
('after_sale_return'),
|
('after_sale_ticket_item'),
|
('after_sale_ticket'),
|
('after_sale_repair'),
|
('after_sale_permission'),
|
('after_sale_problem'),
|
('after_sale_visit'),
|
-- 22 智能体导入会话（临时数据）
|
('system_agent_import_session'),
|
('agent_import_command'),
|
('agent_import_command_row'),
|
('mdm_agent_import_command'),
|
('mdm_agent_import_command_row');
|
|
-- 补充分区名称（顺序敏感：先匹配特殊表，再按前缀兜底）
|
UPDATE tmp_clean_targets SET section = CASE
|
WHEN table_name LIKE 'agent_import%' OR table_name LIKE 'mdm_agent_import%'
|
OR table_name = 'system_agent_import_session'
|
THEN '22 智能体导入会话'
|
WHEN table_name IN ('system_storage_attachment','system_storage_blob','storage_attachment',
|
'storage_blob','infra_file','infra_file_content')
|
THEN '02 系统附件'
|
WHEN table_name LIKE 'system_%'
|
OR table_name IN ('infra_api_access_log','infra_api_error_log','infra_job_log')
|
THEN '01 系统日志与令牌'
|
WHEN table_name LIKE 'mdm_%'
|
THEN '03 基础数据-MDM'
|
WHEN table_name IN ('mes_md_workshop','mes_md_workstation','mes_md_workstation_machine',
|
'mes_md_workstation_tool','mes_md_workstation_worker','mes_pro_line')
|
OR table_name LIKE 'mes_cal_%'
|
THEN '05 基础数据-MES车间班组'
|
WHEN table_name LIKE 'mes_md_%'
|
THEN '04 基础数据-MES物料BOM'
|
WHEN table_name IN ('mes_dv_check_record','mes_dv_check_record_line','mes_dv_mainten_record',
|
'mes_dv_mainten_record_line','mes_dv_repair','mes_dv_repair_line','mes_dv_metering_check')
|
THEN '14 设备-业务记录'
|
WHEN table_name LIKE 'mes_dv_%' OR table_name LIKE 'mes_tm_%'
|
THEN '06 基础数据-MES设备工具台账'
|
WHEN table_name LIKE 'mes_pro_route%' OR table_name IN ('mes_pro_process','mes_pro_process_content',
|
'mes_pro_process_param','mes_pd_process_param','mes_pd_process_param_template',
|
'mes_pd_process_param_template_detail','mes_pro_andon_config')
|
THEN '07 基础数据-MES工艺参数'
|
WHEN table_name IN ('mes_qc_template','mes_qc_template_indicator','mes_qc_template_item',
|
'mes_qc_defect','mes_qc_indicator')
|
THEN '08 基础数据-MES质检基础'
|
WHEN table_name IN ('mes_wm_warehouse','mes_wm_warehouse_area','mes_wm_warehouse_location','mes_wm_barcode_config')
|
THEN '09 基础数据-MES仓库档案'
|
WHEN table_name LIKE 'erp_purchase%' OR table_name LIKE 'erp_finance_payment%'
|
THEN '10 采购-ERP'
|
WHEN table_name LIKE 'srm_%'
|
THEN '11 采购-SRM'
|
WHEN table_name LIKE 'erp_sale%' OR table_name LIKE 'erp_finance_receipt%'
|
THEN '12 销售-ERP'
|
WHEN table_name LIKE 'crm_%'
|
THEN '13 销售-CRM'
|
WHEN table_name LIKE 'mes_wm_%'
|
THEN '15 仓储-业务单据'
|
WHEN table_name LIKE 'mes_pd_%'
|
THEN '16 工艺设计产品设计'
|
WHEN table_name LIKE 'mes_pro_%'
|
THEN '17 生产'
|
WHEN table_name LIKE 'mes_qc_%' OR table_name LIKE 'qc_report_%'
|
THEN '18 质量'
|
WHEN table_name LIKE 'act_%' OR table_name LIKE 'flw_%' OR table_name LIKE 'bpm_%'
|
THEN '19 协同-审核记录'
|
WHEN table_name LIKE 'mes_energy%'
|
THEN '20 能耗'
|
WHEN table_name LIKE 'after_sale%'
|
THEN '21 售后'
|
ELSE '99 未分类'
|
END;
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
OPEN cur;
|
read_loop: LOOP
|
FETCH cur INTO v_section, v_table;
|
IF v_done = 1 THEN
|
LEAVE read_loop;
|
END IF;
|
|
SELECT COUNT(*) INTO v_exists
|
FROM information_schema.TABLES
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = v_table AND TABLE_TYPE = 'BASE TABLE';
|
|
IF v_exists = 0 THEN
|
SET v_status = 'SKIP';
|
SET v_rows = NULL;
|
ELSEIF p_dry_run = 1 THEN
|
SET v_status = 'DRY-RUN';
|
SET @row_count = 0;
|
SET @sql = CONCAT('SELECT COUNT(*) INTO @row_count FROM `', v_table, '`');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
SET v_rows = @row_count;
|
ELSE
|
SET v_status = 'TRUNCATED';
|
SET v_rows = NULL;
|
SET @sql = CONCAT('TRUNCATE TABLE `', v_table, '`');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
END IF;
|
|
INSERT INTO tmp_clean_result (section, table_name, status, rows_before)
|
VALUES (v_section, v_table, v_status, v_rows);
|
END LOOP;
|
CLOSE cur;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
-- 分区汇总
|
SELECT section AS section_name,
|
SUM(status = 'TRUNCATED') AS truncated_tables,
|
SUM(status = 'DRY-RUN') AS planned_tables,
|
SUM(status = 'SKIP') AS skipped_tables,
|
IFNULL(SUM(rows_before), 0) AS rows_before
|
FROM tmp_clean_result
|
GROUP BY section
|
ORDER BY section;
|
|
-- 不存在而被跳过的表
|
SELECT section AS section_name, table_name
|
FROM tmp_clean_result
|
WHERE status = 'SKIP'
|
ORDER BY section, table_name;
|
|
SELECT IF(p_dry_run = 1, '预检完成：未删除任何数据', '业务数据清理完成') AS result;
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_targets;
|
DROP TEMPORARY TABLE IF EXISTS tmp_clean_result;
|
END$$
|
|
-- ------------------------------------------------------------
|
-- 对外入口
|
-- ------------------------------------------------------------
|
CREATE PROCEDURE sp_clean_business_data()
|
BEGIN
|
CALL sp_clean_business_data_core(0);
|
END$$
|
|
CREATE PROCEDURE sp_clean_business_data_check()
|
BEGIN
|
CALL sp_clean_business_data_core(1);
|
END$$
|
|
DELIMITER ;
|
|
-- ============================================================
|
-- 保留清单（不会被清理）
|
-- 【系统】system_users/system_dept/system_post/system_role/system_role_menu/
|
-- system_menu/system_dict_type/system_dict_data/system_tenant*/
|
-- system_user_post/system_user_role/system_oauth2_client/
|
-- system_notice/system_notify_template/system_mail_*/system_sms_template/
|
-- system_social_client/system_company_config
|
-- 【基础设施】infra_config/infra_job/infra_codegen_*/infra_data_source_config/infra_file_config
|
-- 【编码规则】mes_md_auto_code_rule/mes_md_auto_code_part
|
-- 【流程配置】bpm_category/bpm_form/bpm_user_group/bpm_process_listener/
|
-- bpm_process_expression/bpm_process_definition_info/
|
-- bpm_business_attach/bpm_process_auto_approve_config
|
-- 【流程定义】act_re_deployment/act_re_model/act_re_procdef/act_ge_bytearray/
|
-- act_ge_property/act_id_*/act_procdef_info/flw_event_*/flw_channel_definition
|
-- 【主数据】crm_customer/crm_contact/erp_supplier/erp_account/
|
-- srm_supplier/srm_supplier_contact/srm_supplier_category/srm_tender_config
|
-- 【质量平台配置】qc_report_template/qc_report_template_version/qc_report_rule/
|
-- qc_report_data_source
|
-- 【能耗规则】mes_energy_alert_rule
|
-- 【业务未涉及模块】HRM/AI/IM/OA 公告数据
|
-- ============================================================
|
|
-- 执行：
|
-- CALL sp_clean_business_data_check();
|
-- CALL sp_clean_business_data();
|
