MySQL 5.7 と絵文字（ちょこっと Rails）

絵文字（UTF-8 の 4 byte 文字にあたる）を利用可能とするため、文字コードは utf8mb4 を利用する。

絵文字だけでなく非常用漢字なども対応するためには必要。

サーバ設定

とりあえず開発環境（Mac）は Homebrew でインストール brew install mysql （検証時点で 5.7.13）。

サーバの設定を以下のとおり変更する。

$ diff /etc/my.cnf.org /etc/my.cnf
--- /etc/my.cnf.org     2016-07-05 18:46:02.000000000 +0900
+++ /etc/my.cnf 2016-07-15 14:43:47.000000000 +0900
@@ -16,7 +16,7 @@

 # The following options will be passed to all MySQL clients
 [client]
-default-character-set = utf8
+default-character-set = utf8mb4
 #password      = your_password
 port           = 3306
 socket         = /tmp/mysql.sock
@@ -25,7 +25,7 @@

 # The MySQL server
 [mysqld]
-character-set-server = utf8
+character-set-server = utf8mb4
 port           = 3306
 socket         = /tmp/mysql.sock
 skip-external-locking
@@ -74,6 +74,9 @@
 #innodb_flush_log_at_trx_commit = 1
 #innodb_lock_wait_timeout = 50

+[mysqld_safe]
+timezone = UTC
+
 [mysqldump]
 quick
 max_allowed_packet = 16M

ついでに Timezone を UTC に設定している。

デフォルトはローカル環境の Timezone に依存して JST になっていたが、AWS RDS for MySQL の Parameter Group ではデフォルト UTC が設定されているので合わせておきたかったため。

文字コードと Timezone の設定は MySQL client からそれぞれ以下の SQL で確認できる。

mysql> show variables like 'character%';
mysql> show variables like '%time_zone%';

こんな感じ（例）

mysql> show variables like 'character%';
+--------------------------+-------------------
| Variable_name            | Value             
+--------------------------+-------------------
| character_set_client     | utf8mb4
| character_set_connection | utf8mb4
| character_set_database   | utf8mb4
| character_set_filesystem | binary            
| character_set_results    | utf8mb4
| character_set_server     | utf8mb4
| character_set_system     | utf8              
| character_sets_dir       | /path/to/charsets
+--------------------------+-------------------
8 rows in set (0.01 sec)

utf8 と utf8mb4 の違い（ざっくり）

マルチバイト文字（日本語とか）（つまりだいたい UTF-8 の話）の文字コードは 3byte で表現されるが、そこに絵文字などの表現が追加され、それを 4byte で表現するようになった。

これ（4byte で表現）に対応しているのが utf8mb4 。

1 文字を表現するための byte 数が増えるため、データベースに保存される実際のデータサイズも増える。

たとえば VARCHAR(255) に utf8 で 255 文字保存すれば、単純計算で 255*3=765 (byte)、utf8mb4 で 255 文字保存すると 255*4=1020 (byte) という差が出る。
（実際には VARCHAR 値にはプリフィクスが 1〜2 byte 追加されるので、サイズはもうちょっと増えるはず）

その関係で、データサイズ上限だとか INDEX につかえるサイズ制限だとかの問題が出てきて、いろいろ注意や工夫が必要と言われてきたが、MySQL 5.7.7 以降でのデフォルト値ではその辺を（ひとまず）心配しなくても使えるような設定になった様子。

MySQL 5.7.7 以上なら心配しなくていいこと

MySQL 5.5, 5.6 系での絵文字対応記事によくあった設定について調査。

• ActiveRecordをutf8mb4で動かす - Qiita
  • ※2012年 MySQL 5.5 のネ申（kamipo さん）記事
• Rails + MySQL(InnoDB)で絵文字を使う - Qiita
  • ※2016年1月 MySQL（多分）5.6 の記事

以下は MySQL 5.7.7 からデフォルトになったので、utf8mb4 対応のために個別に設定する必要はない。

# my.cnf
innodb_large_prefix = ON
innodb_file_format = Barracuda

• innodb_large_prefix - MySQL :: MySQL 5.7 Reference Manual :: 15.12 InnoDB Startup Options and System Variables
• innodb_file_format - MySQL :: MySQL 5.7 Reference Manual :: 15.12 InnoDB Startup Options and System Variables

