Comment écrire un script pour modifier le jeu de caractères par lots dans MySQL8

Après la migration d'une version inférieure vers MySQL 8, des erreurs de mélange illégal de classements (utf8mb4_general_ci,IMPLICIT) et (utf8mb4_0900_ai_ci,IMPLICIT) peuvent survenir en raison de problèmes de jeu de caractères. À ce stade, le jeu de caractères de l'objet doit être modifié. être modifié.

1. Modification par lots du jeu de caractères de la bibliothèque

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');

Appel :

/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. Jeu de caractères de table de modification

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');

Appel :

/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;

Appel :

/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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!