MySQL批量调整字符集与排序规则

一、查看数据库内的字符集

用于查看数据库里字符集情况的操作

-- 查看数据库中的字符集
show variables where Variable_name like '%collation%'

通过上面的操作来确定需要统一成的字符集类型

二、查看特定数据库中表的字符集情况

查看某数据库内表所用字符集的操作

-- 查看某个数据库的表的用的字符集
SELECT TABLE_SCHEMA '数据库',TABLE_NAME '表',TABLE_COLLATION '原排序规则',CONCAT('ALTER TABLE ',TABLE_NAME, ' CHARACTER SET=utf8mb4,  COLLATE=utf8mb4_general_ci;') '修正SQL'
FROM information_schema.`TABLES` where TABLE_SCHEMA = '你的数据库名' and TABLE_COLLATION != 'utf8mb4_general_ci'

三、查看数据库中所有字段的排序规则

查看数据库内所有字段排序规则的操作

-- 查看数据库中所有字段用的排序规则
SELECT
    TABLE_SCHEMA '数据库',
    TABLE_NAME '表',
    COLUMN_NAME '字段',
    DATA_TYPE '字段类型',
    CHARACTER_SET_NAME '原字符集',
    COLLATION_NAME '原排序规则',
    COLUMN_DEFAULT '列的默认值',
    CONCAT(
    'ALTER TABLE ',
    TABLE_NAME,
    ' MODIFY COLUMN ',
    COLUMN_NAME,
    ' ',
    COLUMN_TYPE,
    -- - 设置新的编码和排序规则
    ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
    ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
    ( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
    ( CASE WHEN COLUMN_DEFAULT is NULL THEN ' ' ELSE concat('DEFAULT ''' , COLUMN_DEFAULT, '''') END ),
    ';' 
) '修正SQL' 
FROM
    information_schema.`COLUMNS` 
WHERE
    -- 过滤正确排序规则
    COLLATION_NAME != 'utf8mb4_general_ci'
    -- 数据库名称
    AND TABLE_SCHEMA = 'ms_db'
    -- 修改varcahr和text类型的字段
    -- AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'text' OR DATA_TYPE = 'longtext')

上述SQL能保证在修改字符集和排序规则时，MySQL字段默认值不做改变；要是不保留字段默认值进行SQL修改，会把修改过的字段默认值删掉，这会引发Java代码配合数据库使用时的正确性问题。

上面的SQL可依据自身情况添加where条件来筛选需要操作的表。修改SQL能够直接批量复制后运行，不过执行前最好先备份相关数据

四、修改数据库的字符集及排序规则

修改数据库字符集与排序规则的语句

ALTER DATABASE `shtp_sunhouse` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

存储字符集相关说明

utf8是MySQL里的一种字符集，它仅支持最长三个字节的UTF - 8字符，也就是Unicode中的基本多文本平面。若要在MySQL中保存4字节长度的UTF - 8字符，得使用utf8mb4字符集，但只有5.5.3版本以后才支持。为获取更好的兼容性，应该始终使用utf8mb4而非utf8。对于CHAR类型的数据，utf8mb4会多占用一些空间，依据MySQL官方建议，用VARCHAR替代CHAR。一般现在新建的数据库大多是8.0以上版本，都使用utf8mb4就行。

排序字符集相关说明

utf8mb4_unicode_ci和utf8mb4_general_ci

1. 准确性方面
2. utf8mb4_unicode_ci是基于标准的Unicode来进行排序和比较的，能够在各种语言之间精准排序。
3. utf8mb4_general_ci没有实现Unicode排序规则，在碰到某些特殊语言或者字符集时，排序结果可能不一致。
4. 性能方面
5. utf8mb4_general_ci在比较和排序时更快。
6. utf8mb4_unicode_ci在特殊情况下，由于Unicode排序规则要处理特殊字符的情况，实现了相对复杂一点的排序算法。

utf8mb4_0900_ai_ci

推荐使用utf8mb4_unicode_ci，不过用utf8mb4_general_ci也没什么大问题。

MySQL 8.0默认的是utf8mb4_0900_ai_ci，它属于utf8mb4_unicode_ci中的一种，具体含义如下：
- uft8mb4表示采用UTF - 8编码方案，每个字符最多占4个字节。
- 0900指的是Unicode校对算法版本（Unicode归类算法是用于比较符合Unicode标准要求的两个Unicode字符串的方法）。
- ai表示口音不敏感，也就是排序时e，è，é，ê和ë之间没有差别。
- ci表示不区分大小写，即排序时p和P之间没有差别。

默认字符集情况

utf8mb4已成为默认字符集，在MySQL 8.0.1及更高版本中把utf8mb4_0900_ai_ci作为默认排序规则。之前，utf8mb4_general_ci是默认排序规则。由于utf8mb4_0900_ai_ci排序规则现在是默认的，所以默认情况下新表格能够存储基本多语言平面之外的字符，现在可以默认存储表情符号了。要是需要重音灵敏度和区分大小写，能够用utf8mb4_0900_as_cs来代替。