innodb_large_prefix の設定に伴って ROW_FORMAT=DINAMIC もデフォルトとされたため、こちらもとくに気にする必要はない。

• MySQL :: MySQL 5.7 Reference Manual :: 15.9.3 DYNAMIC and COMPRESSED Row Formats

つまり先達の記事にあるこれ↓の心配がなくなった。よかった。

schema_migrationsがインデックス張れないエラーでこけます

MySQL 5.5, 5.6 からの移行を考える場合はデフォルト値を考慮しよう。

Rails の接続設定と照合順序（collation）早覚え

encoding と collation を指定。

--- a/config/database.yml
+++ b/config/database.yml
@@ -11,7 +11,8 @@
 #
 default: &default
   adapter: mysql2
-  encoding: utf8
+  encoding: utf8mb4
+  collation: utf8mb4_bin
   pool: 5
   username: root
   password:

なぜ collation の設定が必要かというと、照合順序の種類には以下のタイプ（一例）があり、選択によっていわゆる「ハハパパ問題」「🍣🍻問題」が発生するから。

*_unicode_ci

• ß と ss を等しいと判断したい文化圏の人たちのための設定
• ハハ と パパ が等しいと扱われる問題の元凶（ハハパパ問題）
• Rails4 まで Rails 側の デフォルトがこれだった
  • MySQL のデフォルトを上書きしてくれよる 😇

*_general_ci

• 上記の ß と ss を区別するため、ハハパパ問題が解消される
• ただし ASCII 英字の大文字小文字を区別しない（※一例）
• 🍣🍻問題もこの設定では解消できない
• MySQL のデフォルト はこれ
• Rails5 から Rails 側ではデフォルトで照合順序をいじらなくなった

*_bin

• *_general_ci よりさらに厳しく判定する（文字コード一致で判定される）
• つまり ASCII 英字の大文字小文字を区別する（※一例）
• 🍣と🍻も異なるものだと判定できるようになる

※参考にした記事: MySQLの照合順序 - Qiita
※🍣🍻問題において *_unicode_520_ci という解決法もあるらしいけど、自分は直近で使わないので今回は詳しく調べませんでした
※くわしくは公式のドキュメント読み漁りましょう（あえて日本語ドキュメントへリンク）

その他の参考情報

いろいろ調査をする過程でお世話になった記事など。感謝。

• 👑 MySQL 5.7にやられないためにおぼえておいてほしいこと - SlideShare
• 👑 Rails5から使えるActiveRecord便利機能 - Qiita
• RDS MySQL5.5.33 で『utf8mb4』(4バイト対応UTF-8文字コードセット)を試してみた ｜ Developers.IO
• How to store emoji in a Rails app with a MySQL database - Arkency Blog
  • 💭 絵文字の切り詰めの Warning は 5.7 で Error になったのか。どおりで「保存できるけど絵文字のところで切れてしまう」が再現できないと思った…
• AWS OpsWorks[RDS + Rails] で utf8mb4 を使う - Qiita 💭 OpsWorks 使う人の話
• 公式ドキュメント
  • MySQL :: MySQL 5.7 Reference Manual :: 11.1 Character Set Support
  • MySQL :: MySQL 5.7 Reference Manual :: 15.6.7 Limits on InnoDB Tables
    • 日本語だと 5.6 までしか無いけど、読み取りづらい部分を（バージョンに気をつけて）読む分には日本語で読んでもいいと思う（逆に意味不明になってることもあるけど）

こちらからは以上です。

思い出した追記 🙄

そんなこんなで MySQL 5.7 + Rails 5（kamipo さんのネ申対応がたくさん入った）最強じゃん！と思ってたら、Heroku の MySQL Plugin である ClearDB が MySQL 5.7 に対応していない（MySQL 5.5 だった）ということがあって悲しかった。

アプリケーションエンジニアとしてインフラへの配慮が抜けてるのはちょっと申し訳なかった感。

おまけの追記 🙃

MySQL 5.7.11 で default̲_password_lifetime の暗黙のデフォルトは 0 になりました。それ以降のバージョンであれば 360⽇におびえる必要はありません。

cite: MySQL 5.7にやられないためにおぼえておいてほしいこと - SlideShare