MySQL with utf8mb4 « Blog.XDite.net

almost 11 years ago

MySQL with utf8mb4

TL;DR; 這篇很有可能大家看不懂。只是整理來分享。（把同事 v1nc3ntlaw 的筆記從 Redmine 搬到 blog，他說他懶得寫 XD）。我想作國際產品的人應該很有可能會撞到這個問題...

前陣子我們的 Logdown 某個使用者 blog 匯入一直失敗。後來查了很久以後，我同事 v1nc3ntlaw 追到是 MySQL utf8 編碼沒有完整支援所有 utf8 字元的問題。要解決的話必須使用 utf8mb4。

Logdown 跑的是 Rails 3.2.12。
根據 http://donpark.org/blog/2013/02/16/rails-3-2-12-not-ready-for-mysql-5-5-utf8mb4。

我們 db 機器跑得是 Debian 6，系統 package 是裝 MySQL 5.1.x。而 MySQL 在 5.5.3+ 版本才支援 utf8mb4。所以後來解法是改裝 Percona MySQL 的 5.5。

倒資料

dump 出來的 sql 要修正裡面 utf8 改成 utf8mb4，把 schema 和 data 分開 dump

mysqldump -u root -p --no-data --databases logdown_production > logdown_production_schema.sql
mysqldump -u root -p --no-create-info logdown_production > logdown_production_data.sql

logdown_production_data.sql 只要修改第 10 行連線時的 utf8 參數

- 10 /*!40101 SET NAMES utf8 */;
+ 10 /*!40101 SET NAMES utf8mb4 */;

logdown_production_schema.sql 除了第 10 行的連線參數要改外，還要找出建立 database、table、column 指定成 utf8 的地方都改成 utf8mb4

- 10 /*!40101 SET NAMES utf8 */;
+ 10 /*!40101 SET NAMES utf8mb4 */;
...
- 22 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `logdown_production` /*!40100 DEFAULT CHARACTER SET utf8 */;
+ 22 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `logdown_production` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
...

最後還要把欄位長度超過 255 且有打 index 的資料找出來，把 index 長度限制在 191，不然會炸掉

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html

InnoDB has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters

utf8 一個字元佔 3-byte，utf8mb4 一個字元佔 4-byte。然後 innodb 的 index 長度最長只能到 767 bytes。所以 utf8mb4 index 的長度只能到 767 / 4 = 191 字元。原本 varchar(255) 又有打 index 的地方匯入 SQL 時就會失敗。
只要修改匯入 schema 時的 SQL，找出長度超過 191 又打 index 的地方。加上建立 index 時限制長度為 191 就可以了

這個問題目前在 rails 4 有修正的 patch，但 issue 上還是有人繼續反應有問題

https://github.com/rails/rails/issues/9855

日本人的解法

日文找到的另外一個更好的解法，把 innodb_file_format 改成 Barracuda 格式。然後上個 initializer 的 patch 讓 create table 時 ROW_FORMAT 使用 DYNAMIC 就沒有上述的問題。

因為

When innodb_file_format is set to “Barracuda” and a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long column values are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page.

MySQL Barracuda 和 ROW_FORMAT=DYNAMIC 相關中文說明 http://www.woqutech.com/?p=368

日文的參考資料

＝＝＝升級分隔線 ===

MySQL Database

MySQL 加入以下設定

#
# * Character Settings
#
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
skip-character-set-client-handshake

#
# * Innodb Settings
#
innodb_file_format     = Barracuda
innodb_file_format_max = Barracuda
innodb_file_per_table  = 1
innodb_large_prefix

MySQL 升級到 Percona MySQL 5.5

http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html

倒已經轉好的 db schema

mysql -u root -p < logdown_production_schema_utf8mb4.sql

倒資料

mysql -u root -p logdown_production < logdown_production_data.sql

Rails 的部分

Rails 升級 mysql2 gem 到 0.3.13

修改 config/database.yml 連線使用的 encoding

-  encoding: utf8
+  encoding: utf8mb4
+  charset: utf8mb4
+  collation: utf8mb4_unicode_ci

新增 config/initializers/ar_innodb_row_format.rb patch activerecord create table 時的行為，create table 時加入 ROW_FORMAT=DYNAMIC 的參數

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters

    class AbstractMysqlAdapter
      def create_table_with_innodb_row_format(table_name, options = {})
        table_options = options.reverse_merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')
        create_table_without_innodb_row_format(table_name, table_options) do |td|
          yield td if block_given?
        end
      end
      alias_method_chain :create_table, :innodb_row_format
    end

  end
end

解決開發環境的問題

開發環境用不用 utf8mb4 沒差，只要不碰到不支援的字元就好，但 DYNAMIC ROW_FORMAT table 一定必須使用 Barracuda。否則使用 Logdown 線上的 db 要 import 進開發環境的 db 會遇到錯誤，而使用 Barracuda 並不會影響到其它的專案 db。

所以開發環境必須也要加入下面的設定，homebrew 安裝的 MySQL 設定檔位置：/usr/local/etc/my.cnf

#
# * Innodb Settings
#
innodb_file_format     = Barracuda
innodb_file_format_max = Barracuda
innodb_file_per_table  = 1
innodb_large_prefix

然後重新啓動 MySQL。

$ mysql.server restart

December 19, 2013 14:55
Permalink
Comments