clean-us-business-data.sql
4.68 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- =============================================================================
-- 美国版 · 业务数据清理脚本（标签 / 打印日志 / 报表统计源数据 / 门店组织）
-- =============================================================================
-- 【警告】本脚本会永久删除下列范围内的数据，不可恢复！
-- ✓ 清空：打印日志、标签主数据、模板、产品、门店、Partner/Region、用户-门店绑定
-- ✗ 保留：user / role / userrole / userpost / menu / rolemenu（账号与权限菜单）
--
-- 说明：
-- - 「统计 / Label Report」数据来自 fl_label_print_task 聚合，无独立统计表
-- - 执行前请备份数据库；建议在测试环境先跑一遍
-- - MySQL 5.7+ / 8.0+
--
-- 用法（示例）：
-- mysql -h127.0.0.1 -P3306 -uroot -p your_database < clean-us-business-data.sql
-- 或配合同目录 PowerShell： .\run-clean-us-business-data.ps1
-- =============================================================================
SET NAMES utf8mb4;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;
-- 可选：指定库名（若已在 mysql 客户端 USE 过库，可注释掉下一行）
-- USE food_labeling_us;
START TRANSACTION;
-- ---------------------------------------------------------------------------
-- 0) 清理前行数（便于对比）
-- ---------------------------------------------------------------------------
SELECT '=== BEFORE ===' AS phase;
SELECT 'fl_label_print_data' AS tbl, COUNT(*) AS cnt FROM fl_label_print_data
UNION ALL SELECT 'fl_label_print_task', COUNT(*) FROM fl_label_print_task
UNION ALL SELECT 'fl_label', COUNT(*) FROM fl_label
UNION ALL SELECT 'fl_label_template', COUNT(*) FROM fl_label_template
UNION ALL SELECT 'fl_product', COUNT(*) FROM fl_product
UNION ALL SELECT 'location', COUNT(*) FROM location
UNION ALL SELECT 'userlocation', COUNT(*) FROM userlocation
UNION ALL SELECT 'fl_partner', COUNT(*) FROM fl_partner
UNION ALL SELECT 'fl_group', COUNT(*) FROM fl_group;
-- ---------------------------------------------------------------------------
-- 1) 打印日志（报表 / Print Log / Label Report 的数据源）
-- ---------------------------------------------------------------------------
DELETE FROM fl_label_print_data;
DELETE FROM fl_label_print_task;
-- ---------------------------------------------------------------------------
-- 2) 标签业务（分类、类型、模板、多选项、标签实例）
-- ---------------------------------------------------------------------------
DELETE FROM fl_label_product;
DELETE FROM fl_label;
DELETE FROM fl_label_template_element;
DELETE FROM fl_label_template_product_default;
DELETE FROM fl_label_template_location;
DELETE FROM fl_label_template;
DELETE FROM fl_label_type_location;
DELETE FROM fl_label_type;
DELETE FROM fl_label_category_location;
DELETE FROM fl_label_category;
DELETE FROM fl_label_multiple_option_location;
DELETE FROM fl_label_multiple_option;
-- ---------------------------------------------------------------------------
-- 3) 产品（Menu Management）
-- ---------------------------------------------------------------------------
DELETE FROM fl_location_product;
DELETE FROM fl_product_category_location;
DELETE FROM fl_product;
DELETE FROM fl_product_category;
-- ---------------------------------------------------------------------------
-- 4) 门店与支持信息
-- ---------------------------------------------------------------------------
DELETE FROM fl_location_support;
DELETE FROM userlocation;
DELETE FROM location;
-- ---------------------------------------------------------------------------
-- 5) 组织（Company / Region）— 先 Region 再 Partner（存在 FK_fl_group_partner）
-- ---------------------------------------------------------------------------
DELETE FROM fl_group;
DELETE FROM fl_partner;
-- ---------------------------------------------------------------------------
-- 6) 清理后行数
-- ---------------------------------------------------------------------------
SELECT '=== AFTER (expect 0) ===' AS phase;
SELECT 'fl_label_print_data' AS tbl, COUNT(*) AS cnt FROM fl_label_print_data
UNION ALL SELECT 'fl_label_print_task', COUNT(*) FROM fl_label_print_task
UNION ALL SELECT 'fl_label', COUNT(*) FROM fl_label
UNION ALL SELECT 'fl_label_template', COUNT(*) FROM fl_label_template
UNION ALL SELECT 'fl_product', COUNT(*) FROM fl_product
UNION ALL SELECT 'location', COUNT(*) FROM location
UNION ALL SELECT 'userlocation', COUNT(*) FROM userlocation
UNION ALL SELECT 'fl_partner', COUNT(*) FROM fl_partner
UNION ALL SELECT 'fl_group', COUNT(*) FROM fl_group;
COMMIT;
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SELECT '=== DONE ===' AS phase, NOW() AS finished_at;