MySQLのencodingをutf8からutfmb4に変更して寿司ビール問題に対応する

こんにちは、hachi8833です。

utf8の4バイト文字問題は突然に

160823_0838_ldO8ik

MySQLのデータベースでencoding=utf8が指定されていると、UTF-8の文字長が4バイトの文字をデータベースに保存できなくなる、いわゆるUTF-8の4バイト文字問題、またの名を「寿司ビール問題」が発生することがあります(「MySQLのutf8の4バイト文字問題とは」で後述)。

弊社Webチーム部長のmorimorihoge さんがこの問題に対応したときの手順をメモします。

`utf8`から`utf8mb4`に移行する手順

MySQLのストレージエンジンはInnoDBが前提です。utf8mb4を指定するにはMySQLのバージョンが5.5以上である必要があります。

1. 以下のコマンドでdumpを取る

mysqldump --no-create-info --ignore-table=mydata_store.schema_migrations -uroot mydata_store

2. my.cnfに以下を追加してrestart(MySQL 5.7.9 より前のバージョンの場合)

innodb_file_per_table
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_large_prefix

Indexサイズの問題を回避するため、ファイルフォーマットをAntelopeからBarracudaに切り替えます。

注意: この手順2はMySQL 5.7.9 以降では不要になりました(参考: MySQL(InnoDB) で charset を utf8mb4 にする注意点の現在)。

3. config/initializersに以下の定義が入ったファイルを置く

# MySQLでutf8mb4を利用する場合、ROW_FORMART=DYNAMICが必要
# ※my.cnfへの設定追加も必要なので注意
#
# refer: http://3.1415.jp/mgeu6lf5/
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

4. database.ymlを以下に設定

 encoding: utf8mb4
  charset: utf8mb4
  collation: utf8mb4_unicode_ci

5. db:migrate:resetする

これで全データが消えて、全テーブルがutf8mb4になります。

6. mysqlコマンドで最初にdumpしたデータをインポートする

これで既に入っているデータを保持しつつ、utf8mb4にmigrationできるようになります。

MySQLのutf8の4バイト文字問題とは

MySQL のencodingやcharsetのutf8は、実は真のUTF–8ではなく、4バイト長の文字に対応していません。

160823_0940_JE6dBa

ちょっと検索するだけで、Railsに限らず、MySQLでこの問題を踏んだ多くのエンジニアの悲しい叫び声が続々と見つかります。

4バイト長UTF–8文字が問題になるのは、主に中国語と日本語です。中国語としても使われている一部の漢字が4バイト長になっていますが、一部が日本語でも人名や地名に使われることがあります。そのため、マイナーな文字が使われている人名がテーブルに登録されて発覚することがあります。

160821_1138_n5oIW2

英語圏ではこの問題に直面することはあまりなかったようですが、近年UTF–8の絵文字が多用されるようになり、絵文字の一部が4バイト長になっているため、近年は英語圏でも問題になっています。

UTF-8絵文字の中でも、特に寿司アイコンとビールアイコン(🍣と🍺)が同値判定されてしまう問題が、2015に「寿司ビール問題」と呼ばれるようになりました。「ケツカンマ問題」と並んで、問題を端的に表現した素晴らしいネーミングだと思います。

MySQL のバージョン5.5以降であれば、encodingやcharsetなどの項目にutf8mb4を指定することで4バイト長の文字に対応できるようになります。

とはいうものの、utf8が真のUTF–8でないことに変わりはありません。

MySQL側でutf8をUTF-8としての正しい挙動に変更したときの影響の大きさを考えれば、utf8mb4追加による対応は致し方ないという気もしますが、MySQLを初めて扱うエンジニアが踏みがちなブービートラップとして当分永らえそうです。

Railsのdatabase.ymlのmysql2mysql2 アダプタでutf8が指定されていたら、警告するぐらいのことはしてもよいように思います。

メモ: MySQLは今後どう進むのか

ずい分昔の漢のコンピュータ道ブログでこんな記述を見つけて「おっ」と色めきたったのですが、

(2008-10-01)…というわけでMySQLもようやく次の次のバージョンである6.0からUTF-8が4バイト対応になる。MySQL 6.0ではutf8といえば4バイトの文字コードを指し、これまでの3バイトしか扱えない文字コードはutf8mb3という名前で互換性のために残されている。既存のデータをそのまま扱いたい場合には、このutf8mb3を使うといいだろう。
Real UTF-8 On MySQL 6.0

その直後同じブログの翌年の記事を見つけて壮大にコケました。

(2009-05-25) MySQL 6.0.11-alphaがリリースされた。が、アナウンスレターには気になる記述がひとこと。「これはMySQL 6.0の最後のリリースです」と。寝耳に水かも知れないがこの話は本当だ。実はこれが最後のMySQL 6.0のリリースになる。つまり、MySQL 6.0の開発はこれでストップするのだ。
えっ!!MySQLはもう開発が終わっちゃうの?MySQL終了のお知らせ?!
などと心配しないで頂きたい。MySQLの開発はちゃんと継続される。開発の方針が変更されることになったからMySQL 6.0のリリースが見送られただけである。
Good Bye MySQL 6.0

http://www.mysql.com/をざっと見た限り、最新のロードマップ的なものをうまく見つけられません。OracleのイイコになっちゃったMySQLなので、いろいろ家庭の事情があるのかもしれません。

メモ: コレーションについて

MySQLに限らず、RDBMS、そして自然言語を対象にインデックスを生成するあらゆるソフトウェアでは、コレーション(collation)の指定も重要です。

コレーションは、インデックス作成時にどの文字とどの文字を同値として扱うかという戦略を指定するためのものであり、要件に応じて適切なものを指定する必要があります。たとえば、検索時にカタカナの濁点・半濁点(「ハ」「パ」「バ」)を区別するかどうかに影響します。

コレーションの問題は寿司ビール問題と同時に発生することもありえますが、寿司ビール問題がMySQL固有のエンコード/文字セットの扱いの問題である一方、コレーションは普遍的なテーマなので、それぞれ別の問題です。

コレーションについては別途記事にしたいと思います。

関連記事

Ruby on RailsによるWEBシステム開発、Android/iPhoneアプリ開発、電子書籍配信のことならお任せください この記事を書いた人と働こう! Ruby on Rails の開発なら実績豊富なBPS

この記事の著者

hachi8833

Twitter: @hachi8833、GitHub: @hachi8833 コボラー、ITコンサル、ローカライズ業界、Rails開発を経てTechRachoの編集・記事作成を担当。 これまでにRuby on Rails チュートリアル第2版の半分ほど、Railsガイドの初期翻訳ではほぼすべてを翻訳。その後も折に触れてそれぞれ一部を翻訳。 かと思うと、正規表現の粋を尽くした日本語エラーチェックサービス enno.jpを運営。 実は最近Go言語が好き。 仕事に関係ないすっとこブログ「あけてくれ」は2000年頃から多少の中断をはさんで継続、現在はnote.muに移転。

hachi8833の書いた記事

週刊Railsウォッチ

インフラ

BigBinary記事より

ActiveSupport探訪シリーズ