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

2016-07-24 database mysql

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)