修改MySQL的文字編碼 – From utf8 to utf8mb4

前陣子在設計一隻簡單的網路爬蟲,目標是網路上的部落格文章,結果發現爬下來的內容有時會無法寫入MySQL資料庫儲存,追查後發現是因為 MySQL 預設的 UTF8 並不支援所有的字元,改成使用 UTF8_mb4 就能解決這問題。 其中 UTF8 與  UTF8_mb4 的差別在於一個是 3 byte,另一個是 4 byte,原本 MySQL 如此設計是為了節省空間、增加字串的可儲存長度,但網路爬蟲會爬到甚麼文字內容完全是未知數,當遭遇不支援的字元時反而就出狀況啦!

修改MySQL資料庫所使用的文字編碼 (utf8 to utf8mb4) 本身不是太困難,只是有些配套的設定如果沒有同時修改,未來將可能會遭遇到其他問題,為此做了些功課後有了此篇筆記。

主要需要注意的問題有兩個:
  1. 當修改已經有資料的資料庫時,要針對備份出來的資料作處理,不然會無法存入修改過後的 table。
  2. 如果某些字串欄位已經或將要打上 index,將會遭遇到 index 長度過長的問題,解決方法有:將 index 長度由 255 改成 191,或者使用 Barracuda 動態的 index 格式,下文是後者的作法。
    p.s. 我在使用 Rails 開發網頁時,也有用過第一個方法,將 index 的長度改成191,有機會另述

Step1. 倒出資料

將原有資料庫倒出,分成 schema.sql 與 data.sql 兩個檔案:
$ mysqldump -u root -p –no-data [schema_name] > schema.sql
$ mysqldump -u root -p –no-create-info [schema_name] > data.sql

Step2. 處理 schema.sql

這個檔案的內容的是關於資料庫中 table 的設定,基本上就是一連串MySQL指令的 Script,為避免待會重新建立 table 時會有些細節 MySQL會直接使用我們不希望的預設值,因此由 utf8 換成 utf8mb4 有幾點眉角要處理,使用sed指令修改檔案會有點麻煩,因此我選擇直接使用文字編輯器進行處理:

  1. 將所有 utf8 字串改成 utf8mb4,如原本就是utf8mb4的不需修改,使用字串取代時要小心
  2. 找出未設定collation的 varchar(255) 與 text 欄位,補上設定,
    如:varchar(255) DEFAULT NULL 改成 varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
  3. text COLLATE 改成 utf8mb4_unicode_ci 後,因為單一字元大小由 3 byte 變 4 byte,加上 index 有資料大小限制,原本字串長度255的字串欄位會無法繼續使用 index,解決方法是在 create-table 的設定加上 ROW_FORMAT=DYNAMIC
    如: ENGINE=InnoDB COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
  4. 最後將檔案另存為 schema_fixed.sql
- 10 /*!40101 SET NAMES utf8 */;
+ 10 /*!40101 SET NAMES utf8mb4 */;
...
- 22 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `schema_name` /*!40100 DEFAULT CHARACTER SET utf8 */;
+ 22 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `schema_name` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
...
- 35  `place` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
+ 35  `place` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
...
- 70  `title` varchar(255) DEFAULT NULL,
+ 70  `title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- 71  `article_url` text,
+ 71  `article_url` text COLLATE utf8mb4_unicode_ci,
...

Step3. 處理 data.sql

將所有的 utf8 替換成 utf8mb4。因為需要處理的內容很簡單,加上檔案可能很巨大,不方便用文字編輯器開啟,故使用sed指令來處理:
$ sed ‘s/utf8/utf8mb4/’ data.sql > data_fixed.sql
- 10 /*!40101 SET NAMES utf8 */;
+ 10 /*!40101 SET NAMES utf8mb4 */;
可以用 $ cat data_fixed.sql | grep utf8 檢查修改的結果

Step4. 設定MySQL server

  1. 在 /etc/mysql/my.cnf 中,[mysqld] 底下新增下列設定
  2. 設定後用指令 $ mysqld restart 重新啟動
[mysqld]
#
# * Character Settings
#
character-set-client-handshake = false
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server  = utf8mb4
character-set-client  = utf8mb4
collation-server      = utf8mb4_unicode_ci
#
# * Innodb Settings
#
innodb_file_format     = Barracuda
innodb_file_format_max = Barracuda
innodb_file_per_table  = 1
innodb_large_prefix

Step5. 覆蓋存回資料

$ mysql -u root -p [schema_name] < schema_fixed.sql
$ mysql -u root -p [schema_name] < data_fixed.sql
參考資料
《MySQL with utf8mb4》
《官方文件》關於字串長度的說明
關於使用Barracuda,在schema所需要的設定《MySQL(InnoDB) で “Index column size too large. The maximum column size is 767 bytes." いわれるときの対策》
關於Server設定的說明《Change MySQL 5.5 default character-set to UTF8》
Advertisements

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s