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

概述

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