修改MySQL varchar类型字段的排序规则

用户3401793515085
594 阅读4分钟

MySQL 8.0 默认的排序规则为 utf8mb4_0900_ai_ci，使用脚本还原的表的排序规则可能是 utf8mb4_general_ci，之后又自己在库中建的表是 utf8mb4_0900_ai_ci，于是库中存在这两种排序规则，在做关联查询时就会报错。

解决方案 将库中所有表的排序规则改为一致，此处演示将

utf8mb4_0900_ai_ci

批量改为

utf8mb4_general_ci

生成修改脚本

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 = 'LT_PMP_Dev' #要修改的数据库名称
	and DATA_TYPE = 'varchar'
	and COLLATION_NAME='utf8mb4_0900_ai_ci'

生成的 SQL 语句如下：

ALTER TABLE `project_list` MODIFY `notice` varchar(800) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '项目公告';
ALTER TABLE `project_list` MODIFY `tenant_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '租户号';
ALTER TABLE `project_member` MODIFY `role` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '项目角色';
ALTER TABLE `project_plan` MODIFY `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划名称';
ALTER TABLE `project_plan` MODIFY `status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '计划状态';

执行上面的 SQL 就好了。

如果存在外键： 注意：如果表中有外键的话会执行失败，这就比较麻烦了，删除外键重建吧，或者导出建表 SQL 修改建表语句的排序规则重样的建表还原数据。 ———————————————— 版权声明：本文为CSDN博主「ifu25」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：blog.csdn.net/ifu25/artic…

由于需要修改的字段太多了，手工修改肯定是费时费力的。自然也想到了用脚本的方式批量修改，但是发现这种通过查找MySQL信息表、过滤、拼接生成批量修改的语句太好用了，而且还能做到针对varchar类型。 用法是：先执行如下脚本生成修改数据表和表字段的脚本，然后再执行这些生成的脚本。

1 修改指定数据库中所有varchar类型的表字段的字符集为UTF8，并将排序规则修改为utf8_general_ci 更改表编码(字符集)和表中所有字段的编码(字符集)

ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2 查询某个数据库所有表名的语句

SELECT TABLE_NAME from information_schema.`TABLES` WHERE TABLE_SCHEMA = 'DATABASE_NAME';

3、得到所有的表名，我们可以把表名拼接到上面更改表编码(字符集)和表中所有字段的编码(字符集)的语句中去，得到如下语句 直接使用以下SQL即可，然后把结果集再执行

SELECT
    CONCAT(
        'ALTER TABLE ',
        TABLE_NAME,
        ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
    )
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'DATABASE_NAME';

4、把语句执行得到的结果集复制出来就是批量更新的语句，在数据库中执行即可

第二种

用法是：先执行如下脚本生成修改数据表和表字段的脚本，然后再执行这些生成的脚本。

  1. 修改指定数据库中所有varchar类型的表字段的字符集为UTF8，并将排序规则修改为utf8_general_ci
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET 字符集 COLLATE 排序规则', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != '字符集'
    OR
    COLLATION_NAME != '排序规则'
);
  1. 修改指定数据库中所有数据表的字符集为UTF8，并将排序规则修改为utf8_general_ci

SELECT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' CONVERT TO CHARACTER SET 字符集 COLLATE 排序规则;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '表名'

第三种

网上搜到的解决办法，都提到了修改数据表级别collation排序规则。但是我遇到的场景是数据表级别已经是utf8_unicode_ci，而字段级别是utf8_general_ci，（这里我们关心的字段类型是varchar）。

由于需要修改的字段太多了，手工修改肯定是费时费力的。自然也想到了用脚本的方式批量修改，但是发现这种通过查找MySQL信息表、过滤、拼接生成批量修改的语句太好用了，而且还能做到针对varchar类型。

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_unicode_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'database'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_unicode_ci'
);

database需要改成实际数据库名字。需要注意的是，如果要修改的字段存在外键关系，那就要小心处理，删除外键，修改collation后再把外键关系加回来。 ————————————————

原文链接： blog.csdn.net/weixin_3802… blog.csdn.net/weixin_3802… segmentfault.com/u/honwhy

avatar
文章
阅读
粉丝