记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路

不懂 2019-11-04

概述

前段时间参考网上教程用脚本批量修改了数据库的字符集,过了一天后业务反馈某个功能用不了,检查发现数据库字符类型的字段的默认值和说明全部为空,用实验测试了一下果然脚本存在一些bug,后续因此加班了一整晚,印象深刻,这里介绍一下实验过程。


1、创建表

先建一个utf8编码的表:

CREATE TABLE `t1` (
 `ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `USER_CODE` varchar(225) NOT NULL DEFAULT '01112341' COMMENT '用户编码',
 `START_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `NEED_DAYS` decimal(4,1) NOT NULL COMMENT '请假天数',
 `LEAVE_REASON` varchar(255) DEFAULT '事假' COMMENT '请假理由',
 PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路

注意上面的字段类型,后面做对比。


2、用脚本修改字符集

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
 TABLE_NAME = 't1' 
 AND CHARACTER_SET_NAME = 'utf8';
​
--修正脚本:
ALTER TABLE t1.t1 MODIFY COLUMN USER_CODE varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE t1.t1 MODIFY COLUMN LEAVE_REASON varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路


3、查看t1表验证

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路


4、结构同步

测试一下NAVICAT的结构同步是否有这种情况

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路


5、修改脚本如下:

SELECT
 c.TABLE_SCHEMA '数据库',
 c.TABLE_NAME '表',
 c.COLUMN_NAME '字段',
 c.COLUMN_DEFAULT '默认值',
 c.IS_NULLABLE '是否为空',
 c.DATA_TYPE '字段类型',
 c.character_set_name '原字符集',
 c.collation_name '原排序规则',
 CONCAT(
 'ALTER TABLE ',
 TABLE_SCHEMA,
 '.',
 TABLE_NAME,
 ' MODIFY COLUMN ',
 COLUMN_NAME,
 ' ',
 COLUMN_TYPE,
 ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
 CASE

 WHEN c.is_nullable = 'NO' THEN
 'NOT NULL' ELSE 'NULL' 
 END,
 CASE

 WHEN c.COLUMN_DEFAULT = '' THEN
 ' DEFAULT ''''' 
 WHEN c.COLUMN_DEFAULT IS NULL THEN
 ' DEFAULT NULL' ELSE concat( ' DEFAULT ', '''', c.COLUMN_DEFAULT, '''' ) 
 END,
 ' comment ',
 '''',
 c.COLUMN_COMMENT,
 '''',
 ';' 
) '修正SQL' 
FROM
 information_schema.`COLUMNS` c 
WHERE
 table_name = 't1'
 AND CHARACTER_SET_NAME = 'utf8mb4';
​
--修正sql如下:
ALTER TABLE t1.t1 MODIFY COLUMN USER_CODE varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL default '01112341' comment '用户编码';
ALTER TABLE `t2`.`t1` MODIFY COLUMN `USER_CODE` varchar(225) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户编码' AFTER `ID`;
ALTER TABLE `t2`.`t1` MODIFY COLUMN `LEAVE_REASON` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '请假理由' AFTER `NEED_DAYS`;

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路


综上所述:

1、备份重于一切

因为有备份,所以故障发生时我们是直接从前一天拿一份最新的表结构,用文本编辑改utf8为utf8mb4,导到中间库做一下结构同步恢复的。

2、不能盲目相信网上内容

这一次就是因为拿到脚本后只是简单测试编码修改成功,没有进一步验证,忽略了其他地方才导致故障发生。

觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

记一次脚本批量修改数据库字符集所埋下的一个坑及解决思路

相关推荐