【Mysql 数据库字符集和排序规则设置及批量调整】

一、数据库字符集和排序规则检查

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%'  OR Variable_name LIKE '%collation%';

二、在线设置字符集

--  以字符集utf8mb4 和排序规则utf8mb4_general_ci 为例
-- 如果和目标不符可在线设置，无需重启数据库，需断开重连查看生效状态 （需要super权限）
set global collation_connection=utf8mb4_general_ci;
set global collation_server=utf8mb4_general_ci;
set global collation_database=utf8mb4_general_ci;
set global default_collation_for_utf8mb4=utf8mb4_general_ci;

--  持久化生效语句 重启后依然生效 (mysql 8.0以上)-- 
SET PERSIST default_collation_for_utf8mb4 =utf8mb4_general_ci;
SET PERSIST collation_connection =utf8mb4_general_ci;

三、配置文件设置

– 永久生效，写入配置mysql的配置文件（默认my.cnf)

character-set-server=utf8mb4
character-set-client-handshake = FALSE
collation-server=utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_general_ci'
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_general_ci'

四、调整数据字符集

-- 调整数据库字符集和排序规则  以字符集utf8mb4 和排序规则utf8mb4_general_ci 为例
ALTER DATABASE `库名` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

-- 执行生成批量调整SQL语句   根据实际情况调整where条件
SELECT 
    SCHEMA_NAME '数据库名',
    DEFAULT_CHARACTER_SET_NAME  '原数据库编码',
    DEFAULT_COLLATION_NAME   '原排序规则',
    CONCAT('ALTER DATABASE ',
            SCHEMA_NAME,
            ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修改语句'
FROM
    information_schema.`SCHEMATA`
WHERE
    SCHEMA_NAME NOT IN ('mysql',
        'information_schema',
        'performance_schema',
        'sys');

五、调整表和字段级别字符集和排序规则

-- 以字符集utf8mb4 和排序规则utf8mb4_general_ci 为例
ALTER TABLE  `表名` CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci  ;

-- 批量生成脚本
SELECT 
    TABLE_SCHEMA '数据库',
    TABLE_NAME '表',
    TABLE_COLLATION '原排序规则',
    CONCAT('ALTER TABLE `',
            TABLE_SCHEMA,
            '`.`',
            TABLE_NAME,
            '` CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci;') AS '修改语句'
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA ='库名'
        AND TABLE_TYPE = 'BASE TABLE';

六、针对有外键约束的表进行调整

方式一

 -- 1、删除外键约束
    alter table table drop CONSTRAINT `外键名`;  
    
    -- 2、调整表和字段的字符集和排序规则
    ALTER TABLE  `表名` CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci  ;    
    -- 3、将外键约束加回去
    alter table 表名 add CONSTRAINT `外键名` FOREIGN KEY (`字段1`, `字段2`) REFERENCES `关联表名` (`字段1`, `字段2`) ON DELETE RESTRICT ON UPDATE restrict;

方式二

可以在session中禁用foreign_key_checks执行变更

set foreign_key_checks =0;
ALTER TABLE  `表名1` CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci  ;
ALTER TABLE  `表名2` CONVERT TO CHARACTER  SET utf8mb4 COLLATE utf8mb4_general_ci  ;
set foreign_key_checks =1;

总结

建议在数据库初始化和创建选择合适的字符集和排序规则，针对云RDS 无super 权限的实例在建库和建表是指定相应的字符集和排序规则。

-- 1. 创建数据库或表时指定字符集

create database test character set 'utf8mb4' collate 'utf8mb4_general_ci'; 

create table test.t_tab 
(id int primary key auto_increment,
vname varchar(10) not null default '' collate utf8mb4_general_ci comment '名称',
create_time datetime(3) not null  comment '创建时间'
) comment 'test';