MySQL 寿司ビール問題

Linuxめも

MySQLで4バイトのUTF-8を扱う †

MySQLでは4バイトのUTF-8を扱う場合はutf8mb4を指定する必要があります。

CREATE DATABASE データベース名 CHARACTER SET uff8mb4;

これで4バイトを使用する絵文字が登録できるようになります😁

寿司ビール問題 †

照合順序にも気をつけないと寿司ビール問題に悩まされます。

CREATE TABLE utf8mb4 (c varchar(191) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO utf8mb4 (c) VALUES('🍣');
INSERT INTO utf8mb4 (c) VALUES('🍺');
SELECT * FROM utf8mb4 WHERE c = '🍣';
🍣
🍺

🍣と🍺をINSERTし、🍣だけをSELECTしたいのに、🍺もSELECTされてしまいます。
これがいわゆる寿司ビール問題です。

デフォルトでは照合順序（Collation/コレーション）はutf8mb4_general_ciとなっています。

MySQL5.6からはutf8mb4_unicode_520_ciをサポートしているので、これに変更すると区別できるようになります。

DROP TABLE utf8mb4;
CREATE TABLE utf8mb4 (c varchar(191) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
INSERT INTO utf8mb4 (c) VALUES('🍣');
INSERT INTO utf8mb4 (c) VALUES('🍺');
SELECT * FROM utf8mb4 WHERE c = '🍣';
🍣

これで区別できるようになりました。

ハハパパ問題 †

絵文字が区別できるようになりましたが、utf8mb4_unicode_520_ciを指定していると、今度はハハパパ問題が勃発します。

INSERT INTO utf8mb4(c) VALUES ('ハハ');
INSERT INTO utf8mb4(c) VALUES ('パパ');
SELECT * FROM utf8mb4 WHERE c = 'ハハ';
ハハ
パパ

ハハだけをSELECTしたいのに、パパまでSELECTされてしまいます。

照合順序にutf8mb4_binを指定してみます。

DROP TABLE utf8mb4;
CREATE TABLE utf8mb4 (c varchar(191) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO utf8mb4 (c) VALUES('🍣');
INSERT INTO utf8mb4 (c) VALUES('🍺');
SELECT * FROM utf8mb4 WHERE c = '🍣';
🍣
INSERT INTO utf8mb4(c) VALUES ('ハハ');
INSERT INTO utf8mb4(c) VALUES ('パパ');
SELECT * FROM utf8mb4 WHERE c = 'ハハ';
ハハ

これで寿司ビール問題とハハパパ問題を両方解決することができました。

アルファベットの大文字小文字問題 †

utf8mb4_general_ciではアルファベットの大文字小文字は区別されません。
しかし、utf8mb4_binでは区別されるという動作の違いがあるので考慮しておく必要があります。

DROP TABLE utf8mb4;
CREATE TABLE utf8mb4 (c varchar(191) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO utf8mb4 (c) VALUES('A');
INSERT INTO utf8mb4 (c) VALUES('a');
SELECT * FROM utf8mb4 WHERE c = 'A';
A
a

DROP TABLE utf8mb4;
CREATE TABLE utf8mb4 (c varchar(191) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO utf8mb4 (c) VALUES('A');
INSERT INTO utf8mb4 (c) VALUES('a');
SELECT * FROM utf8mb4 WHERE c = 'A';
A

まとめ †

基本的にはデフォルト設定は変更したくないので、文字コードはuff8mb4を指定し、照合順序はデフォルトのutf8mb4_general_ciとしておく。
UTF-8の4バイト文字でSELECTされる可能性があれば照合順序はutf8mb4_binを指定しておく、
ただしアルファベットの大文字小文字が区別されることを考慮しておく。

参考

• PHP+MySQL5.5.24 utf8mb4奮闘記。Add Star
• MySQLの文字コードをutf8mb4に変更したが、絵文字がうまく入らない
• MySQL で utf8 と utf8mb4 の混在で起きること
• RailsとMySQLでiOSの絵文字に対応(UTF8MB4化)した話
• 【MySQL】照合順序とは？
• 【MovableType, MySQL】記事中に BMP 外の文字を使う

ちなみにPostgreSQLでは起こらない問題のようです。

• MySQLと寿司ビール問題