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<🎜.

/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

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!