批量修改mysql表、表、資料庫的字元校對規則

標籤：

記錄一個在工作中遇到的問題，也不算是問題，為的是找一種簡便的方法批量修改資料表欄位的定序，在MySQL中叫collation，常常和編碼CHARACTER一起出現的。collation有三種層級，分辨是資料庫層級，資料表層級和欄位層級。

1.The database level2.The table level3.The column level

https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database 這篇文章說得比較詳細。

那天遇到的問題是這樣子的，
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=‘，主要是因為遷移資料庫時候沒有把collation規則及時修改過來。

網上搜到的解決辦法，都提到了修改資料表層級collation定序。但是我遇到的情境是資料表層級已經是utf8_unicode_ci，而欄位層級是utf8_general_ci，（這裡我們關心的欄位類型是varchar）。

由於需要修改的欄位太多了，手工修改肯定是費時費力的。自然也想到了用指令碼的方式批量修改，但是發現這種通過尋找MySQL資訊表、過濾、拼接產生批量修改的語句太好用了，而且還能做到針對varchar類型。

SELECT CONCAT(‘ALTER TABLE `‘, table_name, ‘` MODIFY `‘, column_name, ‘` ‘, DATA_TYPE, ‘(‘, CHARACTER_MAXIMUM_LENGTH, ‘) CHARACTER SET UTF8 COLLATE utf8_unicode_ci‘, (CASE WHEN IS_NULLABLE = ‘NO‘ THEN ‘ NOT NULL‘ ELSE ‘‘ END), ‘;‘)FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = ‘database‘AND DATA_TYPE = ‘varchar‘AND(    CHARACTER_SET_NAME != ‘utf8‘    OR    COLLATION_NAME != ‘utf8_unicode_ci‘);

database需要改成實際資料庫名字。需要注意的是，如果要修改的欄位存在外鍵關係，那就要小心處理，刪除外鍵，修改collation後再把外鍵關係加回來。

摘自http://segmentfault.com/a/1190000002570642

==========================以上網路引用，下面內容是我修改後的SQL，感謝鄭同學幫忙=======================================

-- 修改資料庫表校對規則SQL，執行時將表中列的校對規則一併修改。delimiter//drop procedure if exists `alter_table_character` //-- 若已存在則刪除create procedure `alter_table_character`() begin    declare f_name varchar(100);     declare b int default 0;    /*是否達到記錄的末尾控制變數*/-- 注意修改下面的資料庫名稱 wsm_aliyun    declare table_name cursor for SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = ‘wsm_aliyun‘ and TABLE_NAME like ‘wsm_%‘ AND TABLE_COLLATION = ‘utf8_unicode_ci‘;        DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;       OPEN table_name;    REPEAT    FETCH table_name INTO f_name; /*擷取第一條記錄*/SET @STMT :=CONCAT("ALTER TABLE ",f_name," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;");   PREPARE STMT FROM @STMT;       EXECUTE STMT;  -- INSERT into TestTable(name) VALUES (f_name);       -- ALTER TABLE f_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;     UNTIL b = 1END REPEAT;    close table_name;        end;///*切換回系統預設的命令結束標誌*/delimiter ;-- 執行預存程序call alter_table_character();-- 修改資料庫的校對規則set names ‘utf8‘ collate ‘utf8_general_ci‘;

-- 查詢修改的結果，其實還可以用下面的語句產生相應的SQL，執行這個SQL來完成修改，當然沒有上面的預存程序效率高。-- 查看資料庫的校對規則，結果全都為：utf8_general_ci，表示已修改show variables like ‘collation_%‘;-- 查看資料庫的校對規則，沒有資料表明已全部修改。SELECTCONCAT(‘alter table ‘, TABLE_NAME, ‘ CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;‘) as new_sqlFROMINFORMATION_SCHEMA.TABLESWHERETABLE_SCHEMA = ‘wsm_aliyun‘AND TABLE_NAME LIKE ‘wsm_%‘ -- 資料庫名稱AND TABLE_COLLATION = ‘utf8_unicode_ci‘;-- 查詢列結果，沒有資料表明已全部修改。SELECTCONCAT(‘ALTER TABLE `‘,table_name,‘` MODIFY `‘,column_name,‘` ‘,DATA_TYPE,‘(‘,CHARACTER_MAXIMUM_LENGTH,‘) CHARACTER SET UTF8 COLLATE utf8_general_ci;‘) as new_sqlFROMinformation_schema.COLUMNSWHERETABLE_SCHEMA = ‘wsm_aliyun‘ -- 資料庫名AND TABLE_NAME LIKE ‘wsm_%‘AND DATA_TYPE = ‘varchar‘AND CHARACTER_SET_NAME = ‘utf8‘AND COLLATION_NAME = ‘utf8_unicode_ci‘;

批量修改mysql表、表、資料庫的字元校對規則