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