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

こんにちは、hachi8833です。 utf8の4バイト文字問題は突然に 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に切り替えます。 参考: InnoDBの8KBの壁にぶち当たったら 注意: この手順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バイト長の文字に対応していません。 ちょっと検索するだけで、Railsに限らず、MySQLでこの問題を踏んだ多くのエンジニアの悲しい叫び声が続々と見つかります。 4バイト長UTF–8文字が問題になるのは、主に中国語と日本語です。中国語としても使われている一部の漢字が4バイト長になっていますが、一部が日本語でも人名や地名に使われることがあります。そのため、マイナーな文字が使われている人名がテーブルに登録されて発覚することがあります。 英語圏ではこの問題に直面することはあまりなかったようですが、近年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固有のエンコード/文字セットの扱いの問題である一方、コレーションは普遍的なテーマなので、それぞれ別の問題です。 コレーションについては別途記事にしたいと思います。 関連記事 代理キーとナチュラルキー(人気記事) Rails 3.2.7にすると、rake specがdevelopmentモードになる mysqlのrestartでエラー