MYSQL8.0 修改字符集和排序规则-企业实战

#MYSQL8.0 修改字符集和排序规则

由于MYSQL8.X 的排序规则从默认5.7的utf8_general_ci 修改为：utf8mb4_0900_ai_ci， 所以不管是MYSQL5.7迁移还是其他的操作导致排序规则更改都需要更改, 要不然会导致表关联join出错.

#查询非utf8mb4_0900_ai_ci排序规则

select CONCAT(
		'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,
    '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
    ' CHARACTER SET ', CHARACTER_SET_NAME,
    ' COLLATE utf8mb4_0900_ai_ci',
    CASE IS_NULLABLE WHEN 'YES' THEN ' NULL' ELSE ' NOT NULL' END,
    CASE WHEN COLUMN_DEFAULT IS NULL THEN '' ELSE CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\'') END,
    ' COMMENT \'', COLUMN_COMMENT, '\'',
    ';'
)
from information_schema.COLUMNS
where TABLE_SCHEMA in ('DATABASE数据库1', 'DATABASE数据库2', 'DATABASE数据库3') 
and COLLATION_NAME <> 'utf8mb4_0900_ai_ci';

#修改非utf8mb4_0900_ai_ci排序规则

ALTER TABLE `DATABASE数据库`.`TABLE表名` MODIFY COLUMN `FILED字段名` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '';

#参考语句

SHOW VARIABLES like "%coll%"
show create database  DATABASE数据库;
show create table TABLE表名
SHOW FULL COLUMNS FROM TABLE表名