MySQL批量修改表及表内字段排序规则举例详解_Mysql

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 '标识';

3、复制执行即可；

补充：mysql批量修改库、表、列的排序规则

1、表字段修改

select
    table_schema '数据库',
    table_name '表',
    column_name '字段',
    character_set_name '原字符集',
    collation_name '原排序规则',
    concat( 'alter table ', table_schema, '.', table_name, ' modify column ', column_name, ' ', column_type, ' character set utf8mb4 collate utf8mb4_general_ci;' ) '修正sql' 
from
    information_schema.`columns` 
where
    collation_name rlike 'utf8';

utf8 是模糊匹配排序规则，这里需要替换为你数据库中需要替换的字段的排序规则，

utf8mb4设置的是替换的字符集，

utf8mb4_general_ci设置的是替换的排序规则，

上述语句可以增加table_schema='数据库名'，来限定数据库。

把修正sql复制出来直接运行即可。

2、表修改

select
    table_schema '数据库',
    table_name '表',
    table_collation '原排序规则',
    concat( 'alter table ', table_schema, '.', table_name, ' collate=utf8mb4_general_ci;' ) '修正sql' 
from
    information_schema.`tables` 
where
    table_collation rlike 'utf8';

把修正sql 复制出来直接运行即可。

3、数据库修改

#修改数据库
select
    schema_name '数据库',
    default_character_set_name '原字符集',
    default_collation_name '原排序规则',
    concat( 'alter database ', schema_name, ' character set utf8mb4 collate utf8mb4_general_ci;' ) '修正sql' 
from
    information_schema.`schemata` 
where
    default_character_set_name rlike 'utf8';

把修正sql 复制出来直接运行即可。