[MySQL] 文字列検索に大小文字を区別する

MySQLの文字列型フィールドにおける大小文字区別について調べてみた。

テストデータの作成

テーブルを作る。

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
);

レコードを追加する。

INSERT INTO users (name) VALUES ('test');
INSERT INTO users (name) VALUES ('TEST');
INSERT INTO users (name) VALUES ('Test');

普通に検索すると大小文字は区別されない

SELECT * FROM users WHERE name = 'test';
+----+------+
| id | name |
+----+------+
|  1 | test |
|  2 | TEST |
|  3 | Test |
+----+------+
3 rows in set (0.00 sec)

SELECT * FROM users WHERE name = 'TEST';
+----+------+
| id | name |
+----+------+
|  1 | test |
|  2 | TEST |
|  3 | Test |
+----+------+
3 rows in set (0.00 sec)

SELECT * FROM users WHERE name = 'Test';
+----+------+
| id | name |
+----+------+
|  1 | test |
|  2 | TEST |
|  3 | Test |
+----+------+
3 rows in set (0.00 sec)

大小文字を区別するにはフィールド属性にBINARYを追加する

ALTER TABLE users MODIFY name VARCHAR(32) BINARY;

テーブル作成時にフィールド属性にBINARYを指定しても同様。

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) BINARY,
);

BINARY属性が付加された文字列はビット値として比較されるようになるらしい。

SELECT * FROM users WHERE name = 'test';
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+
1 row in set (0.00 sec)

SELECT * FROM users WHERE name = 'TEST';
+----+------+
| id | name |
+----+------+
|  2 | TEST |
+----+------+
1 row in set (0.00 sec)

SELECT * FROM users WHERE name = 'Test';
+----+------+
| id | name |
+----+------+
|  3 | Test |
+----+------+
1 row in set (0.00 sec)

あるいは、検索時にBINARY指定を追加する

SELECT * FROM users WHERE name = BINARY 'test';
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+
1 row in set (0.10 sec)

SELECT * FROM users WHERE name = BINARY 'TEST';
+----+------+
| id | name |
+----+------+
|  2 | TEST |
+----+------+
1 row in set (0.00 sec)

SELECT * FROM users WHERE name = BINARY 'Test';
+----+------+
| id | name |
+----+------+
|  3 | Test |
+----+------+
1 row in set (0.00 sec)

参考サイト

CHAR型とVARCHAR型 – MySQLのデータ型 – MySQLの使い方
http://www.dbonline.jp/mysql/type/index3.html