概述

前段时间参考网上教程用脚本批量修改了数据库的字符集，过了一天后业务反馈某个功能用不了，检查发现数据库字符类型的字段的默认值和说明全部为空，用实验测试了一下果然脚本存在一些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方面的内容，感兴趣的朋友可以关注下~