laravel使うならmysqlのcollation指定はutf8mb4_binにしておくべき

本記事では多少laravelとからめて主にmysqlのcollationについて取り上げます。

そもそもcollationって何？というところからどのように設定にすべきかというところまで解説したいと思います。mysqlのcollationについて一般的に取り扱うので、laravelやphpだけでなくアプリケーションを開発する場合に知っておくべき内容となります。

なんでlaravelとからめるの？ということですがちょうどlaravelをphpフレームワークとして使用していて実際に私が直面したユースケースを取り上げるためです。

私の開発環境ではlaravel5.5を用いていましたが、laravel5.5においてはcollationを指定しなかった場合マイグレーションの仕組みによってデフォルトで utf8_unicode_ci に設定されてしまうようです。

今回はこのケースに沿って説明していきます。

collationとは

collationとは文字列を比較する際に照合するためのルールになります。

よく使われているかはおいておいて charset=utf8mb4 において使用される collation は

• utf8mb4_unicode_ci
• utf8mb4_general_ci
• utf8mb4_bin

があります。それぞれ一致しているとみなす（照合する）仕組みが異なります。

結論から言うと私は黙ってutf8mb4_bin使っておけばいいのではないかと思います。

ちなみに今回はcharsetについては取り上げませんがこちらは間違いなく utf8mb4 一択です。

実際にcollationが違うとどうなる？

小さなサンプルを用いて実際にcollationが異なった場合の挙動を確認してみます。

バージョン

select version();
+-----------------+
| version()       |
+-----------------+
| 10.2.11-MariaDB |
+-----------------+

utf8mb4_unicode_ci の場合

アルファベットの大文字小文字を区別しなくなります。全角半角も混同します。それ以外にもひらがな・かたかなの大文字小文字も区別しなくなります。

例えば

create table t_unicode(
    id integer unsigned primary key auto_increment, 
    s varchar(128) not null
) engine=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into t_unicode(s) values('test');
insert into t_unicode(s) values('あ');

というデータを用意した際に select を発行すると

select * from t_unicode where s = 'test'; // hit
select * from t_unicode where s = 'あ'; // hit 

select * from t_unicode where s = 'TEST'; // hit
select * from t_unicode where s = 'ｔｅｓｔ'; // hit
select * from t_unicode where s = 'ＴＥＳＴ'; // hit
select * from t_unicode where s = 'ぁ'; // hit 
select * from t_unicode where s = 'ア'; // hit 
select * from t_unicode where s = 'ァ'; // hit 

このようになります。誰が期待して使うんだろう。論外ですね。

utf8mb4_general_ci の場合

アルファベットの大文字小文字を区別しなくなります。それ以外は区別されます。

create table t_general(
    id integer unsigned primary key auto_increment, 
    s varchar(128) not null
) engine=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert into t_general(s) values('test');
insert into t_general(s) values('あ');

というデータを用意した際に

select * from t_general where s = 'test'; // hit
select * from t_general where s = 'あ'; // hit 

select * from t_general where s = 'TEST'; // hit
select * from t_general where s = 'ｔｅｓｔ'; // not hit
select * from t_general where s = 'ＴＥＳＴ'; // not hit
select * from t_general where s = 'ぁ'; // not hit 
select * from t_general where s = 'ア'; // not hit 
select * from t_general where s = 'ァ'; // not hit 

このようになります。用途によっては期待する場合もあると思います。

utf8mb4_bin の場合

完全に文字の一致を照合します。

create table t_bin(
    id integer unsigned primary key auto_increment, 
    s varchar(128) not null
) engine=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into t_bin(s) values('test');
insert into t_bin(s) values('あ');

というデータを用意した際に

select * from t_bin where s = 'test'; // hit
select * from t_bin where s = 'あ'; // hit 

select * from t_bin where s = 'TEST'; // not hit
select * from t_bin where s = 'ｔｅｓｔ'; // not hit
select * from t_bin where s = 'ＴＥＳＴ'; // not hit
select * from t_bin where s = ''; // not hit
select * from t_bin where s = 'ぁ'; // not hit 
select * from t_bin where s = 'ア'; // not hit 
select * from t_bin where s = 'ァ'; // not hit 

このようになります。通常期待するのはこちらだと思います。

結論

通常期待するのは utf8mb4_bin のケースだと思いますので、黙って utf8mb4_bin を設定するのが一番困らないと思います。

またユースケースによっては utf8mb4_general_ci のようにアルファベットの大文字小文字を区別しないような結果を期待することもあると思います。その際には select文を発行する際にcollationをしてしてい指定する というのが良いのではないかと思います。

既存のテーブルのcollationを変更する

私のように愚かにも間違ったcollationのままかなりの開発進めてしまった場合、テーブルを削除するということがなかなか難しいこともあります。そのような場合はcollationを変更しましょう。

例えば先程の例で使用した t_unicode の collation を utf8mb4_bin に変更する場合は下記のようなalter table文を発行します。

alter table t_unicode convert to character set utf8mb4 collate utf8mb4_bin

先程のsqlを再度確認してみます。

select * from t_unicode where s = 'test'; // hit
select * from t_unicode where s = 'あ'; // hit 

select * from t_unicode where s = 'TEST'; // not hit
select * from t_unicode where s = 'ｔｅｓｔ'; // not hit
select * from t_unicode where s = 'ＴＥＳＴ'; // not hit
select * from t_unicode where s = 'ぁ'; // not hit 
select * from t_unicode where s = 'ア'; // not hit 
select * from t_unicode where s = 'ァ'; // not hit 

無事に utf8mb4_bin へと変更されているようです。

laravelでマイグレーションを行う場合

後から変更も可能ですが、できればテーブルを作成する際に collation を設定するほうがいいです。laravelマイグレーションガイドにあるようにcollationを指定しましょう。

Schema::create('t_bin', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('s', 128);
    $table->collation = 'utf8mb4_bin';
});