您好,登錄后才能下訂單哦!
這篇“MySQL8批量修改字符集腳本怎么寫(xiě)”文章的知識(shí)點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價(jià)值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來(lái)看看這篇“MySQL8批量修改字符集腳本怎么寫(xiě)”文章吧。
從低版本遷移到MySQL 8后,可能由于字符集問(wèn)題出現(xiàn) Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) 錯(cuò)誤,此時(shí)要修改對(duì)象的字符集。
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');
調(diào)用:
/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
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');
調(diào)用:
/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
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;
調(diào)用:
/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
以上就是關(guān)于“MySQL8批量修改字符集腳本怎么寫(xiě)”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對(duì)大家有幫助,若想了解更多相關(guān)的知識(shí)內(nèi)容,請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。