utf8mb4のCOLLATEの違いについて

はじめに　

こんにちは、新卒2年目のバックエンドエンジニアの伊藤です。

みなさんは、テーブル作成の際にCOLLATEを意識していますか？
文字の比較を行う際に文字コードと共に利用される要素のことをCOLLATEと呼び、 CHARSET毎に、デフォルトで設定されているため、意識しない人もいると思います。
ただ、このCOLLATEの設定を間違えると、欲しいデータを得られなかったり、意図しないデータを取得してしまいます。

本記事ではutf8mb4のCOLLATEについて、説明していきます。
なぜ、utf8mb4なのかについてはこちらの記事を読んでください。

utf8mb4のCOLLATEについては下記のコマンドで参照することができます。

-- MySQL version8.1.0
SHOW COLLATION WHERE Charset = 'utf8mb4';

今回は89種類もあるので、結果については省略します。
数が多く、COLLATEを全て解説はできないので、
違いが分かりやすい、下記の4つを説明します。

utf8mb4_0900_ai_ci
utf8mb4_general_ci
utf8mb4_unicode_ci
utf8mb4_bin

それぞれ、どのように違うのか検証しながら見ていきましょう。
環境はMySQL version8.1.0です。

検証

それぞれのCOLLATEのカラムを持つtestテーブルを作成し、それぞれにデータを入れていきます。
その後、それぞれのカラムに対してselectを行いどのような結果になるかを下記の観点で見ていきます。
判別ができればOK・できなければNGをいれていきます。

例)

観点	判定
ひらがな・カタカナの判別ができる	OK
半角・全角の判別ができる	NG
濁点・半濁点の判別ができる	OK
寿司・ビールの判別ができる(寿司ビール問題)	OK

※ 🍣の絵文字を取得しようとして🍺の絵文字もとれてしまうことを「寿司ビール問題」という　where句に🍣を指定して🍺を取得しなければOK!!

検証用のテーブル作成

CREATE TABLE `test` (
    `0900_column` VARCHAR(16) CHARSET utf8mb4,
    `general_column` VARCHAR(16) CHARSET utf8mb4 COLLATE utf8mb4_general_ci,
    `unicode_column` VARCHAR(16) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci,
    `bin_column` VARCHAR(16) CHARSET utf8mb4 COLLATE utf8mb4_bin
);

-- 絵文字についてはクライアントツールを使って検証していく
INSERT INTO `test` (`0900_column`, `general_column`, `unicode_column`, `bin_column`) VALUES 
 ('あああ', 'あああ', 'あああ', 'あああ'),
 ('アアア', 'アアア', 'アアア', 'アアア'),
 ('ｱｱｱ', 'ｱｱｱ', 'ｱｱｱ', 'ｱｱｱ'),
 ('ははは', 'ははは', 'ははは', 'ははは'),
 ('ばばば', 'ばばば', 'ばばば', 'ばばば'),
 ('パパパ', 'パパパ', 'パパパ', 'パパパ'),
 ('🍣', '🍣', '🍣', '🍣'),
 ('🍺', '🍺', '🍺', '🍺');

テーブルの中身

0900_column	general_column	unicode_column	bin_column
あああ	あああ	あああ	あああ
アアア	アアア	アアア	アアア
ｱｱｱ	ｱｱｱ	ｱｱｱ	ｱｱｱ
ははは	ははは	ははは	ははは
ばばば	ばばば	ばばば	ばばば
パパパ	パパパ	パパパ	パパパ
🍣	🍣	🍣	🍣
🍺	🍺	🍺	🍺

utf8mb4_0900_ai_ci

実行sql

-- ひらがな・カタカナの判別
SELECT * FROM test WHERE 0900_column = "あああ";
-- 半角・全角の判別
SELECT * FROM test WHERE 0900_column = "アアア";
-- 濁点・半濁点の判別
SELECT * FROM test WHERE 0900_column = "ははは";
-- 寿司・ビールの判別
SELECT * FROM test WHERE 0900_column = "🍣";

結果

観点	判定
ひらがな・カタカナの判別ができる	NG
半角・全角の判別ができる	NG
濁点・半濁点の判別ができる	NG
寿司・ビールの判別ができる(寿司ビール問題)	OK

utf8mb4_general_ci

実行sql

-- ひらがな・カタカナの判別
SELECT * FROM test WHERE general_column = "あああ";
-- 半角・全角の判別
SELECT * FROM test WHERE general_column = "アアア";
-- 濁点・半濁点の判別
SELECT * FROM test WHERE general_column = "ははは";
-- 寿司・ビールの判別
SELECT * FROM test WHERE general_column = "🍣";

結果

観点	判定
ひらがな・カタカナの判別ができる	OK
半角・全角の判別ができる	OK
濁点・半濁点の判別ができる	OK
寿司・ビールの判別ができる(寿司ビール問題)	NG

utf8mb4_unicode_ci

実行sql

-- ひらがな・カタカナの判別
SELECT * FROM test WHERE unicode_column = "あああ";
-- 半角・全角の判別
SELECT * FROM test WHERE unicode_column = "アアア";
-- 濁点・半濁点の判別
SELECT * FROM test WHERE unicode_column = "ははは";
-- 寿司・ビールの判別
SELECT * FROM test WHERE unicode_column = "🍣";

結果

観点	判定
ひらがな・カタカナの判別ができる	NG
半角・全角の判別ができる	NG
濁点・半濁点の判別ができる	NG
寿司・ビールの判別ができる(寿司ビール問題)	NG

utf8mb4_bin

実行sql

-- ひらがな・カタカナの判別
SELECT * FROM test WHERE bin_column = "あああ";
-- 半角・全角の判別
SELECT * FROM test WHERE bin_column = "アアア";
-- 濁点・半濁点の判別
SELECT * FROM test WHERE bin_column = "ははは";
-- 寿司・ビールの判別
SELECT * FROM test WHERE bin_column = "🍣";

全体の結果

観点	判定
ひらがな・カタカナの判別ができる	OK
半角・全角の判別ができる	OK
濁点・半濁点の判別ができる	OK
寿司・ビールの判別ができる(寿司ビール問題)	OK

まとめ

それぞれのCOLLATEにたいしての検証の結果

観点	utf8mb4_0900_ai_ci	utf8mb4_general_ci	utf8mb4_unicode_ci	utf8mb4_bin
ひらがな・カタカナの判別ができる	NG	OK	NG	OK
半角・全角の判別ができる	NG	OK	NG	OK
濁点・半濁点の判別ができる	NG	OK	NG	OK
寿司・ビールの判別ができる(寿司ビール問題)	OK	NG	NG	OK