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を徹底解説!!

当然ながらOracleやPostgreSQLといったものはデフォルトで大文字・小文字は区別してくれる。

ちなみにphpMyAdminで見てみると、ちゃんと書いてくれていた・・・。

• 前へ
• 次へ