MySQLのイケてない仕様 - VARCHAR編
今日、MySQLは「LAMP」という言葉が示すように非常にその存在感は強い。
高速性が売りだったり、オープンソースということもあいまって非常に人気のあるベンダーのひとつだ。
今も今後も、ウェブシステムはMySQLが主流になるだろう。
個人的にはSun Micro SystemsがOracleに買収され、さらにDBベンダーのOracleがMySQLを買収して、そのオープン性が失われやしないかが心配だ。
また、MySQLがさらにOracleに近い仕様へバージョンアップするのではという期待もあったりする。
今回の問題はこの人気のMySQLのイケてない仕様について。
普通に使っていては気づかない、とてもマニアックな仕様ともいえるが、これが結構ハマるんではないだろうか。
皆さんは知っているかな？
文字列型は英大文字・小文字を区別しない。
参考URL http://homepage.mac.com/kurusaki/iblog/C1305251996/E138576755/index.html
どういうことかわかるだろうか？
実はVARCHAR型などの文字列型はデフォルトでは英字の大文字・小文字を判別するような仕様になっていない。だがデータ的には大文字・小文字はそのまま出力される。
たとえば条件句に「WHERE data = 'abcdef'」としたとする。この条件ではdataというフィールドに格納されている「abcdef」はもちろん、「ABCDEF」や「Abcdef」、「abCdEf」といったデータも条件対象になってしまう。
感覚的には「WHERE data = 'abcdef'」は「abcdef」にしかヒットしないと考えるがデフォルトはそうでない。
これを回避する方法はいくつかあるが、CREATE TABLEの段階で回避できていたほうがいいだろう。
それにはフィールドにBINARY属性を付与する。たとえば以下のような書き方になる。
CREATE TABLE sample_table (
id SERIAL,
char1 VARCHAR(120),
char2 VARCHAR(120) BINARY,
modified_date TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX char1_index (char1),
INDEX char2_index (char2)
) ENGINE=InnoDB CHARACTER SET 'utf8';
この例ではフィールドchar1は大文字・小文字を区別しない、フィールドchar2は区別することになる。ちなみにBINARY属性を使用しなかった場合でも、条件に指定して精度を高めることができる。実際にデータをいくつか投入してみて確認してほしい。
INSERT INTO sample_table (char1, char2) VALUES
('test', 'test'),('TEST', 'TEST'),('tEST', 'tEST'),('Test', 'Test');
-- 1.取得件数は4件
SELECT * FROM sample_table WHERE char1 = 'TEST';
-- 2.取得件数は1件（条件で処理する場合）
SELECT * FROM sample_table WHERE BINARY char1 = 'TEST';
-- 3.取得件数は1件
SELECT * FROM sample_table WHERE char2 = 'TEST';
ちなみにどちらのフィールドにもインデックスを張っているが、2のSELECT文のように条件でBINARY指定しちゃうとインデックスは効かないみたい。
explainの結果の見方は以下のサイトを参考に。「type」が「ALL」はフルテーブルスキャン。インデックス使わないので、VARCHAR型のフィールドにインデックス張る場合やもちろんデータ件数が多くなることが予想され、このフィールドを条件対象にする場合は、CREATE TABLEで指定しておくのが必須でだろう。
参考サイト 漢(オトコ)のコンピュータ道 MySQLの EXPLAINを徹底解説!!
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| explain SELECT * FROM sample_table WHERE char1 = 'TEST'; | |||||||||
| 1 | SIMPLE | sample_teble | ref | char1_index | char1_index | 363 | const | 2 | Using where |
| explain SELECT * FROM sample_table WHERE BINARY char1 = 'TEST'; | |||||||||
| 1 | SIMPLE | sample_teble | ALL |
|
|
|
|
4 | Using where |
|
explain SELECT * FROM sample_table WHERE char2 = 'TEST'; |
|||||||||
| 1 | SIMPLE | sample_teble | ref | char2_index | char2_index | 363 | const | 1 | Using where |
当然ながらOracleやPostgreSQLといったものはデフォルトで大文字・小文字は区別してくれる。
ちなみにphpMyAdminで見てみると、ちゃんと書いてくれていた・・・。
コメント