Edited at

Rails + MySQL(InnoDB)で絵文字を使う

More than 3 years have passed since last update.


なぜ絵文字をデフォルトで使えないか


  • utf8は3バイト文字までしか対応していない

  • 絵文字は4バイトなので不正な文字として扱われる


絵文字をMysqlで扱うには

Rails + MySQLでのutf8mb4の設定フローは下記のとおり。


1. my.cnfを設定


my.cnf

innodb_large_prefix = ON

innodb_file_format = Barracuda


innodb_large_prefix


  • のちに ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes とエラーが出ることがある。

  • これは767byte問題ともいわれる。InnoDBテーブルのキープリフィックスは最高で767バイトの長さまで可能であり、たとえばutf8でvarchar(255)を使うことはよくあるが、255 * 3 = 755byteで許容範囲だったものが、uth8mb4に変更すると255 * 4 > 767byteとなり、キープレフィックスの許容範囲を超え上記エラーが出る。

  • つまり最大長は、UTF8の場合:767 ÷ 3 ≒ 255となり、UTF8MB4なら、767 ÷ 4 ≒ 191である。

  • innodb_large_prefixはインデックスの最大長を3072byteまで引き上げるので、上記エラーを防ぐために設定する

  • innodb_large_prefixのドキュメント:http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html


innodb_file_format = Barracuda


  • InnoDBのfile formatは、昔からあるAntelopeと新しいBarracudaがある。Barracudaには新機能が追加おり、その一つにインデックスサイズ制限の拡張がある。なので、innodb_large_prefixを使うには必須の設定事項となる

  • また、Barracudaはデータベースのバイナリーファイルを圧縮する機能をもつ。(たとえば、よく利用するデータは圧縮せずに展開したままにして、メモリー上に一時的に展開する)


AWS上(RDS)の設定を変更する場合


  • RDSのパラメータグループより上記設定が可能


2. mysqld restart


  • 設定を読み直す


3. Railsでmigrate時にrow_formatを指定する


config/initializers/ar_innodb_row_format.rb

ActiveSupport.on_load :active_record do

module ActiveRecord::ConnectionAdapters

class AbstractMysqlAdapter
def create_table_with_innodb_row_format(table_name, options = {})
table_options = options.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



row_format



  • 公式より引用: When 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.

  • つまり可変長カラムを20byteに圧縮するので、InnoDB 8KBの壁(InnoDBの行の最大長は約8KBで、これを超えようとするとエラーが発生する)を回避することができる


4. データベースの文字コードを修正する

alter database <DBNAME> character set utf8mb4; 


5. 既存のrow_formatデータを修正する

alter table <TABLENAME> ROW_FORMAT=DYNAMIC;


6. 既存のテーブルの文字コードをutf8mb4に修正する

alter table <TABLENAME> convert to character set utf8mb4;


7. Circle Ciへ変更を反映

もしCIツールとしてCircle Ciを使っているのであれば、


circle.yml

database:

override:
- mysql -u root -e 'set global innodb_file_format = Barracuda'
- mysql -u root -e 'set global innodb_large_prefix = 1'
- export RAILS_ENV=test; export RACK_ENV=test; bundle exec rake db:migrate:reset --trace

上記のようにoverrideする必要がある


Tips


ワンライナー

(mysql -uroot -p <DATABASE_NAME> -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;') > /tmp/alters.sql

上記のようにxargsを用いてmysqlコマンドファイルを作成し、

cat /tmp/alters.sql | mysql -uroot -p ...

でテーブル全てに反映する


参考