首頁 > 文章 > 資料庫 > MySQL8批次修改字符集腳本怎麼寫

MySQL8批次修改字符集腳本怎麼寫

王林轉載: 2023-06-03 08:01:20712瀏覽

從低版本移轉到MySQL 8後，可能會因為字元集問題出現 Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) 錯誤，此時要修改物件的字元集。

1. 批次修改函式庫字元集

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

呼叫：

/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(&#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;);

呼叫：

/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,&#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;

呼叫：

/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

以上是MySQL8批次修改字符集腳本怎麼寫的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文轉載於：yisu.com。如有侵權，請聯絡admin@php.cn刪除

上一篇：mysql left join的基本用法及on與where的差別是什麼下一篇：mysql left join的基本用法及on與where的差別是什麼

看更多