-- ============================================================
|
-- 系统业务数据清理存储过程
|
--
|
-- 用途：清空所有业务数据 + 基础数据（物料/客户/供应商/仓库等）
|
-- 保留：系统表、工作流引擎、定时任务、编码规则、基础设施配置
|
-- 注意：此操作不可逆，执行前请确认已备份数据库
|
--
|
-- 使用方式：CALL sp_clean_business_data();
|
-- ============================================================
|
|
DELIMITER $$
|
|
DROP PROCEDURE IF EXISTS sp_clean_business_data$$
|
|
CREATE 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 infra_api_access_log;
|
TRUNCATE TABLE infra_api_error_log;
|
TRUNCATE TABLE infra_job_log;
|
|
-- ============================================================
|
-- 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. MES 生产模块 — 业务单据
|
-- ============================================================
|
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_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;
|
|
-- ============================================================
|
-- 4. MES 仓库模块 — 业务单据
|
-- ============================================================
|
TRUNCATE TABLE mes_wm_transaction;
|
TRUNCATE TABLE mes_wm_stock_reserve;
|
TRUNCATE TABLE mes_wm_material_stock;
|
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;
|
|
-- ============================================================
|
-- 5. MES 质检模块 — 业务单据
|
-- ============================================================
|
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;
|
|
-- ============================================================
|
-- 6. MES 设备模块 — 业务单据
|
-- ============================================================
|
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;
|
|
-- ============================================================
|
-- 7. MES 工艺/设计模块 — 业务单据
|
-- ============================================================
|
TRUNCATE TABLE mes_pd_archive;
|
TRUNCATE TABLE mes_pd_document_audit;
|
TRUNCATE TABLE mes_pd_document;
|
TRUNCATE TABLE mes_pd_project;
|
|
-- ============================================================
|
-- 8. MES 基础数据 — 物料/产品/BOM/SOP/SIP
|
-- ============================================================
|
TRUNCATE TABLE mes_md_product_bom;
|
TRUNCATE TABLE mes_md_product_sip;
|
TRUNCATE TABLE mes_md_product_sop;
|
TRUNCATE TABLE mes_md_item;
|
TRUNCATE TABLE mes_md_item_batch_config;
|
TRUNCATE TABLE mes_md_item_type;
|
TRUNCATE TABLE mes_md_unit_measure;
|
TRUNCATE TABLE mes_md_vendor;
|
TRUNCATE TABLE mes_md_auto_code_record;
|
|
-- ============================================================
|
-- 9. MES 基础数据 — 车间/工作站/资源
|
-- ============================================================
|
TRUNCATE TABLE mes_md_workstation_machine;
|
TRUNCATE TABLE mes_md_workstation_tool;
|
TRUNCATE TABLE mes_md_workstation_worker;
|
TRUNCATE TABLE mes_md_workstation;
|
TRUNCATE TABLE mes_md_workshop;
|
|
-- ============================================================
|
-- 10. MES 基础数据 — 班组/排班
|
-- ============================================================
|
TRUNCATE TABLE mes_cal_plan_team;
|
TRUNCATE TABLE mes_cal_plan_shift;
|
TRUNCATE TABLE mes_cal_plan;
|
TRUNCATE TABLE mes_cal_team_shift;
|
TRUNCATE TABLE mes_cal_team_member;
|
TRUNCATE TABLE mes_cal_team;
|
TRUNCATE TABLE mes_cal_holiday;
|
|
-- ============================================================
|
-- 11. MES 基础数据 — 设备/工具台账
|
-- ============================================================
|
TRUNCATE TABLE mes_dv_check_plan_subject;
|
TRUNCATE TABLE mes_dv_check_plan_machinery;
|
TRUNCATE TABLE mes_dv_check_plan;
|
TRUNCATE TABLE mes_dv_machinery;
|
TRUNCATE TABLE mes_dv_machinery_type;
|
TRUNCATE TABLE mes_dv_subject;
|
TRUNCATE TABLE mes_tm_tool;
|
TRUNCATE TABLE mes_tm_tool_type;
|
|
-- ============================================================
|
-- 12. MES 基础数据 — 工艺路线/工序/参数
|
-- ============================================================
|
TRUNCATE TABLE mes_pro_route_product_bom;
|
TRUNCATE TABLE mes_pro_route_product;
|
TRUNCATE TABLE mes_pro_route_process;
|
TRUNCATE TABLE mes_pro_route;
|
TRUNCATE TABLE mes_pro_process_content;
|
TRUNCATE TABLE mes_pro_process;
|
TRUNCATE TABLE mes_pd_process_param_template_detail;
|
TRUNCATE TABLE mes_pd_process_param_template;
|
TRUNCATE TABLE mes_pd_process_param;
|
|
-- ============================================================
|
-- 13. MES 基础数据 — 质检方案/缺陷/指标
|
-- ============================================================
|
TRUNCATE TABLE mes_qc_template_item;
|
TRUNCATE TABLE mes_qc_template_indicator;
|
TRUNCATE TABLE mes_qc_template;
|
TRUNCATE TABLE mes_qc_defect;
|
TRUNCATE TABLE mes_qc_indicator;
|
|
-- ============================================================
|
-- 14. MES 基础数据 — 仓库/库区/库位
|
-- ============================================================
|
TRUNCATE TABLE mes_wm_warehouse_location;
|
TRUNCATE TABLE mes_wm_warehouse_area;
|
TRUNCATE TABLE mes_wm_warehouse;
|
|
-- ============================================================
|
-- 15. MES 基础配置 — 编码记录/条码/安灯配置
|
-- ============================================================
|
TRUNCATE TABLE mes_wm_barcode_config;
|
TRUNCATE TABLE mes_pro_andon_config;
|
|
-- ============================================================
|
-- 16. MDM 主数据
|
-- ============================================================
|
TRUNCATE TABLE mdm_item_sku;
|
TRUNCATE TABLE mdm_item_migration_map;
|
TRUNCATE TABLE mdm_item_batch_config;
|
TRUNCATE TABLE mdm_item;
|
TRUNCATE TABLE mdm_item_category;
|
TRUNCATE TABLE mdm_brand;
|
TRUNCATE TABLE mdm_unit_measure;
|
TRUNCATE TABLE mdm_warehouse;
|
|
-- ============================================================
|
-- 17. CRM 模块 — 业务单据 + 客户 + 配置
|
-- ============================================================
|
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;
|
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_contact;
|
TRUNCATE TABLE crm_follow_up_record;
|
TRUNCATE TABLE crm_customer;
|
TRUNCATE TABLE crm_permission;
|
TRUNCATE TABLE crm_business_status;
|
TRUNCATE TABLE crm_business_status_type;
|
TRUNCATE TABLE crm_contract_config;
|
TRUNCATE TABLE crm_customer_limit_config;
|
TRUNCATE TABLE crm_customer_pool_config;
|
|
-- ============================================================
|
-- 18. ERP 模块 — 业务单据 + 供应商 + 结算账户
|
-- ============================================================
|
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_supplier;
|
TRUNCATE TABLE erp_account;
|
|
-- ============================================================
|
-- 19. SRM 模块 — 业务单据 + 供应商 + 配置
|
-- ============================================================
|
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_contact;
|
TRUNCATE TABLE srm_supplier_apply;
|
TRUNCATE TABLE srm_supplier_category;
|
TRUNCATE TABLE srm_supplier;
|
TRUNCATE TABLE srm_tender_config;
|
|
-- ============================================================
|
-- 20. HRM 模块 — 业务单据 + 配置
|
-- ============================================================
|
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;
|
TRUNCATE TABLE hrm_employee_work_history;
|
TRUNCATE TABLE hrm_employee_education;
|
TRUNCATE TABLE hrm_employee_emergency_contact;
|
TRUNCATE TABLE hrm_employee_social_security;
|
TRUNCATE TABLE hrm_employee_salary;
|
TRUNCATE TABLE hrm_employee;
|
TRUNCATE TABLE hrm_attendance_holiday;
|
TRUNCATE TABLE hrm_attendance_rule;
|
TRUNCATE TABLE hrm_leave_type_config;
|
TRUNCATE TABLE hrm_salary_structure;
|
TRUNCATE TABLE hrm_social_security_scheme;
|
TRUNCATE TABLE hrm_tax_rate_config;
|
|
-- ============================================================
|
-- 21. BPM 模块 — 业务数据 + 流程定义
|
-- ============================================================
|
TRUNCATE TABLE bpm_oa_leave;
|
TRUNCATE TABLE bpm_process_instance_copy;
|
TRUNCATE TABLE bpm_process_definition_info;
|
TRUNCATE TABLE bpm_process_expression;
|
TRUNCATE TABLE bpm_process_listener;
|
TRUNCATE TABLE bpm_user_group;
|
TRUNCATE TABLE bpm_form;
|
TRUNCATE TABLE bpm_category;
|
|
-- ============================================================
|
-- 22. AI 模块 — 数据 + 配置
|
-- ============================================================
|
TRUNCATE TABLE ai_chat_message;
|
TRUNCATE TABLE ai_chat_conversation;
|
TRUNCATE TABLE ai_knowledge_segment;
|
TRUNCATE TABLE ai_knowledge_document;
|
TRUNCATE TABLE ai_knowledge;
|
TRUNCATE TABLE ai_image;
|
TRUNCATE TABLE ai_mind_map;
|
TRUNCATE TABLE ai_music;
|
TRUNCATE TABLE ai_write;
|
TRUNCATE TABLE ai_workflow;
|
TRUNCATE TABLE ai_api_key;
|
TRUNCATE TABLE ai_model;
|
TRUNCATE TABLE ai_tool;
|
TRUNCATE TABLE ai_chat_role;
|
|
-- ============================================================
|
-- 23. IM 模块 — 数据 + 配置
|
-- ============================================================
|
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_sensitive_word;
|
TRUNCATE TABLE im_face_user_item;
|
TRUNCATE TABLE im_face_pack_item;
|
TRUNCATE TABLE im_face_pack;
|
|
-- ============================================================
|
-- 24. BI 报表配置
|
-- ============================================================
|
TRUNCATE TABLE bi_warehouse_coordinate;
|
TRUNCATE TABLE bi_chart_config;
|
TRUNCATE TABLE bi_dashboard;
|
TRUNCATE TABLE bi_data_source_config;
|
|
-- ============================================================
|
-- 25. 其他模块数据
|
-- ============================================================
|
TRUNCATE TABLE oa_notice_read;
|
TRUNCATE TABLE oa_notice_user;
|
TRUNCATE TABLE oa_notice;
|
TRUNCATE TABLE system_notice;
|
TRUNCATE TABLE system_notify_template;
|
TRUNCATE TABLE system_mail_log;
|
TRUNCATE TABLE system_mail_template;
|
TRUNCATE TABLE system_mail_account;
|
TRUNCATE TABLE system_sms_code;
|
TRUNCATE TABLE system_sms_log;
|
TRUNCATE TABLE system_sms_template;
|
TRUNCATE TABLE system_sms_channel;
|
TRUNCATE TABLE system_social_user_bind;
|
TRUNCATE TABLE system_social_user;
|
TRUNCATE TABLE system_social_client;
|
TRUNCATE TABLE report_go_view_project;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
SELECT '业务数据清理完成' AS result;
|
END$$
|
|
DELIMITER ;
|
|
-- ============================================================
|
-- 保留的表（不会被清理）：
|
--
|
-- 【系统核心】
|
-- system_users / system_dept / system_post
|
-- system_role / system_role_menu
|
-- system_menu
|
-- system_dict_type / system_dict_data
|
-- system_tenant / system_tenant_package
|
-- system_user_post / system_user_role
|
-- system_oauth2_client
|
-- system_notify_template → 已清理
|
--
|
-- 【基础设施】
|
-- infra_config / infra_job
|
-- infra_codegen_table / infra_codegen_column
|
-- infra_data_source_config / infra_file_config
|
--
|
-- 【工作流引擎】
|
-- act_* (Activiti 引擎表)
|
-- flw_* (Flowable 引擎表)
|
--
|
-- 【定时任务】
|
-- qrtz_* (Quartz 引擎表)
|
--
|
-- 【编码规则（保留）】
|
-- mes_md_auto_code_rule
|
-- mes_md_auto_code_part
|
--
|
-- 【示例表】
|
-- yudao_demo01_contact
|
-- yudao_demo02_category
|
-- yudao_demo03_course / yudao_demo03_grade / yudao_demo03_student
|
-- ============================================================
|
|
-- 执行：CALL sp_clean_business_data();
|
