MySQL 使ってて utf8_unicode_ci や utf8_general_ci とか mb4 …？ とプチ事故が起きたので解消するべく調べた。
照合順序とは
「utf8_general_ci」といった文字コードの指定してるっぽいやつは、文字列カラムにおけるコレーション（=照合順序）と呼ばれる。ここで指定された値によって、MySQL が文字列を解釈したり比較を行うようになっている。
表記は 文字コード_文字セット名_比較方法（※特殊なものとして *_bin もある）
- 文字コード
- utf8mb4 とか utf8 とか cp932 とか
- 言文字セット名
- japanese とか german とか
- Unicodeに限り、japanese はないので general か unicode か bin を選ぶ。
- （言語によって文字の特殊性があるらしく、その言語別の定義があるが日本語はない）
- 比較方法
- 3種類あある
- ci
- 大文字小文字を比較しない
- cs
- 大文字小文字を比較する
- bin
- バイナリとして判断する
- よく使う utf8... では ci と bin のみになる…？（何でー？）
どんな照合順序が使えるかを確認する
show collation というクエリを実行することで確認できる。
show collation;
| Collation | Charset | Id | Default | Compiled | Sortlen |
|---|---|---|---|---|---|
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | Yes | 1 | |
| dec8_swedish_ci | 8-Dec | 3 | Yes | Yes | 1 |
| dec8_bin | 8-Dec | 69 | Yes | 1 | |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | Yes | 1 | |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | Yes | 1 | |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | Yes | 1 | |
| latin1_german1_ci | latin1 | 5 | Yes | 1 | |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | Yes | 1 | |
| ... |
※ like など SQL を書くときに使うような演算子を利用して絞り込みができたり、 ORDER BY 並び順を設定できる。
show collation where Collation like 'utf8%';
| Collation | Charset | Id | Default | Compiled | Sortlen |
|---|---|---|---|---|---|
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | Yes | 1 | |
| utf8_unicode_ci | utf8 | 192 | Yes | 8 | |
| utf8_icelandic_ci | utf8 | 193 | Yes | 8 |
MySQL サーバーのバージョンによって使える照合順序が違うので、環境によって結果が変わることがある。（古いと utf8mb4 がないとかそういう） 5.6, 5.7 あたりならこの記事の話は問題ないはず。詳しい一覧はここで確認ができる。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14 MySQL でサポートされる文字セットと照合順序
*_unicode_ci と *_general_ci の違い
照合順序の違いによってどんなことが起こるか、実際にクエリを発行しつつ確認する。
# バージョン確認
select version();
# 5.7.17
# アルファベットの大文字小文字、半角全角
#============================================================
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'A' = 'a', 'A' = 'Ａ', 'A' = 'ａ';
# 1, 1, 1
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'A' = 'a', 'A' = 'Ａ', 'A' = 'ａ';
# 1, 0, 0
SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'A' = 'a', 'A' = 'Ａ', 'A' = 'ａ';
# 0, 0, 0
# ひらがな, 半角カタカナ + 半濁音
#============================================================
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'は' = 'ﾊﾟ';
# 1
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'は' = 'ﾊﾟ';
# 0
SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'は' = 'ﾊﾟ';
# 0
この結果をまとめると。
| 半角全角の比較 | 大文字小文字の比較 | ひらがなカタカナの比較 | |
|---|---|---|---|
| unicode_ci | できない | できない | できない |
| general_ci | できる | できない | できる |
| bin | できる | できる | できる |
というわけで general_ci か bin を使うようにするとこのあたりの比較がちゃんと出来てよさそう。
utf8 と utf8mb4 の違い
MySQL の UTF-8 は 3 バイトの幅しかなく、絵文字などを表現するためには 4 バイトが必要。きっと歴史的な背景から utf8 と utf8mb4 が分裂している。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.10.7 utf8mb4 文字セット (4 バイトの UTF-8 Unicode エンコーディング)
先程と同じように文字コードを指定して比較していく。
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'あ' = '?', '?' = '?';
# 0, 1
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
select 'あ' = '?', '?' = '?';
# 0, 1
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'あ' = '?', '?' = '?';
# 0, 0
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
select 'あ' = '?', '?' = '?';
# 0, 1
SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'あ' = '?', '?' = '?';
# 0, 0
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';
select 'あ' = '?', '?' = '?';
# 0, 0
SET NAMES 'utf8' COLLATE 'utf8_unicode_520_ci';
select 'あ' = '?', '?' = '?';
# 0, 1
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';
select 'あ' = '?', '?' = '?';
# 0, 0
// あれ、このあたり文字化けしてるかも…？ // あ=絵文字1, 絵文字1=絵文字2; と絵文字を比較してます。。
むむ、結構厄介。絵文字が入力される可能性があり、ちゃんと識別したい場合は utfmb4_bin を使うのがよさそう。
参考リンク
utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる MySQL と寿司ビール問題 - かみぽわーる