MySQLの大文字小文字判定テスト
MySQLの大文字小文字判定テスト
０．改訂履歴
１．はじめに
このドキュメントでは，MySQLを使って，大文字小文字の処理について確認する． 意外に知られていないMySQLのつぼというかブラックホールとして，この大文字小文字問題を抱えているようで，この挙動を知っていないとアプリケーション開発では最悪の事態になる． 将来的なバージョンアップで変更されるかもしれないという可能性もあるという事も含めて，一度検討してみる必要があるだろう．
２．char&varchar型の中身が大文字小文字が判別されない事を確認する
[shinnai@jupiter shinnai]$ /usr/bin/mysql -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.1.13-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
|
- MySQL 4.1.13である．
- テスト用のデータベースを作成する．
mysql> create database CAPS_TEST
-> ;
Query OK, 1 row affected (0.07 sec)
mysql>
|
mysql> use CAPS_TEST;
Database changed
mysql> create table TEST1 ( a char(10) );
Query OK, 0 rows affected (0.05 sec)
mysql>
|
mysql> insert into TEST1 value("A");
Query OK, 1 row affected (0.03 sec)
mysql>
|
mysql> insert into TEST1 value("a");
Query OK, 1 row affected (0.00 sec)
mysql>
|
mysql> select * from TEST1;
+------+
| a |
+------+
| A |
| a |
+------+
2 rows in set (0.00 sec)
mysql>
|
- 大文字と小文字の２行が格納されている事が確認できた．
- 小文字データを検索してみる．
mysql> select * from TEST1 where a = "a";
+------+
| a |
+------+
| A |
| a |
+------+
2 rows in set (0.02 sec)
mysql>
|
- 大文字，小文字の両方の列が選択された．
- 大文字データを検索してみる．
mysql> select * from TEST1 where a = "A";
+------+
| a |
+------+
| A |
| a |
+------+
2 rows in set (0.00 sec)
mysql>
|
- こちらも大文字，小文字の両方の列が選択された．
- つまり，MySQLでは，日本語SJIS環境では大文字小文字は判別されていない事が確認できた．
３．Binaryモードでcreate tableする
- 次にTEST2テーブルを作成する．
- この時，値を格納するフィールドbは，binary定義をつける．
mysql> create table TEST2 ( b char(10) binary );
Query OK, 0 rows affected (0.00 sec)
mysql>
|
- 前述のTEST1テーブルのテストと同じように，大文字．小文字データを投入する．
mysql> insert into TEST2 value("B");
Query OK, 1 row affected (0.00 sec)
mysql> insert into TEST2 value("b");
Query OK, 1 row affected (0.00 sec)
mysql>
|
mysql> select * from TEST2;
+------+
| b |
+------+
| B |
| b |
+------+
2 rows in set (0.00 sec)
mysql>
|
mysql> select * from TEST2 where b = "B";
+------+
| b |
+------+
| B |
+------+
1 row in set (0.03 sec)
mysql>
|
- 対象フィールドを作成する際に，binaryで指定する事によって，大文字小文字を区別できる．
４．SQL文にてbinaryを指定する
- where句にbinaryモードをつける事によって，大文字小文字を判定させる事ができる．
mysql> select * from TEST1 where a = "A";
+------+
| a |
+------+
| A |
| a |
+------+
2 rows in set (0.00 sec)
mysql> select * from TEST1 where binary a = "A";
+------+
| a |
+------+
| A |
+------+
1 row in set (0.00 sec)
mysql>
|
- プログラムを既に作成している場合は，binaryをつける事で対応可能．
５．テーブル定義を変更する場合
- 既存テーブルに対して対処する場合，全プログラムにbinaryをつけるのは非現実的なので，テーブル定義を変更してみる．
- まずは既存テーブル定義を確認する．
mysql> desc TEST1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql>
|
- モードに対しては何も記述されていない．
- create table文を確認する．
mysql> show create table TEST1;
+-------+----------------------------------------------------------------
-------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------
-------------------------+
| TEST3 | CREATE TABLE `TEST1` (
`a` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=sjis |
+-------+----------------------------------------------------------------
-------------------------+
1 row in set (0.00 sec)
mysql>
|
- alter tableで，フィールドaの定義を変更する．
mysql> alter table TEST1 modify a char(10) binary;
Query OK, 2 rows affected (0.10 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
|
mysql> desc TEST1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql>
|
- ここではわからない．
- 再度create table文を確認する．
mysql> show create table TEST1;
+-------+----------------------------------------------------------------
-------------------------------------------------------------+
| Table | Create Table|
+-------+----------------------------------------------------------------
-------------------------------------------------------------+
| TEST1 | CREATE TABLE `TEST1` (
`a` char(10) character set sjis collate sjis_bin default NULL
) ENGINE=MyISAM DEFAULT CHARSET=sjis |
+-------+----------------------------------------------------------------
-------------------------------------------------------------+
1 row in set (0.02 sec)
mysql>
|
- binaryモードとされている事が確認できた．
- 再度select文を発行する．
mysql> select * from TEST1 where a = "A";
+------+
| a |
+------+
| A |
+------+
1 row in set (0.00 sec)
mysql>
|
- 大文字小文字が判別されている．
- この状態でbinaryモードも試してみる．
mysql> select * from TEST1 where binary a = "A";
+------+
| a |
+------+
| A |
+------+
1 row in set (0.00 sec)
mysql>
|
６．オブジェクト名の検証
- オブジェクト名の大文字小文字の挙動について確認する．
- ここでいう「オブジェクト名」とは，テーブル名とフィールド名．
- 大文字小文字がまざった名前を持ったテーブルを作成する．
mysql> create table AaA ( bbb char );
Query OK, 0 rows affected (0.01 sec)
mysql>
|
- 作成した通りのテーブル名でSELECT文を実行する．
mysql> select * from AaA;
Empty set (0.00 sec)
mysql>
|
- 問題なく検索できた．
- テーブル名を全部大文字で実行してみる．
mysql> select * from AAA;
ERROR 1146 (42S02): Table 'CAPS_TEST.AAA' doesn't exist
mysql>
|
- テーブルが無いというエラーになった．
- つまり，テーブル名は大文字小文字を判定している事がわかる．
- 次に，作成した時と同じ名前でフィールドを指定してSELECT文を実行する．
mysql> select bbb from AaA;
Empty set (0.00 sec)
mysql>
|
- 問題なく実行できた．（当たり前）
- 次に，フィールド名を全部大文字に変更して実行する．
mysql> select BBB from AaA;
Empty set (0.00 sec)
mysql>
|
- 問題なく実行できた．
- 次に大文字小文字が混在したフィールド名で実行してみる．
mysql> select bBb from AaA;
Empty set (0.00 sec)
mysql>
|
- この結果から，フィールド名は大文字小文字を判定していない事がわかる．
７．おまけ
- SQL文の予約語の大文字小文字をランダムにしてみる．
mysql> sElECt BbB fRoM AaA;
Empty set (0.00 sec)
mysql>
|
- 問題なく実行できている事がわかった．
- こんなSQL文を発行しているプログラムとかを見かけたら卒倒しそうだけど．
