MySQL:批量修改表的排序规则

当MySQL8.0数据库中存在utf8mb4_0900_ai_ci和utf8mb4_general_ci两种排序规则时,关联查询会报错。解决方案是批量修改所有表的排序规则为同一类型。可以生成SQL脚本,如ALTER TABLE语句,批量修改表的排序规则。如果涉及外键,可能需要先删除外键约束再进行修改,否则执行会失败。
摘要由CSDN通过智能技术生成

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 修改建表语句的排序规则重样的建表还原数据。

ALTER TABLE ACT_DE_MODEL MODIFY id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
3780 - Referencing column ‘model_id’ and referenced column ‘id’ in foreign key constraint ‘fk_relation_child’ are incompatible.
时间: 0.003s

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值