MySQL批量修改表及表內(nèi)字段排序規(guī)則舉例詳解
更新時間：2024年05月25日 16:13:07 作者：LukeGLY
在MySQL中字段排序規(guī)則(也稱為字符集和排序規(guī)則)用于確定如何比較和排序字符串,下面這篇文章主要給大家介紹了關(guān)于MySQL批量修改表及表內(nèi)字段排序規(guī)則的相關(guān)資料,需要的朋友可以參考下
Mysql向TiDB遷移時發(fā)現(xiàn)不支持 'utf8mb4_german2_ci'排序規(guī)則，為解決此問題需修改Mysql表及表內(nèi)字段排序規(guī)則，具體操作如下：
一、表排序規(guī)則修改
1、生成修改表排序規(guī)則的SQL語句
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
' COLLATE utf8mb4_general_ci;') AS '修正SQL'
from information_schema.tables
where TABLE_SCHEMA = '數(shù)據(jù)庫名'
and TABLE_COLLATION = 'utf8mb4_german2_ci';2、生成的 SQL 語句如下：
ALTER TABLE 數(shù)據(jù)庫名.表名 COLLATE utf8mb4_general_ci;
3、復(fù)制執(zhí)行即可；
二、表內(nèi)字段排序規(guī)則修改
1、生成修改字段排序規(guī)則的SQL語句
SELECT
CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE,
'(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
(case when IFNULL(column_comment,'')='' then '' else concat(' COMMENT \'' , column_comment ,'\'') end),
';') AS `修正SQL`
FROM information_schema.COLUMNS
WHERE 1=1
and TABLE_SCHEMA = '數(shù)據(jù)庫名' #要修改的數(shù)據(jù)庫名稱
and DATA_TYPE IN ('varchar','char')
and COLLATION_NAME='utf8mb4_german2_ci'2、生成的 SQL 語句如下：
ALTER TABLE `t_categories` MODIFY `code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標(biāo)識';
3、復(fù)制執(zhí)行即可；
補充：MySQL批量修改庫、表、列的排序規(guī)則
1、表字段修改
SELECT
TABLE_SCHEMA '數(shù)據(jù)庫',
TABLE_NAME '表',
COLUMN_NAME '字段',
CHARACTER_SET_NAME '原字符集',
COLLATION_NAME '原排序規(guī)則',
CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.`COLUMNS`
WHERE
COLLATION_NAME RLIKE 'utf8';utf8 是模糊匹配排序規(guī)則，這里需要替換為你數(shù)據(jù)庫中需要替換的字段的排序規(guī)則，
utf8mb4設(shè)置的是替換的字符集，
utf8mb4_general_ci設(shè)置的是替換的排序規(guī)則，
上述語句可以增加TABLE_SCHEMA='數(shù)據(jù)庫名'，來限定數(shù)據(jù)庫。
把修正SQL復(fù)制出來直接運行即可。
2、表修改
SELECT
TABLE_SCHEMA '數(shù)據(jù)庫',
TABLE_NAME '表',
TABLE_COLLATION '原排序規(guī)則',
CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' COLLATE=utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.`TABLES`
WHERE
TABLE_COLLATION RLIKE 'utf8';把修正SQL 復(fù)制出來直接運行即可。
3、數(shù)據(jù)庫修改
#修改數(shù)據(jù)庫
SELECT
SCHEMA_NAME '數(shù)據(jù)庫',
DEFAULT_CHARACTER_SET_NAME '原字符集',
DEFAULT_COLLATION_NAME '原排序規(guī)則',
CONCAT( 'ALTER DATABASE ', SCHEMA_NAME, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) '修正SQL'
FROM
information_schema.`SCHEMATA`
WHERE
DEFAULT_CHARACTER_SET_NAME RLIKE 'utf8';把修正SQL 復(fù)制出來直接運行即可。
總結(jié)
到此這篇關(guān)于MySQL批量修改表及表內(nèi)字段排序規(guī)則的文章就介紹到這了,更多相關(guān)MySQL批量修改表及字段排序規(guī)則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL選錯索引的原因以及解決方案，幫助大家更好的理解和使用MySQL索引，感興趣的朋友可以了解下2020-10-10
在我們裝mysql數(shù)據(jù)庫時，出現(xiàn)安裝失敗是一件非常令人煩惱的事情，接下來小編就給大家?guī)碓谖覀儼惭bmysql數(shù)據(jù)庫失敗的一些解決方法，感興趣的小伙伴們可以參考一下2016-05-05
通過分享本文帶領(lǐng)大家三十分鐘入門mysql，包括sql的基礎(chǔ)知識，creat語法知識，非常不錯，具有一定的參考借鑒價值，感興趣的朋友一起看看吧2016-11-11
mysql存儲過程遇到ERROR 1060 (42S21):Duplicate colum
這篇文章主要介紹了mysql存儲過程遇到ERROR 1060 (42S21):Duplicate column問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-04-04
mysql的語句查詢順序、耗時分析以及查詢調(diào)優(yōu)技巧分享
文章詳細介紹了MySQL中SQL查詢語句的執(zhí)行順序、耗時分析及查詢調(diào)優(yōu)方法,包括使用EXPLAIN、SHOW PROFILE和慢查詢?nèi)罩镜裙ぞ哌M行耗時分析,以及使用索引、避免全表掃描、優(yōu)化查詢語句、分區(qū)表和調(diào)整服務(wù)器配置等技巧進行性能優(yōu)化2025-02-02
深入理解MySQL主從復(fù)制線程狀態(tài)轉(zhuǎn)變
這篇文章主要給大家介紹了關(guān)于MySQL主從復(fù)制線程狀態(tài)轉(zhuǎn)變的相關(guān)資料，文中介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
詳解如何校驗MySQL及Oracle時間字段合規(guī)性
這篇文章主要為大家介紹了如何校驗MySQL及Oracle時間字段合規(guī)性詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2023-06-06
MySQL中的JOIN操作用于將多個表中的數(shù)據(jù)關(guān)聯(lián)起來,常見的JOIN 類型包括INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN（MySQL 不直接支持 FULL JOIN,但可通過 UNION 實現(xiàn)）,本文介紹MySQL各種 JOIN 的特點及應(yīng)用場景,感興趣的朋友一起看看吧2025-12-12
在創(chuàng)建MySQL外鍵約束時,報錯3780通常是因為主表和從表中對應(yīng)字段的數(shù)據(jù)類型不一致,使用Navicat可視化界面修改數(shù)據(jù)類型,即可解決此問題,這是一個常見的數(shù)據(jù)庫設(shè)計錯誤,確保數(shù)據(jù)類型一致是關(guān)鍵2024-11-11
這篇文章主要介紹了Mysql中日期比較大小的方法,需要的朋友可以參考下2018-04-04