MES 业务数据清理 SQL

清理范围

清理 MES 模块全部业务数据，保留系统配置类数据。按依赖顺序执行。

清理分类

当前数据量

清理 SQL

-- ============================================================
-- MES 全部业务数据清理脚本
-- 执行前请确认：这些数据可以删除，不可恢复！
-- ============================================================

-- ==================== 1. 库存占用（生产相关） ====================
DELETE FROM mes_wm_stock_reserve
WHERE biz_type IN (4, 5);  -- 4=生产领料, 5=生产产出

-- ==================== 2. 库存事务流水（生产相关 biz_type） ====================
-- 116=生产领料, 117=生产退料, 118=生产入库, 123=生产消耗, 124=产品产出
DELETE FROM mes_wm_transaction
WHERE biz_type IN (116, 117, 118, 123, 124);

-- ==================== 3. 物料消耗单 ====================
DELETE FROM mes_wm_item_consume_detail;
DELETE FROM mes_wm_item_consume_line;
DELETE FROM mes_wm_item_consume;

-- ==================== 4. 生产产出单 ====================
DELETE FROM mes_wm_product_produce_detail;
DELETE FROM mes_wm_product_produce_line;
DELETE FROM mes_wm_product_produce;

-- ==================== 5. 生产入库单 ====================
DELETE FROM mes_wm_product_receipt_detail;
DELETE FROM mes_wm_product_receipt_line;
DELETE FROM mes_wm_product_receipt;

-- ==================== 6. 生产退料单 ====================
DELETE FROM mes_wm_return_issue_detail;
DELETE FROM mes_wm_return_issue_line;
DELETE FROM mes_wm_return_issue;

-- ==================== 7. 生产领料出库单 ====================
DELETE FROM mes_wm_product_issue_detail;
DELETE FROM mes_wm_product_issue_line;
DELETE FROM mes_wm_product_issue;

-- ==================== 8. 质检记录 ====================
DELETE FROM mes_qc_indicator_result_detail;
DELETE FROM mes_qc_indicator_result;
DELETE FROM mes_qc_defect_record;
DELETE FROM mes_qc_ipqc_line;
DELETE FROM mes_qc_ipqc;
DELETE FROM mes_qc_rqc_line;
DELETE FROM mes_qc_rqc;
DELETE FROM mes_qc_iqc_line;
DELETE FROM mes_qc_iqc;
DELETE FROM mes_qc_oqc_line;
DELETE FROM mes_qc_oqc;

-- ==================== 9. 生产报工 ====================
DELETE FROM mes_pro_feedback;

-- ==================== 10. 任务投料记录 ====================
DELETE FROM mes_pro_task_issue;

-- ==================== 11. 生产任务 ====================
DELETE FROM mes_pro_task;

-- ==================== 12. 工单参数记录 ====================
DELETE FROM mes_pro_work_order_param_record_detail;
DELETE FROM mes_pro_work_order_param_record;

-- ==================== 13. 工单 BOM ====================
DELETE FROM mes_pro_work_order_bom;

-- ==================== 14. 生产工单 ====================
DELETE FROM mes_pro_work_order;

-- ==================== 15. 流转卡 ====================
DELETE FROM mes_pro_card_process;
DELETE FROM mes_pro_card;

-- ==================== 16. 安灯 ====================
DELETE FROM mes_pro_andon_record;
DELETE FROM mes_pro_andon_config;

-- ==================== 17. 上下工记录 ====================
DELETE FROM mes_pro_work_record_log;
DELETE FROM mes_pro_work_record;

-- ==================== 18. 工艺路线产品 BOM ====================
DELETE FROM mes_pro_route_product_bom;

-- ==================== 19. 工艺路线产品 ====================
DELETE FROM mes_pro_route_product;

-- ==================== 20. 工艺路线工序 ====================
DELETE FROM mes_pro_route_process;

-- ==================== 21. 工艺路线 ====================
DELETE FROM mes_pro_route;

-- ==================== 22. 工序内容 ====================
DELETE FROM mes_pro_process_content;

-- ==================== 23. 生产工序 ====================
DELETE FROM mes_pro_process;

-- ==================== 24. 产品 SOP ====================
DELETE FROM mes_md_product_sop;

-- ==================== 25. 产品 SIP ====================
DELETE FROM mes_md_product_sip;

-- ==================== 26. 产品 BOM ====================
DELETE FROM mes_md_product_bom;

-- ==================== 27. 物料批次配置 ====================
DELETE FROM mes_md_item_batch_config;

-- ==================== 28. MES 物料 ====================
DELETE FROM mes_md_item;

-- ==================== 29. 物料分类 ====================
DELETE FROM mes_md_item_type;

-- ==================== 30. 计量单位 ====================
DELETE FROM mes_md_unit_measure;

-- ==================== 31. 客户 ====================
DELETE FROM mes_md_client;

