MySQLとsequelで文字列の照合順序について調べる // Kwappa研究開発室

概要

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
 => "SELECT * FROM `hoge2` WHERE (`ci_str` = 'fuga')"

# likeでfilter => BINARYキーワードをつけてくれる
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.like 'fuga').sql
 => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE BINARY 'fuga')"

# ilikeでfilter => カラムのcollationに依存
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.ilike 'fuga').sql
 => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE 'fuga')"

# case-sensitiveなカラムを大文字小文字無視してSELECT
ruby-1.9.1-p378 > Hoge2.filter{ |f| [f.lower(:cs_str) => 'fuga'] }.sql
 => "SELECT * FROM `hoge2` WHERE ((lower(`cs_str`) = 'fuga'))"

sequelを使う場合のお勧め

テーブル / カラムはデフォルトの照合順序(case-insensitive)にしておく
SQLでは大文字小文字を区別したいときBINARYキーワードでキャストを行う
sequelでは文字列でfilterする場合普段はlikeを使い、明示的に大文字小文字を同一視したいときだけilikeを使う

以上がダイジェスト。あとは検証過程なので興味と時間的余裕がある方はどうぞ。

いきさつ

久しぶりにケータイ関連のお話。日本のフィーチャーホン(いわゆるガラケー)は個体識別情報というのを持っていて、これを使ってアクセスしてきたユーザを特定する。

勝手サイト(キャリアが公認していないサイト)においての取得方法は過去何度か書いてきた(「ユーザエージェントからキャリア種別と端末名を知る」 / 「iモードIDを利用する」)。公式サイトの場合は守秘義務があって書けないのだが、キャリアからもらえるドキュメントに書いてある。

各キャリアの公式 / 勝手とも英数字の組み合わせでできており、大文字 / 小文字は区別される。つまり「abc」と「Abc」は別のIDになるのだ。

で、現在開発しているプロジェクトで初めて「IDが衝突している」という事態に遭遇し、調べてみたら大文字 / 小文字を判別せずにSELECTしていたのが原因だった、というわけ。

collationとは？

「照合順序」のこと。文字データの並べ替えと比較の順序について定められたルール。今回はこのうち、文字列の比較をするときアルファベットの大文字小文字を区別する(case-sensitive)かしないのか(case-insensitive)についてのお話。

MySQLにおける照合順序

MySQLはCREATE TABLEによるテーブル生成時に、そのテーブルの照合順序(colllation)を設定する。指定がなかった場合は文字コードデフォルトの照合順序を使用する。

まずは文字コードの一覧を見てみよう。

mysql> SHOW CHARSET ;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)
<em>※日本語に関係する文字コードのみ選抜</em>

デフォルトはぜんぶ末尾が「_ci」。これはcase-insencitiveの略で、つまり大文字小文字を区別しない。

では区別する照合順序は？ということで、照合順序の一覧を見てみる。

mysql> SHOW COLLATION ;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
| ascii_general_ci     | ascii    |  11 | Yes     | Yes      |       1 |
| ascii_bin            | ascii    |  65 |         | Yes      |       1 |
| ujis_japanese_ci     | ujis     |  12 | Yes     | Yes      |       1 |
| ujis_bin             | ujis     |  91 |         | Yes      |       1 |
| sjis_japanese_ci     | sjis     |  13 | Yes     | Yes      |       1 |
| sjis_bin             | sjis     |  88 |         | Yes      |       1 |
| utf8_general_ci      | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin             | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci      | utf8     | 192 |         | Yes      |       8 |
| cp932_japanese_ci    | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin            | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci  | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin          | eucjpms  |  98 |         | Yes      |       1 |
+----------------------+----------+-----+---------+----------+---------+
126 rows in set (0.00 sec)
<em>※日本語に関係する文字コードのみ選抜</em>

※日本語に関係する文字コードのみ選抜

日本語に関係する照合順序は「_ci」か「_bin」しかない。

ちなみにドキュメントには…

「_ci (大文字と小文字が区別されない)、_cs (大文字と小文字が区別される)、_bin (バイナリ)のいずれかで終わる。」
MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.2 MySQLにおけるキャラクタセットおよび照合順序

http://dev.mysql.com/doc/refman/5.1/ja/charset-mysql.html

…と書いてあるので、好奇心から調べてみたら…

