(cache)MySQLで大文字小文字を区別させる

MySQLでは文字列の検索時に大文字小文字を区別しない

WHERE句で条件を指定する時に、ユーザーの名前など文字列で検索する場合が多々ある。
Oracleでは気にしたことはなかったのだが、MySQLではデフォルトの状態では大文字小文字の区別をしない。

mysql> SELECT * FROM testtable;
+------+------+
| no   | name |
+------+------+
|    1 | test |
|    2 | TEST |
|    3 | abc  |
|    4 | ABC  |
|    5 | tEsT |
|    6 | tEst |
|    7 | Test |
+------+------+

mysql> SELECT * FROM testtable;

+------+------+

| no | name |

+------+------+

| 1 | test |

| 2 | TEST |

| 3 | abc |

| 4 | ABC |

| 5 | tEsT |

| 6 | tEst |

| 7 | Test |

+------+------+

こんな感じのtableからnameが’test’のユーザーだけ引っ張りたい場合は

SELECT * FROM testtable WHERE name = 'test';

1	SELECT * FROM testtable WHERE name = 'test';

といったクエリになると思う。
実際にこのクエリを発行すると

mysql> SELECT * FROM testtable WHERE name = 'test';
+------+------+
| no   | name |
+------+------+
|    1 | test |
|    2 | TEST |
|    5 | tEsT |
|    6 | tEst |
|    7 | Test |
+------+------+

mysql> SELECT * FROM testtable WHERE name = 'test';

+------+------+

| no | name |

+------+------+

| 1 | test |

| 2 | TEST |

| 5 | tEsT |

| 6 | tEst |

| 7 | Test |

+------+------+

この様に’test’という文字列の場合は大文字小文字全てを対象に出力してしまう。

この状態を対処する方法としては２つあって

クエリにBINARY演算子を加える
対象のカラムにBINARY属性を付与する

のどちらかで対応することになる。

1.クエリにBINARY演算子を加える

これはDB側ではなく、SQLの変更で対応する方法になる。
HibernateなどのORMライブラリを使っていたりするとSQLを直書きではないため、場合によってはうまく対処できない場合もある。

もともとの

SELECT * FROM testtable WHERE name = 'test';

1	SELECT * FROM testtable WHERE name = 'test';

このクエリを

SELECT * FROM testtable WHERE name = BINARY 'test';

1	SELECT * FROM testtable WHERE name = BINARY 'test';

もしくは

SELECT * FROM testtable WHERE BINARY name = 'test';

1	SELECT * FROM testtable WHERE BINARY name = 'test';

のどちらかになるように変更することで大文字小文字を区別するようになる。

mysql>
mysql> SELECT * FROM testtable WHERE name = BINARY 'test';
+------+------+
| no   | name |
+------+------+
|    1 | test |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM testtable WHERE BINARY name = 'TEST';
+------+------+
| no   | name |
+------+------+
|    2 | TEST |
+------+------+
1 row in set (0.00 sec)

mysql>

mysql> SELECT * FROM testtable WHERE name = BINARY 'test';

+------+------+

| no | name |

+------+------+

| 1 | test |

+------+------+

1 row in set (0.00 sec)

mysql> SELECT * FROM testtable WHERE BINARY name = 'TEST';

+------+------+

| no | name |

+------+------+

| 2 | TEST |

+------+------+

1 row in set (0.00 sec)

ただ、WHERE句の条件に指定しているカラム(今回はname)にindexが貼ってある場合は
前者の name = BINARY ‘test’ を使わないとindexが効かないので注意が必要。
12.10 キャスト関数と演算子

2.対象のカラムにBINARY属性を付与する

もう一つはクエリを変更するのではなくスキーマ変更で対応する方法。
これは既にテーブルが存在する場合はALTER TABLEで対象のカラムにBINARY属性を付ける。

ALTER TABLE testtable MODIFY name VARCHAR(10) BINARY;

1	ALTER TABLE testtable MODIFY name VARCHAR(10) BINARY;

この場合も注意が必要で、既に大量のデータが格納されているテーブルの場合はALTER TABLEにかなりの時間がかかる。システムを止められない場合は’waiting for table metadata lock’が大量に発生してダウンする可能性もある。

また、対象のカラムにPrimary Keyが付けられている場合は、一度DROPする必要がある。

なので、スキーマ変更で対処する場合は対象のテーブルのコピーを作り、そのテーブルに対してALTER TABLEをし、変更が終わった後renameでシステムが使っているテーブルと入れ替えるなどの処理が必要になるかも。

一番良いのは、大文字小文字を区別して運用することがわかっているならCREATE TABLEの時点で対象のカラムにBINARY属性を付与した状態で作ること。

CREATE TABLE test(no INT(5), name VARCHAR(10) BINARY);

1	CREATE TABLE test(no INT(5), name VARCHAR(10) BINARY);