MySQL8 批量修改字符集脚本
作者:wzy0623 时间:2024-01-16 12:50:34
从低版本迁移到MySQL 8后,可能由于字符集问题出现 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) 错误,此时要修改对象的字符集。
1. 批量修改库字符集
change_database_characset.sql
select concat('alter database ',schema_name,' default character set utf8mb4 collate utf8mb4_0900_ai_ci;')
from information_schema.schemata
where schema_name not in ('sys','mysql','performance_schema','information_schema')
and lower(default_collation_name) in ('utf8mb4_general_ci','utf8_general_ci');
调用:
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -N < change_database_characset.sql > change_database_characset_result.sql
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -f < change_database_characset_result.sql > change_database_characset_result.out 2>&1
2. 批量修改表字符集
change_table_characset.sql
select concat('alter table ',table_schema,'.',table_name,' default character set utf8mb4 collate = utf8mb4_0900_ai_ci;')
from information_schema.tables where table_schema not in ('sys','mysql','performance_schema','information_schema')
and table_type='BASE TABLE' and lower(table_collation) in ('utf8mb4_general_ci','utf8_general_ci');
调用:
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -N < change_table_characset.sql > change_table_characset_result.sql
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -f < change_table_characset_result.sql > change_table_characset_result.out 2>&1
3. 批量修改列字符集
change_column_characset.sql
set group_concat_max_len=10240;
select concat(c1,c2,';')
from (select c1, group_concat(c2) c2
from (select concat('alter table ',t1.table_schema,'.',t1.table_name) c1,concat(' modify ','`',t1.column_name,'` ',t1.data_type,
if (t1.data_type in ('varchar','char'),concat('(',t1.character_maximum_length,')'),''),
' character set utf8mb4 collate utf8mb4_0900_ai_ci',if(t1.is_nullable='NO',' not null',' null'),' comment ','''',t1.column_comment,'''') c2
from information_schema.columns t1, information_schema.tables t2
where t1.table_schema=t2.table_schema and t1.table_name=t2.table_name and t2.table_type='BASE TABLE'
and lower(t1.collation_name) in ('utf8mb4_general_ci','utf8_general_ci') and t1.table_schema not in ('sys','mysql','performance_schema','information_schema')) t1
group by c1) t;
调用:
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -N < change_column_characset.sql > change_column_characset_result.sql
/home/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql -uroot -h10.0.0.18 -P3306 -p70n6w+1XklMu -f < change_column_characset_result.sql > change_column_characset_result.out 2>&1
来源:https://wxy0327.blog.csdn.net/article/details/128670848
标签:MySQL8,字符集
0
投稿
猜你喜欢
最新anaconda安装配置教程
2021-07-17 05:05:34
python实现自动整理文件
2021-03-04 14:15:46
Python Opencv任意形状目标检测并绘制框图
2023-06-06 19:03:43
go语言base64用法实例
2024-04-26 17:22:04
详解Python中字典的增删改查
2021-09-14 19:53:34
简洁js的隔行变色代码
2008-06-18 18:17:00
基于wxpython开发的简单gui计算器实例
2023-09-09 04:42:25
js,jquery滚动/跳转页面到指定位置的实现思路
2024-04-10 16:19:54
python如何通过实例方法名字调用方法
2021-04-24 09:00:31
Python+Turtle动态绘制一棵树实例分享
2021-10-03 00:33:20
使用Python发送各种形式的邮件的方法汇总
2022-12-21 15:24:46
sqlserver 数据库学习笔记
2011-12-01 08:15:06
asp如何在ADO中客户端利用好缓存技术?
2010-06-17 12:50:00
Web表单设计:表单结构
2011-04-22 12:32:00
使用babel-plugin-import 实现自动按需引入方式
2024-04-27 16:00:42
python实现简单飞机大战小游戏
2023-12-19 06:55:37
python实现sm2和sm4国密(国家商用密码)算法的示例
2021-11-17 08:02:13
程序员的八种境界,你在哪一境?
2022-07-19 11:22:19
计划备份mysql数据库
2009-03-09 14:34:00
二种python发送邮件实例讲解(python发邮件附件可以使用email模块实现)
2022-08-18 14:01:35