mysql> SHOW COLLATION LIKE '%cs' ;
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| latin1_general_cs  | latin1  | 49 |         | Yes      |       1 |
| latin2_czech_cs    | latin2  |  2 |         | Yes      |       4 |
| cp1250_czech_cs    | cp1250  | 34 |         | Yes      |       2 |
| latin7_estonian_cs | latin7  | 20 |         | Yes      |       1 |
| latin7_general_cs  | latin7  | 42 |         | Yes      |       1 |
| cp1251_general_cs  | cp1251  | 52 |         | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+
6 rows in set (0.01 sec)

つまりは「_bin」を使えということだろう。

テーブルとカラムの定義

テーブル単位での設定と変換

CREATE TABLEで文字コードと照合順序の「デフォルト」を設定することができる。

CREATE TABLEで指定するには

table_option(ENGINEとかCHARSETとか書くところ)で指定することができる。

CREATE TABLE hoge0 (
  id  INTEGER PRIMARY KEY AUTO_INCREMENT,
  str VARCHAR(16)
 )
 ENGINE = InnoDB,
 CHARSET = utf8 ;

mysql> CREATE TABLE hoge1 (
  id  INTEGER PRIMARY KEY AUTO_INCREMENT,
  str VARCHAR(16)
 )
 ENGINE = InnoDB,
 CHARSET = utf8,
 COLLATE = utf8_bin ;

mysql> SHOW TABLE STATUS LIKE 'hoge%' ;
+-------+--------+-      -+-----------------+----------+-
| Name  | Engine | (snip) | Collation       | Checksum | (snip)
+-------+--------+-      -+-----------------+----------+-
| hoge0 | InnoDB | (snip) | utf8_general_ci |  NULL    | (snip)
| hoge1 | InnoDB | (snip) | utf8_bin        |  NULL    | (snip)
+-------+--------+-      -+-----------------+----------+-
2 rows in set (0.00 sec)

mysql> INSERT INTO hoge0 SET str = 'PiYo' ;
mysql> INSERT INTO hoge1 SET str = 'PiYo' ;

mysql> SELECT * FROM hoge0 WHERE str = 'piyo' ;
+----+------+
| id | str |
+----+------+
| 1 | PiYo |
+----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM hoge1 WHERE str = 'piyo' ;
Empty set (0.00 sec)

おお区別している。

ALTER TABLEで変換するには

ALTER TABLEで既存のカラムの照合順序を変更することができる。ただし文字コードの変換を伴うので注意が必要な場面もあるかもしれない。

mysql> ALTER TABLE hoge0 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin ;

「CONVERT TO」をつけないと「テーブルのデフォルト文字コード(と照合順序)」を変更する。つまり、既存カラムはutf8_general_ciの(=大文字小文字を区別しない)ままであることに注意。

カラム単位での設定と変換

CREATE TABLEで指定するには

CREATE TABLE時にBINARYキーワードを付与する。CHARACTER SETとCOLLATE句で指定してもよい。

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 ;

mysql> INSERT INTO hoge2 SET ci_str = 'FuGa', cs_str = 'FuGa' , other_str = 'FuGa' ;

-- ci_strは大文字小文字を気にしない
mysql> SELECT * FROM hoge2 WHERE ci_str = 'fuga' ;
+----+--------+--------+-----------+
| id | ci_str | cs_str | other_str |
+----+--------+--------+-----------+
|  1 | FuGa   | FuGa   | FuGa      |
+----+--------+--------+-----------+
1 row in set (0.00 sec)
-- cs_strは大文字小文字を気にする
mysql> SELECT * FROM hoge2 WHERE cs_str = 'fuga' ;
Empty set (0.01 sec)

ALTER TABLEで変換するには

ALTER TABLEで変更することもできる。CHANGE COLUMNでのカラム属性変更では新しい名前 / データ型を省略できないのでちょっと冗長な記述になる。

-- ほかの定義はそのままにCOLLATEだけを指定する
mysql> ALTER TABLE hoge2 CHANGE COLUMN ci_str ci_str VARCHAR(16) COLLATE utf8_bin ;
-- さっきと同じSELECTを発行しても結果がない
mysql> SELECT * FROM hoge2 WHERE ci_str = 'fuga' ;
Empty set (0.00 sec)

CONVERT TOで文字コードを変更するときもCOLLATE句で指定できる。指定しないとデフォルトの照合順序が設定されるので…

-- utf8_general_ciに戻るはず(=大文字小文字を気にしない)
mysql> ALTER TABLE hoge2 CONVERT TO CHARACTER SET utf8 ;
-- 試してみよう
mysql> SELECT * FROM hoge2 WHERE ci_str = 'fuga' ;
+----+--------+--------+
| id | ci_str | cs_str |
+----+--------+--------+
|  1 | FuGa   | FuGa  |
+----+--------+--------+
1 row in set (0.00 sec)

