修复排序规则冲突_精简版.sql
2.46 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
-- 修复排序规则冲突 - 精简版
-- 只更新报错的相关表
-- 1. 统一 BASE_USER 表的所有字段排序规则
ALTER TABLE BASE_USER CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 2. 统一 lq_mdxx 表的所有字段排序规则
ALTER TABLE lq_mdxx CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 3. 统一 lq_zjl_mdsmxsz 表的所有字段排序规则
ALTER TABLE lq_zjl_mdsmxsz CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 4. 统一 lq_ycsd_jsj 表的所有字段排序规则
ALTER TABLE lq_ycsd_jsj CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 5. 统一 lq_kd_jksyj 表的所有字段排序规则
ALTER TABLE lq_kd_jksyj CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 6. 统一 lq_statistics_gold_triangle 表的所有字段排序规则
ALTER TABLE lq_statistics_gold_triangle CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 7. 统一 lq_jinsanjiao_user 表的所有字段排序规则
ALTER TABLE lq_jinsanjiao_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 8. 统一 lq_salary_statistics 表的所有字段排序规则
ALTER TABLE lq_salary_statistics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 9. 统一 lq_kd_pxmx 表的所有字段排序规则
ALTER TABLE lq_kd_pxmx CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 10. 跳过 lq_kd_kdjlb 表（表太大，无法转换）
-- ALTER TABLE lq_kd_kdjlb CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 11. 统一 lq_xmzl 表的所有字段排序规则
ALTER TABLE lq_xmzl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 12. 统一 lq_statistics_personal_performance 表的所有字段排序规则
ALTER TABLE lq_statistics_personal_performance CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 13. 统一 lq_statistics_store_total_performance 表的所有字段排序规则
ALTER TABLE lq_statistics_store_total_performance CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 验证排序规则是否统一
SELECT
TABLE_NAME,
TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('BASE_USER', 'lq_mdxx', 'lq_zjl_mdsmxsz', 'lq_ycsd_jsj', 'lq_kd_jksyj', 'lq_statistics_gold_triangle', 'lq_jinsanjiao_user', 'lq_salary_statistics', 'lq_kd_pxmx', 'lq_xmzl', 'lq_statistics_personal_performance', 'lq_statistics_store_total_performance')
ORDER BY TABLE_NAME;