環境:
MySQL Server version: 5.6.19
Rails 4.1.5
iPhone の絵文字を MySQL に登録しようとしたらMysql2::Error: Incorrect string value:...
というエラーが出た。3バイトに収まらない4バイトUTF-8を扱うには MySQL 5.5.3 以上でキャラクタセットをutf8mb4
にする必要がある。
database.yml の encoding を utf8 から utf8mb4 に変更した。
config/database.yml
修正前:
default: &default adapter: mysql2 encoding: utf8 pool: 5 username: root password: socket: /tmp/mysql.sock development: <<: *default database: foo_app_development test: <<: *default database: foo_app_test production: <<: *default database: foo_app_production username: foo_app password: <%= ENV['FOO_APP_DATABASE_PASSWORD'] %>
修正後:
default: &default adapter: mysql2 encoding: utf8mb4 pool: 5 username: root password: socket: /tmp/mysql.sock # snip
そして以下を実行。
$ bin/rake db:migrate:reset
database.yml とマイグレーションファイルから DB 削除 -> DB 作成 -> テーブル作成を一発でやってくれる。
しかし今度はテーブル作成時に以下のエラーが出てしまった。
Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE UNIQUE INDEX `index_users_on_uuid` ON `users` (`uuid`)
UUIDを入れるカラムvarchar(255)
にユニークインデックスを張っているが key が最大値の767 byte を超えているというエラーのようだ。
参考:
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix limit is 1000 bytes.
(innodb_large_prefixオプションを有効にすれば 3072 byte まで増やせるようだ)
UUID を入れるカラムなので36文字あればいいのでマイグレーションファイルのカラム定義のところでvarchar(36)
となるように文字数を制限する修正を入れた。
class CreateUsers < ActiveRecord::Migration def change create_table :users do |t| t.string :uuid, null: false, limit: 36 t.string :nickname t.timestamps end add_index :users, :uuid, unique: true end end
再度、migrate コマンドを実行したところ問題なく作成され、テーブルの CHARSET がutf8mb4
になった。(COLLATE については MySQL のデフォルトではxxx_general_ci
だけどActiveRecord ではxxx_unicode_ci
がデフォルトらしい)
mysql> show create database foo_app_development\G *************************** 1. row *************************** Database: foo_app_development Create Database: CREATE DATABASE `foo_app_development` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ mysql> show create table users; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL, `nickname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_users_on_uuid` (`uuid`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
これで iPhone の絵文字が登録できるようになった。
COLLATE をutf8mb4_general_ci
にしたい場合は以下のようにcollation
も追加しておく。
default: &default adapter: mysql2 encoding: utf8mb4 collation: utf8mb4_general_ci pool: 5 username: root password: socket: /tmp/mysql.sock
今回はクライアント側の設定で対応したがmy.cnf
の変更が可能であればそちらで設定してもよい。
その場合はクライアント側の encoding や collation での設定は不要になる。
※ 追記:
MySQL のキャラクタセット設定について
MySQLでは、サーバ全体 / データベース / テーブル / カラム という単位で CHARSET、COLLATE を指定できる。
- サーバ全体
my.cnf
の [mysqld] セクションでcharacter-set-server
を設定し、MySQL を再起動。
character-set-server=utf8mb4 collation-server=utf8mb4_general_ci
- データベース
ALTER DATABASE <データベース名> CHARACTER SET utf8mb4; ALTER DATABASE <データベース名> COLLATE utf8mb4_general_ci;
- テーブル
ALTER TABLE <テーブル名> CHARACTER SET utf8mb4; ALTER TABLE <テーブル名> COLLATE utf8mb4_general_ci;
- カラム
ALTER TABLE <テーブル名> MODIFY COLUMN <カラム名> <データ型> CHARACTER SET utf8mb4; ALTER TABLE <テーブル名> MODIFY COLUMN <カラム名> <データ型> COLLATE utf8mb4_general_ci;
キャラクタセットのシステム変数
キャラクタセットのシステム変数がたくさんあってややこしい。
mysql> show variables like 'character\_set\_%'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | +--------------------------+--------+
クライアント側のキャラクタセットに関係する変数はcharacter_set_client
とcharacter_set_connection
とcharacter_set_results
の3つで、クエリ送受信時のキャラクタセットを設定する。
character_set_server
は新しく database を作るときのデフォルトを設定するものなので、 (clearDB で使える database は最初に割り当てられた1つのみで追加作成はできない) latin1 のまま変更する必要はない。
character_set_database
は、 デフォルト database に合わせて (つまり USE コマンドを発行するごとに) この変数に対象の database に設定されているキャラクタセットが設定される。
character_set_filesystem
はファイルシステムのキャラクタセット。binary
のままでよい。
character_set_system
はシステムの使用するキャラクタセット。utf8
でよい。