概要
MySQLで文字列を検索対象にした場合、一般的なデフォルト設定では大文字・小文字を区別しない。この問題にしっかりハマってしまったので、調査と対策を行ったメモを晒すことにした。
基本的なことがらなのにものすごく長いので「ダイジェスト」を読んでいただければおおむねOKにしてある。検証過程に興味のある方はその続きをどうぞ。
ダイジェスト
MySQLはデフォルトで運用すると文字列の比較 / 並び替えで大文字 / 小文字を区別しない。
大文字小文字を区別して検索するには…
…での対応が可能。
データ定義
テーブル単位
-- CREATE TABLE時にテーブルのデフォルト文字コードと照合順序を指定する
CREATE TABLE hoge1 (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
str VARCHAR(16)
)
ENGINE = InnoDB,
CHARSET = utf8,
COLLATE = utf8_bin ;
-- ALTER TABLEで文字コードと照合順序を変換する
ALTER TABLE hoge2 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin ;
カラム単位
-- CREATE TABLE時にカラムの文字コードと照合順序を指定する
CREATE TABLE hoge2 (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
ci_str VARCHAR(16),
cs_str VARCHAR(16) BINARY,
other_str VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_bin
)
ENGINE = InnoDB ;
-- カラムの文字コードと照合順序を変換する
ALTER TABLE hoge2 CHANGE COLUMN ci_str ci_str VARCHAR(16) COLLATE utf8_bin ;
データ取得
SQL
-- 大文字小文字を区別するカラムで区別せずに検索
mysql> SELECT * FROM hoge2 WHERE LOWER(cs_str) = 'fuga' ;
-- 大文字小文字を区別しないカラムで区別して検索
SELECT * FROM hoge2 WHERE BINARY ci_str = 'fuga' ;
sequel
# 普通にfilterする => カラムのcollationに依存
ruby-1.9.1-p378 > Hoge2.filter(:ci_str => 'fuga').sql<br /> => "SELECT * FROM `hoge2` WHERE (`ci_str` = 'fuga')"<br /><br /># likeでfilter => BINARYキーワードをつけてくれる
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.like 'fuga').sql<br /> => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE BINARY 'fuga')"<br /><br /># ilikeでfilter => カラムのcollationに依存
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.ilike 'fuga').sql<br /> => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE 'fuga')"<br /><br /># case-sensitiveなカラムを大文字小文字無視してSELECT
ruby-1.9.1-p378 > Hoge2.filter{ |f| [f.lower(:cs_str) => 'fuga'] }.sql<br /> => "SELECT * FROM `hoge2` WHERE ((lower(`cs_str`) = 'fuga'))"
sequelを使う場合のお勧め
- テーブル / カラムはデフォルトの照合順序(case-insensitive)にしておく
- SQLでは大文字小文字を区別したいときBINARYキーワードでキャストを行う
- sequelでは文字列でfilterする場合普段はlikeを使い、明示的に大文字小文字を同一視したいときだけilikeを使う
以上がダイジェスト。あとは検証過程なので興味と時間的余裕がある方はどうぞ。
(さらに…)