Bagaimana untuk menulis skrip untuk mengubah suai set aksara dalam kelompok dalam MySQL8

Selepas berhijrah daripada versi yang lebih rendah kepada MySQL 8, Ralat gabungan pengumpulan yang tidak sah (utf8mb4_general_ci,IMPLICIT) dan (utf8mb4_0900_ai_ci,IMPLICIT) mungkin berlaku disebabkan isu set aksara pada masa ini, set aksara objek perlu diubahsuai.

1. Ubah suai set aksara perpustakaan

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

Panggil:

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

Panggil:

/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 Kumpulan ubah suai set aksara lajur

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;

Call

Atas ialah kandungan terperinci Bagaimana untuk menulis skrip untuk mengubah suai set aksara dalam kelompok dalam MySQL8. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!