Mysql向TiDB迁移时发现不支持 'utf8mb4_german2_ci'排序规则,为解决此问题需修改Mysql表及表内字段排序规则,具体操作如下:
一、表排序规则修改
1、生成修改表排序规则的SQL语句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
' COLLATE utf8mb4_general_ci;') AS '修正SQL'
from information_schema.tables
where TABLE_SCHEMA = '数据库名'
and TABLE_COLLATION = 'utf8mb4_german2_ci';
2、生成的 SQL 语句如下:
ALTER TABLE 数据库名.表名 COLLATE utf8mb4_general_ci;
3、复制执行即可;
二、表内字段排序规则修改
1、生成修改字段排序规则的SQL语句
SELECT
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE,
'(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
(case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
';') AS `修正SQL`
FROM information_schema.COLUMNS
WHERE 1=1
and TABLE_SCHEMA = '数据库名' #要修改的数据库名称
and DATA_TYPE IN ('varchar','char')
and COLLATION_NAME='utf8mb4_german2_ci'
2、生成的 SQL 语句如下:
ALTER TABLE `t_categories` MODIFY `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标识';