mysql 批量修改 表字段/表/数据库 字符集和排序规则

 

今天接到一个任务是需要把数据库的字符编码全部修改一下,写了以下修正用的SQL,修正顺序是   表字段 > 表 > 数据库。

 

表字段修复:

#改变字段数据
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '',COLUMN_NAME '字段',CHARACTER_SET_NAME '原字符集',COLLATION_NAME '原排序规则',CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`COLUMNS` 
WHERE COLLATION_NAME RLIKE 'latin1';

latin1 是我demo的模糊匹配排序规则,这里需要替换为你数据库中需要替换的字段的排序规则,utf8mb4设置的是替换的字符集,utf8mb4_general_ci设置的是替换的排序规则,可以换为需要修正为什么标准,下面是SQL跑出来的样例。

把修正的SQL复制出来运行,字段标准就修复了。

ALTER TABLE scm_users.users MODIFY COLUMN password varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN address varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN country varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN postal_code varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN contact_person varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN contact_no varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN mobile varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN email varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact MODIFY COLUMN remark text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse MODIFY COLUMN warehouse_no varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse MODIFY COLUMN name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse_category MODIFY COLUMN name varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse_op_hours MODIFY COLUMN start_time varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse_op_hours MODIFY COLUMN end_time varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 

表修复:

#改变表
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES`
WHERE TABLE_COLLATION RLIKE 'latin1';

表修复只需要设置排序规则,字符集会自动设置到正确的标准,替换的话,跟上面一样,下面是SQL跑出来的样例。

同样的,把SQL复制出来运行,表就会修复了。

ALTER TABLE scm_procurement.receiving_report COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_procurement.receiving_report_detail COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_sku.sku COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_sku.sku_category COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_sku.sku_secondary_uom COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_sku.sku_sub_category COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_sku.uom COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_users.department COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_users.role COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_users.users COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_warehouse.contact COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse_category COLLATE=utf8mb4_general_ci;
ALTER TABLE scm_warehouse.warehouse_op_hours COLLATE=utf8mb4_general_ci;
ALTER TABLE tscg.fim_data COLLATE=utf8mb4_general_ci;

 

数据库修复:

#修改数据库
SELECT SCHEMA_NAME '数据库',DEFAULT_CHARACTER_SET_NAME '原字符集',DEFAULT_COLLATION_NAME '原排序规则',CONCAT('ALTER DATABASE ',SCHEMA_NAME,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`SCHEMATA`
WHERE DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8';

数据库修复,跟字段修复一样,需要设置字符集和排序规则,这里utf8是模糊匹配需要修正的数据库的字符集,也可以用DEFAULT_COLLATION_NAME 排序规则来筛选,这些都很灵活,因为我们工程是分库的,所以没有匹配数据库

如果需要选定到数据库,就用SCHEMA_NAME 匹配一下就行了,下面是SQL跑出来的样例。

 

一样的,SQL复制出来运行,数据库就也修复了。

ALTER DATABASE scm CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE scm_company CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE scm_procurement CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE scm_sku CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE scm_users CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE scm_warehouse CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER DATABASE tscg CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

 

 如果大家有什么不解,或意见,欢迎在下方留言,楼主看到就会回复的,谢谢。

posted @ 2019-04-26 18:50  一角01  阅读(9090)  评论(1编辑  收藏  举报