-- ============================================================
|
-- 业务数据清理存储过程（目标库：mom-thzy）
|
--
|
-- 用途：清空各业务模块的流水/事务数据，保留用户、角色、菜单、
|
-- 字典、流程定义、基础配置与主数据档案。
|
--
|
-- 注意：
|
-- 1. TRUNCATE 为 DDL 隐式提交，无法回滚，执行前请先备份。
|
-- 2. 执行方式：mysql -uroot -p mom-thzy < sp_clean_business_data.sql
|
-- 或在客户端中整体执行本脚本（含 DELIMITER）。
|
-- 3. 执行清理：CALL sp_clean_business_data();
|
-- ============================================================
|
|
DROP PROCEDURE IF EXISTS `sp_clean_business_data`;
|
|
DELIMITER $$
|
|
CREATE DEFINER=`root`@`%` PROCEDURE `sp_clean_business_data`()
|
BEGIN
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
-- ============================================================
|
-- 1. 系统运行数据（日志 / Token / 验证码 / 站内信 / 社交绑定）
|
-- ============================================================
|
TRUNCATE TABLE system_login_log;
|
TRUNCATE TABLE system_operate_log;
|
TRUNCATE TABLE system_notify_message;
|
TRUNCATE TABLE system_oauth2_access_token;
|
TRUNCATE TABLE system_oauth2_refresh_token;
|
TRUNCATE TABLE system_oauth2_code;
|
TRUNCATE TABLE system_oauth2_approve;
|
TRUNCATE TABLE system_sms_code;
|
TRUNCATE TABLE system_sms_log;
|
TRUNCATE TABLE system_mail_log;
|
TRUNCATE TABLE system_social_user;
|
TRUNCATE TABLE system_social_user_bind;
|
TRUNCATE TABLE infra_api_access_log;
|
TRUNCATE TABLE infra_api_error_log;
|
TRUNCATE TABLE infra_job_log;
|
|
-- 删除与本项目无关的框架自带定时任务（pay/mall/iot/demo），
|
-- 保留日志清理三件套：accessLogCleanJob / errorLogCleanJob / jobLogCleanJob
|
DELETE FROM infra_job
|
WHERE handler_name IN (
|
'payNotifyJob', 'payOrderSyncJob', 'payOrderExpireJob',
|
'payRefundSyncJob', 'payTransferSyncJob',
|
'tradeOrderAutoCancelJob', 'tradeOrderAutoReceiveJob', 'tradeOrderAutoCommentJob',
|
'brokerageRecordUnfreezeJob', 'combinationRecordExpireJob',
|
'couponExpireJob', 'productStatisticsJob',
|
'iotDeviceOfflineCheckJob', 'iotOtaUpgradeJob',
|
'demoJob'
|
);
|
|
-- Quartz 调度运行时数据（qrtz_locks 保留锁行，清空会导致调度器加锁报错）
|
-- 清理后如需恢复保留任务的调度注册，在【基础设施-定时任务】页面点"同步"或调用
|
-- POST /admin-api/infra/job/sync 从 infra_job 重建触发器
|
TRUNCATE TABLE qrtz_blob_triggers;
|
TRUNCATE TABLE qrtz_calendars;
|
TRUNCATE TABLE qrtz_cron_triggers;
|
TRUNCATE TABLE qrtz_fired_triggers;
|
TRUNCATE TABLE qrtz_job_details;
|
TRUNCATE TABLE qrtz_paused_trigger_grps;
|
TRUNCATE TABLE qrtz_scheduler_state;
|
TRUNCATE TABLE qrtz_simple_triggers;
|
TRUNCATE TABLE qrtz_simprop_triggers;
|
TRUNCATE TABLE qrtz_triggers;
|
|
-- ============================================================
|
-- 2. 文件与附件
|
-- ============================================================
|
TRUNCATE TABLE system_storage_attachment;
|
TRUNCATE TABLE system_storage_blob;
|
TRUNCATE TABLE storage_attachment;
|
TRUNCATE TABLE storage_blob;
|
TRUNCATE TABLE infra_file;
|
TRUNCATE TABLE infra_file_content;
|
|
-- ============================================================
|
-- 3. BPM 流程实例与审批历史
|
-- 保留：流程定义/表单/分类/表达式/监听器/用户组/业务附件配置
|
-- 保留：act_re_* / act_ge_* / act_id_* / act_procdef_info（定义与部署资源）
|
-- ============================================================
|
TRUNCATE TABLE bpm_oa_leave;
|
TRUNCATE TABLE bpm_process_instance_copy;
|
TRUNCATE TABLE act_hi_procinst;
|
TRUNCATE TABLE act_hi_taskinst;
|
TRUNCATE TABLE act_hi_actinst;
|
TRUNCATE TABLE act_hi_varinst;
|
TRUNCATE TABLE act_hi_identitylink;
|
TRUNCATE TABLE act_hi_comment;
|
TRUNCATE TABLE act_hi_detail;
|
TRUNCATE TABLE act_hi_attachment;
|
TRUNCATE TABLE act_hi_entitylink;
|
TRUNCATE TABLE act_hi_tsk_log;
|
TRUNCATE TABLE act_ru_task;
|
TRUNCATE TABLE act_ru_execution;
|
TRUNCATE TABLE act_ru_actinst;
|
TRUNCATE TABLE act_ru_variable;
|
TRUNCATE TABLE act_ru_identitylink;
|
TRUNCATE TABLE act_ru_job;
|
TRUNCATE TABLE act_ru_timer_job;
|
TRUNCATE TABLE act_ru_deadletter_job;
|
TRUNCATE TABLE act_ru_suspended_job;
|
TRUNCATE TABLE act_ru_external_job;
|
TRUNCATE TABLE act_ru_history_job;
|
TRUNCATE TABLE act_ru_event_subscr;
|
TRUNCATE TABLE act_ru_entitylink;
|
TRUNCATE TABLE act_evt_log;
|
-- Flowable 批次运行时（multi-instance 批处理，原脚本遗漏）
|
TRUNCATE TABLE flw_ru_batch;
|
TRUNCATE TABLE flw_ru_batch_part;
|
|
-- ============================================================
|
-- 4. MES 生产模块 — 业务数据
|
-- 保留：mes_pro_process / mes_pro_route*（工艺与路线定义）
|
-- ============================================================
|
TRUNCATE TABLE mes_pro_work_order_param_record_detail;
|
TRUNCATE TABLE mes_pro_work_order_param_record;
|
TRUNCATE TABLE mes_pro_work_order_process;
|
TRUNCATE TABLE mes_pro_work_order_bom;
|
TRUNCATE TABLE mes_pro_work_order;
|
TRUNCATE TABLE mes_pro_task_issue;
|
TRUNCATE TABLE mes_pro_task;
|
TRUNCATE TABLE mes_pro_task_pause_record; -- 生产任务停工记录（原脚本遗漏）
|
TRUNCATE TABLE mes_pro_feedback;
|
TRUNCATE TABLE mes_pro_mps_merge_item;
|
TRUNCATE TABLE mes_pro_mps;
|
TRUNCATE TABLE mes_pro_card_process;
|
TRUNCATE TABLE mes_pro_card;
|
TRUNCATE TABLE mes_pro_work_record_log;
|
TRUNCATE TABLE mes_pro_work_record;
|
TRUNCATE TABLE mes_pro_andon_record;
|
TRUNCATE TABLE mes_pro_scan_event; -- 袋码扫码入库事件（原脚本遗漏）
|
|
-- ============================================================
|
-- 5. MES 仓储模块 — 业务数据
|
-- 保留：mes_wm_warehouse / _area / _location / _barcode_config（仓储配置）
|
-- ============================================================
|
TRUNCATE TABLE mes_wm_transaction;
|
TRUNCATE TABLE mes_wm_stock_reserve;
|
TRUNCATE TABLE mes_wm_material_stock;
|
TRUNCATE TABLE mes_wm_material_stock_bag; -- 库存袋码明细（原脚本遗漏）
|
TRUNCATE TABLE mes_wm_stock_warning; -- 库存预警记录（原脚本遗漏）
|
TRUNCATE TABLE mes_wm_sn;
|
TRUNCATE TABLE mes_wm_batch;
|
TRUNCATE TABLE mes_wm_barcode;
|
TRUNCATE TABLE mes_wm_stock_taking_task_result;
|
TRUNCATE TABLE mes_wm_stock_taking_task_line;
|
TRUNCATE TABLE mes_wm_stock_taking_task;
|
TRUNCATE TABLE mes_wm_stock_taking_plan_param;
|
TRUNCATE TABLE mes_wm_stock_taking_plan;
|
TRUNCATE TABLE mes_wm_package_line;
|
TRUNCATE TABLE mes_wm_package;
|
TRUNCATE TABLE mes_wm_item_receipt_detail;
|
TRUNCATE TABLE mes_wm_item_receipt_line;
|
TRUNCATE TABLE mes_wm_item_receipt;
|
TRUNCATE TABLE mes_wm_item_consume_detail;
|
TRUNCATE TABLE mes_wm_item_consume_line;
|
TRUNCATE TABLE mes_wm_item_consume;
|
TRUNCATE TABLE mes_wm_product_produce_detail;
|
TRUNCATE TABLE mes_wm_product_produce_line;
|
TRUNCATE TABLE mes_wm_product_produce;
|
TRUNCATE TABLE mes_wm_product_issue_detail;
|
TRUNCATE TABLE mes_wm_product_issue_line;
|
TRUNCATE TABLE mes_wm_product_issue;
|
TRUNCATE TABLE mes_wm_product_receipt_detail;
|
TRUNCATE TABLE mes_wm_product_receipt_line;
|
TRUNCATE TABLE mes_wm_product_receipt;
|
TRUNCATE TABLE mes_wm_product_sales_detail;
|
TRUNCATE TABLE mes_wm_product_sales_line;
|
TRUNCATE TABLE mes_wm_product_sales;
|
TRUNCATE TABLE mes_wm_misc_receipt_detail;
|
TRUNCATE TABLE mes_wm_misc_receipt_line;
|
TRUNCATE TABLE mes_wm_misc_receipt;
|
TRUNCATE TABLE mes_wm_misc_issue_detail;
|
TRUNCATE TABLE mes_wm_misc_issue_line;
|
TRUNCATE TABLE mes_wm_misc_issue;
|
TRUNCATE TABLE mes_wm_outsource_receipt_detail;
|
TRUNCATE TABLE mes_wm_outsource_receipt_line;
|
TRUNCATE TABLE mes_wm_outsource_receipt;
|
TRUNCATE TABLE mes_wm_outsource_issue_detail;
|
TRUNCATE TABLE mes_wm_outsource_issue_line;
|
TRUNCATE TABLE mes_wm_outsource_issue;
|
TRUNCATE TABLE mes_wm_return_vendor_detail;
|
TRUNCATE TABLE mes_wm_return_vendor_line;
|
TRUNCATE TABLE mes_wm_return_vendor;
|
TRUNCATE TABLE mes_wm_return_issue_detail;
|
TRUNCATE TABLE mes_wm_return_issue_line;
|
TRUNCATE TABLE mes_wm_return_issue;
|
TRUNCATE TABLE mes_wm_return_sales_detail;
|
TRUNCATE TABLE mes_wm_return_sales_line;
|
TRUNCATE TABLE mes_wm_return_sales;
|
TRUNCATE TABLE mes_wm_sales_notice_line;
|
TRUNCATE TABLE mes_wm_sales_notice;
|
TRUNCATE TABLE mes_wm_transfer_detail;
|
TRUNCATE TABLE mes_wm_transfer_line;
|
TRUNCATE TABLE mes_wm_transfer;
|
TRUNCATE TABLE mes_wm_arrival_notice_line;
|
TRUNCATE TABLE mes_wm_arrival_notice;
|
|
-- ============================================================
|
-- 6. MES 质检模块 — 业务数据
|
-- 保留：mes_qc_template(_indicator/_item) / _indicator / _defect（质检配置与字典）
|
-- ============================================================
|
TRUNCATE TABLE mes_qc_indicator_result_detail;
|
TRUNCATE TABLE mes_qc_indicator_result;
|
TRUNCATE TABLE mes_qc_defect_record;
|
TRUNCATE TABLE mes_qc_ncr;
|
TRUNCATE TABLE mes_qc_iqc_line;
|
TRUNCATE TABLE mes_qc_iqc;
|
TRUNCATE TABLE mes_qc_ipqc_line;
|
TRUNCATE TABLE mes_qc_ipqc;
|
TRUNCATE TABLE mes_qc_oqc_line;
|
TRUNCATE TABLE mes_qc_oqc;
|
TRUNCATE TABLE mes_qc_rqc_line;
|
TRUNCATE TABLE mes_qc_rqc;
|
-- 种子质检记录（原脚本遗漏）
|
TRUNCATE TABLE mes_qc_seed_quality_indicator;
|
TRUNCATE TABLE mes_qc_seed_quality_source;
|
TRUNCATE TABLE mes_qc_seed_quality;
|
|
-- ============================================================
|
-- 7. MES 追溯模块 — 事件与预警记录（原脚本遗漏）
|
-- 保留：mes_trace_anti_config（防伪窜配置）
|
-- ============================================================
|
TRUNCATE TABLE mes_trace_consumer_scan_event;
|
TRUNCATE TABLE mes_trace_cross_region_warning;
|
TRUNCATE TABLE mes_trace_integrity_record;
|
TRUNCATE TABLE mes_trace_event;
|
|
-- ============================================================
|
-- 8. MES 设备模块 — 点检/保养/维修/计量检测记录
|
-- 保留：mes_dv_machinery(_type) / _check_plan* / _subject / _metering（台账与计划）
|
-- ============================================================
|
TRUNCATE TABLE mes_dv_check_record_line;
|
TRUNCATE TABLE mes_dv_check_record;
|
TRUNCATE TABLE mes_dv_mainten_record_line;
|
TRUNCATE TABLE mes_dv_mainten_record;
|
TRUNCATE TABLE mes_dv_repair_line;
|
TRUNCATE TABLE mes_dv_repair;
|
TRUNCATE TABLE mes_dv_metering_check;
|
|
-- ============================================================
|
-- 9. MES 工艺/设计模块 — 业务数据（编码生成记录为流水数据）
|
-- 保留：mes_pd_process_param(_template/_detail)（工艺参数定义）
|
-- / mes_md_auto_code_rule / _part（编码规则）
|
-- ============================================================
|
TRUNCATE TABLE mes_pd_archive;
|
TRUNCATE TABLE mes_pd_document_audit;
|
TRUNCATE TABLE mes_pd_document;
|
TRUNCATE TABLE mes_pd_project;
|
TRUNCATE TABLE mes_md_auto_code_record;
|
|
-- ============================================================
|
-- 10. CRM 模块 — 业务数据
|
-- 保留：crm_customer / crm_contact / crm_business_status(_type)
|
-- / crm_contract_config / crm_customer_limit_config / crm_customer_pool_config
|
-- ============================================================
|
TRUNCATE TABLE crm_receivable_plan;
|
TRUNCATE TABLE crm_receivable;
|
TRUNCATE TABLE crm_contract_product;
|
TRUNCATE TABLE crm_contract;
|
TRUNCATE TABLE crm_sale_quotation_product;
|
TRUNCATE TABLE crm_sale_quotation;
|
TRUNCATE TABLE crm_business_product;
|
TRUNCATE TABLE crm_contact_business;
|
TRUNCATE TABLE crm_business;
|
TRUNCATE TABLE crm_clue;
|
TRUNCATE TABLE crm_follow_up_record;
|
TRUNCATE TABLE crm_invoice;
|
TRUNCATE TABLE crm_sales_target;
|
TRUNCATE TABLE crm_permission;
|
|
-- ============================================================
|
-- 11. 售后模块 — 业务数据
|
-- ============================================================
|
TRUNCATE TABLE after_sale_return_item;
|
TRUNCATE TABLE after_sale_return;
|
TRUNCATE TABLE after_sale_ticket_item;
|
TRUNCATE TABLE after_sale_ticket;
|
TRUNCATE TABLE after_sale_repair;
|
TRUNCATE TABLE after_sale_permission;
|
|
-- ============================================================
|
-- 12. ERP 模块 — 业务数据
|
-- 保留：erp_supplier / erp_account
|
-- ============================================================
|
TRUNCATE TABLE erp_finance_payment_item;
|
TRUNCATE TABLE erp_finance_payment;
|
TRUNCATE TABLE erp_finance_receipt_item;
|
TRUNCATE TABLE erp_finance_receipt;
|
TRUNCATE TABLE erp_purchase_order_items;
|
TRUNCATE TABLE erp_purchase_order;
|
TRUNCATE TABLE erp_purchase_request_items;
|
TRUNCATE TABLE erp_purchase_request;
|
TRUNCATE TABLE erp_sale_order_items;
|
TRUNCATE TABLE erp_sale_order;
|
TRUNCATE TABLE erp_purchase_invoice;
|
|
-- ============================================================
|
-- 13. SRM 模块 — 业务数据
|
-- 保留：srm_supplier / srm_supplier_contact / srm_supplier_category
|
-- / srm_tender_config
|
-- ============================================================
|
TRUNCATE TABLE srm_bid_award;
|
TRUNCATE TABLE srm_bid_evaluation;
|
TRUNCATE TABLE srm_bid_open;
|
TRUNCATE TABLE srm_tender_bid;
|
TRUNCATE TABLE srm_tender_material;
|
TRUNCATE TABLE srm_tender_project;
|
TRUNCATE TABLE srm_delivery_plan;
|
TRUNCATE TABLE srm_purchase_collaboration;
|
TRUNCATE TABLE srm_supplier_quote;
|
TRUNCATE TABLE srm_supplier_evaluation;
|
TRUNCATE TABLE srm_supplier_certificate;
|
TRUNCATE TABLE srm_supplier_apply;
|
|
-- ============================================================
|
-- 14. HRM 模块 — 业务数据
|
-- 保留：hrm_employee 及档案子表（教育/工作经历/紧急联系人/
|
-- 社保公积金/薪酬档案/合同）、考勤节假日/规则、请假类型配置、
|
-- 薪酬结构、社保公积金方案、个税税率配置
|
-- ============================================================
|
TRUNCATE TABLE hrm_salary_payment_detail;
|
TRUNCATE TABLE hrm_salary_payment;
|
TRUNCATE TABLE hrm_salary_calculation;
|
TRUNCATE TABLE hrm_attendance_summary;
|
TRUNCATE TABLE hrm_attendance_record;
|
TRUNCATE TABLE hrm_attendance_exception;
|
TRUNCATE TABLE hrm_leave_application;
|
TRUNCATE TABLE hrm_resignation_application;
|
TRUNCATE TABLE hrm_transfer_application;
|
|
-- ============================================================
|
-- 15. AI 模块 — 对话与生成内容
|
-- 保留：ai_api_key / ai_model / ai_tool / ai_chat_role
|
-- / ai_knowledge(_document/_segment) / ai_workflow
|
-- ============================================================
|
TRUNCATE TABLE ai_chat_message;
|
TRUNCATE TABLE ai_chat_conversation;
|
TRUNCATE TABLE ai_image;
|
TRUNCATE TABLE ai_mind_map;
|
TRUNCATE TABLE ai_music;
|
TRUNCATE TABLE ai_write;
|
|
-- ============================================================
|
-- 16. IM 模块 — 会话与消息
|
-- 保留：im_sensitive_word / im_face_pack / im_face_pack_item
|
-- ============================================================
|
TRUNCATE TABLE im_group_message_receipt;
|
TRUNCATE TABLE im_group_message;
|
TRUNCATE TABLE im_private_message;
|
TRUNCATE TABLE im_message_file;
|
TRUNCATE TABLE im_group_request;
|
TRUNCATE TABLE im_group_member;
|
TRUNCATE TABLE im_group;
|
TRUNCATE TABLE im_friend_request;
|
TRUNCATE TABLE im_friend;
|
TRUNCATE TABLE im_conversation_read;
|
TRUNCATE TABLE im_conversation;
|
TRUNCATE TABLE im_channel_message;
|
TRUNCATE TABLE im_channel_material;
|
TRUNCATE TABLE im_channel;
|
TRUNCATE TABLE im_rtc_participant;
|
TRUNCATE TABLE im_rtc_call;
|
TRUNCATE TABLE im_user_online;
|
TRUNCATE TABLE im_face_user_item;
|
|
-- ============================================================
|
-- 17. OA 已读/指定人关联（保留通知公告内容 oa_notice / system_notice）
|
-- ============================================================
|
TRUNCATE TABLE oa_notice_read;
|
TRUNCATE TABLE oa_notice_user;
|
|
-- ============================================================
|
-- 18. 示例数据
|
-- ============================================================
|
TRUNCATE TABLE yudao_demo01_contact;
|
TRUNCATE TABLE yudao_demo02_category;
|
TRUNCATE TABLE yudao_demo03_course;
|
TRUNCATE TABLE yudao_demo03_grade;
|
TRUNCATE TABLE yudao_demo03_student;
|
|
-- ============================================================
|
-- 19. 基础数据与一袋一码管理
|
-- 基础数据：物料管理 / 物料分类 / 计量单位 / 品牌 / SKU
|
-- 一袋一码：产线维护 / 批次生产管理 / 赋码管理 / 托盘码管理
|
-- 品种数据源：mes_md_item（与 mdm_item 同步，批次/袋码选品种使用）
|
-- 保留：mes_md_vendor（供应商，与 erp_supplier 策略一致）
|
-- / mdm_warehouse（仓库，与 mes_wm_warehouse 策略一致）
|
-- / mes_md_workshop / mes_md_workstation*（车间工作站配置）
|
-- ============================================================
|
-- 一袋一码：先清引用批次的明细，再清批次与产线
|
TRUNCATE TABLE mes_pro_batch_worker;
|
TRUNCATE TABLE mes_pro_bag_code_replace; -- 袋码替换记录（原脚本遗漏）
|
TRUNCATE TABLE mes_pro_bag_code;
|
TRUNCATE TABLE mes_pro_pallet;
|
TRUNCATE TABLE mes_pro_batch;
|
TRUNCATE TABLE mes_pro_line;
|
-- 品种数据源（MES 物料产品关联配置）
|
TRUNCATE TABLE mes_md_product_bom;
|
TRUNCATE TABLE mes_md_product_sop;
|
TRUNCATE TABLE mes_md_product_sip;
|
TRUNCATE TABLE mes_md_item_batch_config;
|
TRUNCATE TABLE mes_md_unit_measure; -- MES 计量单位（原脚本遗漏）
|
TRUNCATE TABLE mes_md_item;
|
-- 基础数据（MDM 物料主数据 / 分类 / 计量单位 / 品牌 / SKU）
|
TRUNCATE TABLE mdm_item_batch_config;
|
TRUNCATE TABLE mdm_item_sku; -- 物料 SKU（mdm_item 子表，原脚本遗漏）
|
TRUNCATE TABLE mdm_item;
|
TRUNCATE TABLE mdm_item_category; -- 物料分类（原脚本遗漏）
|
TRUNCATE TABLE mdm_brand; -- 品牌（原脚本遗漏）
|
TRUNCATE TABLE mes_md_item_type;
|
TRUNCATE TABLE mdm_unit_measure;
|
-- 物料迁移映射表：如清理后需从 ERP 重新迁移物料，请放开下面一行
|
-- TRUNCATE TABLE mdm_item_migration_map;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
-- ============================================================
|
-- 清理结果校验（代表表行数应全部为 0）
|
-- ============================================================
|
SELECT
|
(SELECT COUNT(*) FROM mes_pro_work_order) AS work_order_cnt,
|
(SELECT COUNT(*) FROM mes_wm_material_stock) AS stock_cnt,
|
(SELECT COUNT(*) FROM mes_qc_iqc) AS iqc_cnt,
|
(SELECT COUNT(*) FROM crm_contract) AS contract_cnt,
|
(SELECT COUNT(*) FROM erp_sale_order) AS sale_order_cnt,
|
(SELECT COUNT(*) FROM mdm_item) AS mdm_item_cnt,
|
(SELECT COUNT(*) FROM system_operate_log) AS operate_log_cnt;
|
|
SELECT '业务数据清理完成' AS result;
|
END $$
|
|
DELIMITER ;
|
