Tech Racho エンジニアの「?」を「!」に。
  • Ruby / Rails関連

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

更新情報

  • 2016/08/25: 初版公開
  • 2020/12/03: 追記

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

こんにちは、hachi8833です。

160823_0838_ldO8ik

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

BPSWebチーム部長の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に切り替えます。

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

追記(2020/12/03)

2017年の記事「Sushi = Beer ?! An introduction of UTF8 support in MySQL 8.0 | MySQL Server Blog」より↓

歴史的な理由で、MySQLのutf8文字セットはutf8mb4ではなくutf8mb3を参照しています。3バイトのutf8文字セットは、Unicodeで定義されている文字のうち限定的なセットしかサポートしないので、基本的には基本多言語平面(BMP: basic multilingual plane)になります。追加多言語面(SMP: supplementary multilingual plane)の絵文字やその他の文字はサポートされません。同様に、追加漢字面(SIP: supplementary ideographic plane)に含まれる追加の漢字(CJK統合漢字
拡張B
: CJK unified ideographs extension B)もutf8mb3ではサポートされません。
mysqlserverteam.comの同記事より

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

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

参考: 第86回 「𠮷」と「吉」 | 人名用漢字の新字旧字(安岡 孝一) | 三省堂 ことばのコラム

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

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

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

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

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

追記(2020/12/03)

同じく「Sushi = Beer ?! An introduction of UTF8 support in MySQL 8.0 | MySQL Server Blog」には2017年の時点でデフォルト文字セットutf8mb4に移行する構想が述べられており、その後MySQL 8.0.1からはデフォルト文字セットがutf8mb4になりました

参考: MySQL8.0の文字コード設定 | blog.kotamiyake.me

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

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

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

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

追記(2020/12/03)

MySQLのコレーションについては、8.0.1でutf8mb4_ja_0900_as_csを含む以下のコレーションが追加されました。

-- mysqlserverteam.comより
mysql> select collation_name from information_schema.collations where character_set_name='utf8mb4' and collation_name like '%as_cs' order by collation_name;
+----------------------------+
| collation_name             |
+----------------------------+
| utf8mb4_0900_as_cs         |
| utf8mb4_cs_0900_as_cs      |
| utf8mb4_da_0900_as_cs      |
| utf8mb4_de_pb_0900_as_cs   |
| utf8mb4_eo_0900_as_cs      |
| utf8mb4_es_0900_as_cs      |
| utf8mb4_es_trad_0900_as_cs |
| utf8mb4_et_0900_as_cs      |
| utf8mb4_hr_0900_as_cs      |
| utf8mb4_hu_0900_as_cs      |
| utf8mb4_is_0900_as_cs      |
| utf8mb4_ja_0900_as_cs      |
| utf8mb4_la_0900_as_cs      |
| utf8mb4_lt_0900_as_cs      |
| utf8mb4_lv_0900_as_cs      |
| utf8mb4_pl_0900_as_cs      |
| utf8mb4_ro_0900_as_cs      |
| utf8mb4_sk_0900_as_cs      |
| utf8mb4_sl_0900_as_cs      |
| utf8mb4_sv_0900_as_cs      |
| utf8mb4_tr_0900_as_cs      |
| utf8mb4_vi_0900_as_cs      |
+----------------------------+
22 rows in set (0.02 sec)

参考: MySQL 8.0.1: Accent and case sensitive collations for utf8mb4 | MySQL Server Blog
参考: 寿司とビールについて話し合いをしてきました | エンジニアブログ | GREE Engineering


CONTACT

TechRachoでは、パートナーシップをご検討いただける方からの
ご連絡をお待ちしております。ぜひお気軽にご意見・ご相談ください。