# MES 业务数据清理 SQL

## 清理范围

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

### 清理分类

| 分类 | 涉及表 | 说明 |
|------|--------|------|
| 库存事务/占用 | transaction, stock_reserve | 生产相关 biz_type |
| 物料消耗单 | item_consume + 行/明细 | 倒冲消耗记录 |
| 产出单/入库单 | product_produce, product_receipt + 行/明细 | 生产产出 |
| 退料单 | return_issue + 行/明细 | 生产退料 |
| 领料出库单 | product_issue + 行/明细 | 生产领料 |
| 质检单 | ipqc, rqc, iqc, oqc + 行/缺陷/指标 | 质检记录 |
| 报工 | feedback | 生产报工 |
| 任务投料 | task_issue | 投料记录 |
| 生产任务 | task | 排产任务 |
| 工单 | work_order + bom + 参数记录 | 生产工单 |
| 流转卡 | card + 工序记录 | 生产流转卡 |
| 安灯 | andon_config, andon_record | 安灯呼叫 |
| 上下工 | work_record, work_record_log | 工作站绑定/上下工 |
| 工艺路线 | route + 工序/产品/BOM | 工艺路线及关联 |
| 工序 | process + 内容 | 生产工序 |
| 产品BOM/SIP/SOP | product_bom, product_sip, product_sop | 产品文档 |
| 物料 | item + 分类/批次配置 | MES 物料 |
| 计量单位 | unit_measure | MES 计量单位 |
| 客户/供应商 | client, vendor | MES 客户/供应商 |
| 车间/工作站 | workshop, workstation + 资源 | MES 车间/工作站 |
| 编码规则 | auto_code_rule + 组成/记录 | 编码规则 |

## 当前数据量

| 表 | 记录数 | 表 | 记录数 |
|----|--------|----|--------|
| mes_pro_work_order | 2 | mes_pro_task | 3 |
| mes_pro_feedback | 13 | mes_pro_task_issue | 0 |
| mes_pro_process | 6 | mes_pro_route | 2 |
| mes_pro_route_process | 5 | mes_pro_route_product | 2 |
| mes_pro_route_product_bom | 6 | mes_pro_work_order_bom | 3 |
| mes_wm_product_issue | 4 | mes_wm_return_issue | 3 |
| mes_wm_item_consume | 5 | mes_wm_transaction(生产) | 9 |
| mes_wm_stock_reserve | 1 | mes_md_item | 9 |
| mes_md_product_bom | 11 | mes_md_workstation | 1 |
| mes_md_unit_measure | 2 | mes_md_item_type | 5 |

## 清理 SQL

```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

```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 生产相关