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ī)則，為解決此問題需修改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ù)庫(kù)名'
and TABLE_COLLATION = 'utf8mb4_german2_ci';2、生成的 SQL 語句如下：
ALTER TABLE 數(shù)據(jù)庫(kù)名.表名 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ù)庫(kù)名' #要修改的數(shù)據(jù)庫(kù)名稱
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)識(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ī)則，
上述語句可以增加TABLE_SCHEMA='數(shù)據(jù)庫(kù)名'，來限定數(shù)據(jù)庫(kù)。
把修正SQL復(fù)制出來直接運(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ù)制出來直接運(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ù)制出來直接運(yùn)行即可。
總結(jié)
到此這篇關(guān)于MySQL批量修改表及表內(nèi)字段排序規(guī)則的文章就介紹到這了,更多相關(guān)MySQL批量修改表及字段排序規(guī)則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
如何把本地mysql遷移到服務(wù)器數(shù)據(jù)庫(kù)
這篇文章主要介紹了如何把本地mysql遷移到服務(wù)器數(shù)據(jù)庫(kù),文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-11-11
如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog
這篇文章主要介紹了如何區(qū)分MySQL的innodb_flush_log_at_trx_commit和sync_binlog，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下2021-02-02
MySQL的DELETE(刪除數(shù)據(jù))用法解讀
本文將詳細(xì)介紹DELETE語句的基本語法、高級(jí)用法、性能優(yōu)化策略以及注意事項(xiàng),具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-03-03
這篇文章主要介紹了MySQL 5.7.27詳細(xì)下載安裝配置教程,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-08-08
在數(shù)據(jù)庫(kù)中,NULL 值表示缺失或未知的數(shù)據(jù),在 MySQL 中,我們可以使用特定的查詢語句來查找包含 NULL 值的數(shù)據(jù),本文將詳細(xì)介紹如何在 MySQL 中查找 NULL 值,并提供相關(guān)實(shí)例和代碼片段,需要的朋友可以參考下2024-05-05
MySQL的自增id都定義了初始值，然后不斷加步長(zhǎng)。雖然自然數(shù)沒有上限，但定義了表示這個(gè)數(shù)的字節(jié)長(zhǎng)度，那自增id用完，會(huì)怎么樣？本文就來介紹一下2021-08-08
批量 kill mysql 中運(yùn)行時(shí)間長(zhǎng)的sql
這篇文章主要介紹了批量 kill mysql 中運(yùn)行時(shí)間長(zhǎng)的sql,需要的朋友可以參考下2016-01-01
MySQL定位并優(yōu)化慢查詢sql的詳細(xì)實(shí)例
mysql記錄下查詢超過指定時(shí)間的語句,被稱為慢查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL定位并優(yōu)化慢查詢sql的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2021-12-12
MySQL數(shù)據(jù),查詢QPS,TPS數(shù)據(jù)方式
文章詳細(xì)介紹了查詢MySQL數(shù)據(jù)庫(kù)QPS和TPS的方法和工具,包括直接通過命令行、PerformanceSchema、mysqladmin、Prometheus、自動(dòng)化腳本等,同時(shí),還提供了優(yōu)化建議,如索引優(yōu)化、SQL調(diào)優(yōu)、事務(wù)控制和配置調(diào)優(yōu)2025-02-02
這篇文章主要詳細(xì)介紹了mysql表的清空、刪除和修改操作的方法，以及一些常用的mysql的常用操作，非常的簡(jiǎn)單實(shí)用，有需要的可以參考下2014-09-09