MySQLで大文字小文字を区別しないのを直す

(2016-07-24)

Collationの話。

MySQL 5.6
CREATE TABLE sample (
  id SERIAL,
  name VARCHAR(30)
) ENGINE=InnoDB CHARACTER SET utf8mb4;

INSERT INTO sample (name) VALUES ('tom'),('Tom'),('TOM');

このテーブルを”tom”で絞り込むとこうなる。大文字小文字を区別していない。

mysql> SELECT * FROM sample2 WHERE name = 'tom';
+----+------+
| id | name |
+----+------+
|  1 | tom  |
|  2 | Tom  |
|  3 | TOM  |
+----+------+
3 rows in set (0.01 sec)

MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.5.1 文字列検索での大文字/小文字の区別

単純な比較操作 (>=、>、=、<、<=、ソート、およびグループ化) は、各文字の「ソート値」に基づきます。 同じソート値を持つ文字は同じ文字として扱われます。たとえば、「e」 と 「é」 が対象の照合順序で同じソート値を持つ場合は、等しいと判断されます。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.2 MySQL での文字セットと照合順序

照合順序名には、関連する文字セットの名前で始まる、通常は言語名を含む、 _ci (大文字と小文字を区別しない)、_cs (大文字と小文字を区別する)、_bin (バイナリ) のいずれかで終わる、という規則が適用されます。

ciはcase-insensitive、csというのはcase-sensitiveの略だ。 照合順序(Collation)をSHOW FULL COLUMNS FROM sampleで確認したところ、確かに区別しないutf8mb4_general_ciとなっていた。 これは、文字コードutf8mb4のデフォルトの照合順序だ。(SHOW CHARACTER SETで確認できる)

それならutf8mb4_general_csというのがあるんだなと、SHOW COLLATION LIKE 'utf8mb4%'で確認してみたが、 そんなものはなかった。どうやら区別させるためにはutf8mb4_binを使うのが正解みたいで、これをCOLLATEで指定してやるか、 カラム単位でBINARYとすると、照合順序がutf8mb4_binとなり、期待通りの結果が得られる。

CREATE TABLE sample2 (
  id SERIAL,
  name VARCHAR(30)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

CREATE TABLE sample3 (
  id SERIAL,
  name VARCHAR(30) BINARY
) ENGINE=InnoDB CHARACTER SET utf8mb4;

ALTER TABLE sample MODIFY name VARCHAR(30) BINARY;
mysql> SELECT * FROM sample2 WHERE name = 'tom';
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.01 sec)