QA@IT

MySQLで文字列の検索をCase Sensitiveにするには?

5975 PV

MySQLで

SELECT * FROM hoge WHERE fuga = 'Piyo' ;

とすると、カラム'fuga'の値が'piyo'でも'PIYO'でも抽出されてしまいます。どうやらアルファベットの大文字 / 小文字を区別していないようです。

これはなぜでしょうか?また、大文字 / 小文字を区別するにはどうしたらよいのでしょうか?

回答

「文字列の照合順序」を適切に設定する必要があります。

文字列の照合順序

MySQLには文字コードの他に「文字列の照合順序」というパラメータがあります。

用意されているのは…

  • ci
    • Case Insensitive : 大文字小文字を区別しない
  • cs
    • Case Sensitive : 大文字小文字を区別する
  • bin
    • Binary : バイナリ比較 = 大文字小文字を区別する

…の3種類ですが、日本語を表せる文字コードには「ci」と「bin」のみが用意され、なおかつデフォルトでは「ci」が選択されるようになっています。これを気にせずテーブルを作成しクエリを発光すると、質問のような現象が発生します。

対処法

テーブル単位で文字列の照合順序を設定する

  • 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で既存カラムの文字コードと照合順序を変換することができます。
ALTER TABLE hoge2 CHANGE COLUMN ci_str ci_str VARCHAR(16) COLLATE utf8_bin ;

クエリで大文字 / 小文字の区別を指定する

  • 大文字小文字を区別するカラムで区別せずに検索するには…
SELECT * FROM hoge2 WHERE LOWER(cs_str) = 'fuga' ;
  • 大文字小文字を区別しないカラムで区別して検索するには…
SELECT * FROM hoge2 WHERE BINARY ci_str = 'fuga' ;

まとめとリンク

MySQLからRDBMSに触れた人にとっては常識でしょうが、回答者のような他のRDMBSで入門した人には意外と落とし穴になるかもしれません。

最後のは回答者のblogエントリです。手前味噌で恐縮ですがご参考までに。

編集 履歴 (1)
ウォッチ

この質問への回答やコメントをメールでお知らせします。