从低版本迁移到MySQL 8后，可能由于字符集问题出现 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) 错误，此时要修改对象的字符集。
1. 批量修改库字符集change_database_characset.sql
selectconcat('alterdatabase',schema_name,'defaultcharactersetutf8mb4collateutf8mb4_0900_ai_ci;')frominformation_schema.schematawhereschema_namenotin('sys','mysql','performance_schema','information_schema')andlower(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.out2>&12. 批量修改表字符集
change_table_characset.sql
selectconcat('altertable',table_schema,'.',table_name,'defaultcharactersetutf8mb4collate=utf8mb4_0900_ai_ci;')frominformation_schema.tableswheretable_schemanotin('sys','mysql','performance_schema','information_schema')andtable_type='BASETABLE'andlower(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.out2>&13. 批量修改列字符集
change_column_characset.sql
setgroup_concat_max_len=10240;selectconcat(c1,c2,';')from(selectc1,group_concat(c2)c2from(selectconcat('altertable',t1.table_schema,'.',t1.table_name)c1,concat('modify','`',t1.column_name,'`',t1.data_type,if(t1.data_typein('varchar','char'),concat('(',t1.character_maximum_length,')'),''),'charactersetutf8mb4collateutf8mb4_0900_ai_ci',if(t1.is_nullable='NO','notnull','null'),'comment','''',t1.column_comment,'''')c2frominformation_schema.columnst1,information_schema.tablest2wheret1.table_schema=t2.table_schemaandt1.table_name=t2.table_nameandt2.table_type='BASETABLE'andlower(t1.collation_name)in('utf8mb4_general_ci','utf8_general_ci')andt1.table_schemanotin('sys','mysql','performance_schema','information_schema'))t1groupbyc1)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.out2>&1
上一篇：mysql socket指的是什么
下一篇：mysql之数据库常用脚本有哪些
mysql
