MySQL8批量修改字符集脚本怎么写
作者:暮色微凉
时间:2023-04-24
来源:正软商城
浏览:5
从低版本迁移到MySQL8后,可能由于字符集问题出现Illegalmixofcollations(utf8mb4_general_ci,IMPLICIT)and(utf8mb4_0900_ai_ci,IMPLICIT)错误,此时要修改对象的字符集。1.批量修改库字符集change_database_characset.sqlselectconcat('alterdatabase',schema_name,'defaultcharactersetutf8mb4collateutf8m
本文目录
从低版本迁移到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 -h20.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 -h20.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 -h20.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 -h20.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 -h20.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 -h20.0.0.18 -P3306 -p70n6w+1XklMu -f < change_column_characset_result.sql > change_column_characset_result.out 2>&1
作者最新文章
抖音搜索app是做什么的 抖音搜索app收费吗
2026-11-19 15:26
腾讯会议本地录制和云录制怎么选 保存路径设置
2026-11-18 17:23
AO3镜像彩云站访问入口及使用指南
2026-11-18 14:16
AO3中文入口及官方镜像链接
2026-11-18 09:24
AO3镜像网址及中文版入口链接
2026-11-17 17:36
热门文章
更多
精品专题
更多
Mac软件
更多
火山引擎 网页版
Windows/ macOS/ Linux
火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力,依托字节自研豆包多模态大模型,凭借火山方舟一站式平台,提供模型精调、推理部署、智能应用搭建等全链路服务,结合高性能AI算力,助力各行业快速落地大模型数字化与智能化应用。
