-- ==================== 32. 供应商 ====================
DELETE FROM mes_md_vendor;

-- ==================== 33. 工作站资源 ====================
DELETE FROM mes_md_workstation_machine;
DELETE FROM mes_md_workstation_tool;
DELETE FROM mes_md_workstation_worker;

-- ==================== 34. 工作站 ====================
DELETE FROM mes_md_workstation;

-- ==================== 35. 车间 ====================
DELETE FROM mes_md_workshop;

-- ==================== 36. 质检方案 ====================
DELETE FROM mes_qc_template_indicator;
DELETE FROM mes_qc_template_item;
DELETE FROM mes_qc_template;

-- ==================== 37. 缺陷类型 ====================
DELETE FROM mes_qc_defect;

-- ==================== 38. 质检指标 ====================
DELETE FROM mes_qc_indicator;

-- ==================== 39. 编码规则 ====================
DELETE FROM mes_md_auto_code_record;
DELETE FROM mes_md_auto_code_part;
DELETE FROM mes_md_auto_code_rule;

验证 SQL

-- 验证清理结果，预期全部为 0
SELECT 'mes_pro_work_order' as tbl, COUNT(*) as cnt FROM mes_pro_work_order WHERE deleted=0
UNION ALL SELECT 'mes_pro_task', COUNT(*) FROM mes_pro_task WHERE deleted=0
UNION ALL SELECT 'mes_pro_feedback', COUNT(*) FROM mes_pro_feedback WHERE deleted=0
UNION ALL SELECT 'mes_pro_task_issue', COUNT(*) FROM mes_pro_task_issue WHERE deleted=0
UNION ALL SELECT 'mes_pro_process', COUNT(*) FROM mes_pro_process WHERE deleted=0
UNION ALL SELECT 'mes_pro_route', COUNT(*) FROM mes_pro_route WHERE deleted=0
UNION ALL SELECT 'mes_pro_route_process', COUNT(*) FROM mes_pro_route_process WHERE deleted=0
UNION ALL SELECT 'mes_pro_route_product', COUNT(*) FROM mes_pro_route_product WHERE deleted=0
UNION ALL SELECT 'mes_pro_route_product_bom', COUNT(*) FROM mes_pro_route_product_bom WHERE deleted=0
UNION ALL SELECT 'mes_wm_product_issue', COUNT(*) FROM mes_wm_product_issue WHERE deleted=0
UNION ALL SELECT 'mes_wm_return_issue', COUNT(*) FROM mes_wm_return_issue WHERE deleted=0
UNION ALL SELECT 'mes_wm_item_consume', COUNT(*) FROM mes_wm_item_consume WHERE deleted=0
UNION ALL SELECT 'mes_wm_product_produce', COUNT(*) FROM mes_wm_product_produce WHERE deleted=0
UNION ALL SELECT 'mes_wm_product_receipt', COUNT(*) FROM mes_wm_product_receipt WHERE deleted=0
UNION ALL SELECT 'mes_md_item', COUNT(*) FROM mes_md_item WHERE deleted=0
UNION ALL SELECT 'mes_md_item_type', COUNT(*) FROM mes_md_item_type WHERE deleted=0
UNION ALL SELECT 'mes_md_product_bom', COUNT(*) FROM mes_md_product_bom WHERE deleted=0
UNION ALL SELECT 'mes_md_unit_measure', COUNT(*) FROM mes_md_unit_measure WHERE deleted=0
UNION ALL SELECT 'mes_md_client', COUNT(*) FROM mes_md_client WHERE deleted=0
UNION ALL SELECT 'mes_md_vendor', COUNT(*) FROM mes_md_vendor WHERE deleted=0
UNION ALL SELECT 'mes_md_workstation', COUNT(*) FROM mes_md_workstation WHERE deleted=0
UNION ALL SELECT 'mes_md_workshop', COUNT(*) FROM mes_md_workshop WHERE deleted=0
UNION ALL SELECT 'mes_wm_stock_reserve(生产)', COUNT(*) FROM mes_wm_stock_reserve WHERE deleted=0 AND biz_type IN (4, 5)
UNION ALL SELECT 'mes_wm_transaction(生产)', COUNT(*) FROM mes_wm_transaction WHERE deleted=0 AND biz_type IN (116, 117, 118, 123, 124);

注意事项

• 以上 SQL 使用物理删除（DELETE），**执行后不可恢复**
• 执行顺序很重要：先删子表/明细，再删主表，避免外键约束报错
• 库存记录(mes_wm_material_stock)不清理：清理事务流水后，库存台账可能有残留数量，如需重置需单独处理
• 仓库/库区/库位(mes_wm_warehouse 等)不清理：基础设施数据保留
• 批次(mes_wm_batch)不清理：批次基础数据保留
• 采购/销售/调拨等其他业务数据不清理：只清理 MES 生产相关