書き方はいろいろあるので詳細は公式ドキュメントを参照されたい。

SQLによる大文字 / 小文字の区別

テーブルの変更を行いたくない / 行うのが難しい場合、検索のクエリでなんとかすることになる。

大文字小文字を区別するカラムで区別せずに検索

-- 検索対象のカラムを小文字にしてから検索
mysql> SELECT * FROM hoge2 WHERE LOWER(cs_str) = 'fuga' ;

大文字小文字を区別しないカラムで区別して検索

-- BINARYキーワードをつけて(=キャストして)検索
mysql> SELECT * FROM hoge2 WHERE BINARY ci_str = 'fuga' ;
Empty set (0.00 sec)
mysql> SELECT * FROM hoge2 WHERE BINARY ci_str = 'FuGa' ;
+----+--------+--------+-----------+
| id | ci_str | cs_str | other_str |
+----+--------+--------+-----------+
|  1 | FuGa   | FuGa   | FuGa      |
+----+--------+--------+-----------+
1 row in set (0.00 sec)

sequelによる大文字 / 小文字の区別

migrationによるデータ定義

create_tableメソッドに渡すオプションとして照合順序を指定することができる。

class CreateTestTable < Sequel::Migration
 def up
  create_table :hoge3, { :engine => 'InnoDB', :collate => 'utf8_bin' } do
   primary_key  :id
   String   :cs_str, :size => 16
  end
 end
end

alter tableを発行するメソッドでの書き方はわからなかった…ので、既存テーブルの照合順序変更をmigrationに含めるのであれば、Sequel::Database#runメソッドに直接SQLを書く必要がありそうだ。

filterで発行されるSQL

SQLとその結果についてはたっぷり書いたので、ここではsequelでfilterするときの書き方とSQL出力を貼り付けるにとどめておく。

# irbで試す準備
ruby-1.9.1-p378 > DB = Sequel::connect("mysql://#{user}:#{pw}@#{host}/#{database}")
ruby-1.9.1-p378 > class Hoge2 < Sequel::Model(:hoge2) ; end
# 普通にfilterする => カラムのcollationに依存
ruby-1.9.1-p378 > Hoge2.filter(:ci_str => 'fuga').sql
 => "SELECT * FROM `hoge2` WHERE (`ci_str` = 'fuga')"

# likeでfilter => BINARYキーワードをつけてくれる
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.like 'fuga').sql
 => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE BINARY 'fuga')"

# ilikeでfilter => カラムのcollationに依存
ruby-1.9.1-p378 > Hoge2.filter(:ci_str.ilike 'fuga').sql
 => "SELECT * FROM `hoge2` WHERE (`ci_str` LIKE 'fuga')"

# case-sensitiveなカラムを大文字小文字無視してSELECT
ruby-1.9.1-p378 > Hoge2.filter{ |f| [f.lower(:cs_str) => 'fuga'] }.sql
 => "SELECT * FROM `hoge2` WHERE ((lower(`cs_str`) = 'fuga'))"

まとめ

sequelを使う場合、case-sencitiveなカラムをcase-insencitiveに比較する書き方が面倒になるので、照合順序はデフォルトにしつつlike / ilikeを使い分けるのがよさそうだ。

テーブル / カラムはデフォルトの照合順序(case-insensitive)にしておく
SQLでは大文字小文字を区別したいときBINARYキーワードでキャストを行う
sequelでは文字列でfilterする場合普段はlikeを使い、明示的に大文字小文字を同一視したいときだけilikeを使う

私はPostgreSQLを使っていた時期が長かったので、まさかデフォルトがcase-insencitiveだとは想像もできなかった。思い込みって恐ろしいなーと、この年になってもまだ思い知らされている。

概要

ダイジェスト

データ定義

テーブル単位

カラム単位

データ取得

SQL

sequel

sequelを使う場合のお勧め

いきさつ

collationとは？

MySQLにおける照合順序

テーブルとカラムの定義

テーブル単位での設定と変換

CREATE TABLEで指定するには

ALTER TABLEで変換するには

カラム単位での設定と変換

CREATE TABLEで指定するには

ALTER TABLEで変換するには

SQLによる大文字 / 小文字の区別

大文字小文字を区別するカラムで区別せずに検索

大文字小文字を区別しないカラムで区別して検索

sequelによる大文字 / 小文字の区別

migrationによるデータ定義

filterで発行されるSQL

まとめ

Links