记一次生产数据库批量修改表字段、表、数据库字符集和排序规则

Dennist 2019-11-01

概述

目前数据库字符集统一用的utf8,由于项目需要,引进了表情,但是utf8mb5才支持表情字符,所以需统一修改数据库字符集,下面介绍批量修改数据库字符集的办法。

修正顺序是字段级别>表级别>库级别。


一、批量修改整个数据库字符集(字段级别)

统一将字符字符集变成utf8_general_ci

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_SCHEMA = 'lcpdb1' 
 AND CHARACTER_SET_NAME = 'utf8';

utf8是我demo的模糊匹配排序规则,这里需要替换为你数据库中需要替换的字段的排序规则,utf8mb4设置的是替换的字符集,utf8mb4_general_ci设置的是替换的排序规则,可以换为需要修正为什么标准

记一次生产数据库批量修改表字段、表、数据库字符集和排序规则


二、批量修改整个数据库字符集(表级别)

1、创建批量修改语句

修改单个表的字符集的命令是:alter table 表名 convert to character set utf8mb4 collate utf8mb4_bin;

创建修改语句的命令是:

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_SCHEMA = 'lcpdb1' 
 AND TABLE_COLLATION = 'utf8_general_ci';

记一次生产数据库批量修改表字段、表、数据库字符集和排序规则

将上面的修正SQL语句批量复制,然后全部执行。


三、批量修改多个数据库字符集(库级别)

1、找出MySQL实例中字符集不是utf8mb4的数据库

SELECT * FROM information_schema.`SCHEMATA` WHERE DEFAULT_COLLATION_NAME<>'utf8_general_ci';

记一次生产数据库批量修改表字段、表、数据库字符集和排序规则

2、批量修改

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='utf8';

记一次生产数据库批量修改表字段、表、数据库字符集和排序规则


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

记一次生产数据库批量修改表字段、表、数据库字符集和排序规则

相关推荐