修改MySQL数据库的字符集和排序规则

栏目：云苍穹知识作者：金蝶来源：金蝶云社区发布：2024-09-23浏览：2

修改MySQL数据库的字符集和排序规则

苍穹要求使用的mysql数据库的字符集和排序规则是 utf8mb4和utf8mb4_unicode_ci。很多客户或厂商提供的MySQL服务没有按照这个要求配置。导致苍穹使用过程中报错或排序规则不一致时性能卡慢情况，所以需要统一规范数据库的字符集和排序规则。

修改数据库的字符集和排序规则有两种方法：

一是中断所有业务后，需要修改主配置文件，清空数据库后，重新导入导出，方法比较快。

二是在线修改，耗时会比较久。

以下详细讲一下第二种方法，在线修改数据库字符集和排序规则方法。

-- 参数修改

-- 命令行修改，永久生效需要在参数文件中修改

SET character_set_client     = 'utf8mb4';
SET character_set_connection    = 'utf8mb4';
SET character_set_database    = 'utf8mb4';
SET character_set_results     = 'utf8mb4';
SET character_set_server     = 'utf8mb4';
SET collation_connection        = 'utf8mb4_unicode_ci';
SET collation_database        = 'utf8mb4_unicode_ci';
SET collation_server         = 'utf8mb4_unicode_ci';

-- 参数文件修改（需要重启数据库生效）

[client]
init_connect               = 'SET NAMES utf8mb4'
character-set-server           = utf8mb4
default-character-set           = utf8mb4
[mysqld]
init_connect                = 'SET NAMES utf8mb4'
init_connect                = 'SET collation_connection = utf8mb4_unicode_ci'
character-set-server           = utf8mb4
collation-server             = utf8mb4_unicode_ci

-- 数据库级别（新建表生效，已有表不生效）

select concat('alter database ',schema_name,' charset=utf8mb4 collate=utf8mb4_unicode_ci;')
from information_schema.schemata
where schema_name not in ('information_schema','mysql','performance_schema','sys','test');

这个sql语句会找出不是utf8mb4_unicode_ci排序规则的库名，并生成出alter语，然后执行alter语句进行修改

-- 已有表排序规则修改

-- 修改过程会锁表，只能读不能写入，请选择业务低谷期，谨慎操作！！！

SELECT CONCAT('ALTER TABLE ', table_schema,'.',table_name, ' CONVERT TO CHARACTER SET  utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
  and TABLE_COLLATION != 'utf8mb4_unicode_ci';

这个sql语句会找出不是utf8mb4_unicode_ci排序规则的表名，并生成出alter语句，然后等业务低峰期时执行这些alter修改语句进行修改。

-- 已有表中的字段修改

-- 修改过程会锁表，只能读不能写入，请选择业务低谷期，谨慎操作！！！

SELECT CONCAT('ALTER TABLE ',table_schema,'.', table_name, ' MODIFY ', column_name, ' ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),(CASE WHEN COLUMN_DEFAULT is not null THEN concat(' default ''',COLUMN_DEFAULT,'''') ELSE '' END),';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
AND TABLE_NAME not in ('T_ISCB_BILL_LOG_RELATION','T_ISCB_VALUE_CONV_CACHE')
AND DATA_TYPE <> 'longtext'
AND (CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci');

这个sql语句会找出不是utf8mb4_unicode_ci排序规则的字段名，并生成出alter语句，然后等业务低峰期时执行这些alter修改语句进行修改。