How to write a script for batch modifying character set in MySQL8-Mysql Tutorial-php.cn

How to write a script for batch modifying character set in MySQL8

王林

Release： 2023-06-03 08:01:20

forward

790 people have browsed it

After migrating from a lower version to MySQL 8, Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) errors may occur due to character set issues. At this time, the character set of the object needs to be modified.

1. Batch modify the library character set

change_database_characset.sql

select concat(&#39;alter database &#39;,schema_name,&#39; default character set utf8mb4 collate utf8mb4_0900_ai_ci;&#39;) 
  from information_schema.schemata 
 where schema_name not in (&#39;sys&#39;,&#39;mysql&#39;,&#39;performance_schema&#39;,&#39;information_schema&#39;) 
   and lower(default_collation_name) in (&#39;utf8mb4_general_ci&#39;,&#39;utf8_general_ci&#39;);

Copy after login

Call:

/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

Copy after login

2. Batch modify the table character set

change_table_characset.sql

select concat(&#39;alter table &#39;,table_schema,&#39;.&#39;,table_name,&#39; default character set utf8mb4 collate = utf8mb4_0900_ai_ci;&#39;) 
  from information_schema.tables where table_schema not in (&#39;sys&#39;,&#39;mysql&#39;,&#39;performance_schema&#39;,&#39;information_schema&#39;) 
   and table_type=&#39;BASE TABLE&#39; and lower(table_collation) in (&#39;utf8mb4_general_ci&#39;,&#39;utf8_general_ci&#39;);

Copy after login

Call:

/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

Copy after login

3. Batch modify column character set

change_column_characset.sql

set group_concat_max_len=10240;
 
select concat(c1,c2,&#39;;&#39;) 
  from (select c1, group_concat(c2) c2
          from (select concat(&#39;alter table &#39;,t1.table_schema,&#39;.&#39;,t1.table_name) c1,concat(&#39; modify &#39;,&#39;`&#39;,t1.column_name,&#39;` &#39;,t1.data_type,
                              if (t1.data_type in (&#39;varchar&#39;,&#39;char&#39;),concat(&#39;(&#39;,t1.character_maximum_length,&#39;)&#39;),&#39;&#39;),
                              &#39; character set utf8mb4 collate utf8mb4_0900_ai_ci&#39;,if(t1.is_nullable=&#39;NO&#39;,&#39; not null&#39;,&#39; null&#39;),&#39; comment &#39;,&#39;&#39;&#39;&#39;,t1.column_comment,&#39;&#39;&#39;&#39;) 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=&#39;BASE TABLE&#39; 
                   and lower(t1.collation_name) in (&#39;utf8mb4_general_ci&#39;,&#39;utf8_general_ci&#39;) and t1.table_schema not in (&#39;sys&#39;,&#39;mysql&#39;,&#39;performance_schema&#39;,&#39;information_schema&#39;)) t1
         group by c1) t;

Copy after login

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

Copy after login

The above is the detailed content of How to write a script for batch modifying character set in MySQL8. For more information, please follow other related articles on the PHP Chinese website!