不懂 2019-11-04
前段时间参考网上教程用脚本批量修改了数据库的字符集,过了一天后业务反馈某个功能用不了,检查发现数据库字符类型的字段的默认值和说明全部为空,用实验测试了一下果然脚本存在一些bug,后续因此加班了一整晚,印象深刻,这里介绍一下实验过程。
先建一个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
注意上面的字段类型,后面做对比。
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;
测试一下NAVICAT的结构同步是否有这种情况
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方面的内容,感兴趣的朋友可以关注下~
echo 1. 使用当前日期命名备份文件。echo 2. 自动删除7天前的备份。forfiles /p "%LOGDIR%" /s /m *.* /d -7 /c "cmd /c del @path"