Oracle脳には馴染みの薄い、MySQLの「Collation」にまつわる挙動の話

Collationとは

直訳すると「照合」。
MySQL的には、「照合順序」と訳されます。

ただでさえ面倒くさい文字コードの問題ですが、データを保存する際の文字コードとは別に、データを照合するときの方法を指定することができます。
照合って何かっていうと、＝等での評価や、ソート順序の評価時に使われるものです。

大事なところなので繰り返しますが、「保存時の文字コードとは別」です。

どんなものがある？

200超ありますが、多くは
「文字コードセット_照合種別」
で定義されています。

文字コードセットは、保存時の文字コードで使われる種類と同じで、40種類ほど。
日本語環境のシステム構築では、sjis,ujis（EUC-JP）やuft8、utf8mb4あたりの文字コードセットがよく見かけられると思います。

その後ろ、照合種別になると
_bin
_general_cs
_general_ci
_言語名_ci
_言語名_cs
binary（これだけ例外）
といったパターンになります。

ciとはcsというのは、それぞれ「Case Sensitive」「Case Insencitive」ということで、大文字小文字（Case）に過敏（Sensitive）≒区別するか否かを意味します。

MySQLの場合、_ciがデフォルトになっていることが多いです。

CI、つまり「大文字小文字を区別しない」が設定されている状態とは、どういうことか。

本題についてはここからです。
理屈については、MySQLのマニュアルを読んでいただくものとして、典型的な挙動を見ていただくのが早いと思います。

utf8mb4_general_ciを設定した環境を用意し、データを投入する所まで、準備します。

mysql> CREATE DATABASE test_collate_ci
    -> DEFAULT CHARACTER SET = 'utf8mb4' 
    -> DEFAULT COLLATE = 'utf8mb4_general_ci';
Query OK, 1 row affected (0.00 sec)

mysql> use test_collate_ci;
Database changed

mysql> CREATE TABLE test_tbl1(
    ->  col1 int,
    ->  col2 varchar(10),
    ->  PRIMARY KEY(col1)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE  test_tbl1\G
*************************** 1. row ***************************
       Table: test_tbl1
Create Table: CREATE TABLE `test_tbl1` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> SELECT table_name,table_collation FROM information_schema.tables WHERE table_schema = 'test_collate_ci';
+------------+--------------------+
| table_name | table_collation    |
+------------+--------------------+
| test_tbl1  | utf8mb4_general_ci |
+------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT table_name,table_collation FROM information_schema.tables WHERE table_schema = 'test_collate_ci';
+------------+--------------------+
| table_name | table_collation    |
+------------+--------------------+
| test_tbl1  | utf8mb4_general_ci |
| test_tbl2  | utf8mb4_general_ci |
+------------+--------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test_tbl1 (col1,col2) VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_tbl2 (col1,col2) VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_tbl2 (col1,col2) VALUES (6,'a'),(7,'b'),(8,'c'),(9,'d'),(10,'e');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test_tbl1;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from test_tbl2;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | a    |
|    7 | b    |
|    8 | c    |
|    9 | d    |
|   10 | e    |
+------+------+
10 rows in set (0.00 sec)

Collationの設定はSHOW CREATE～では確認できないことがあるので、あえてinformation_schemaを参照しました。

では、とりあえず文字列型で検索してみましょう。

mysql> select * from test_tbl2 where col2 = 'a';
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    6 | a    |
+------+------+
2 rows in set (0.00 sec)

_ci、つまり「大文字小文字を区別しない」というのは、そういうことです。

ですから、結合なんかでも影響してきます。

mysql> select * from test_tbl1 as a JOIN test_tbl2 as b ON a.col2 = b.col2;
+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
|    1 | A    |    1 | A    |
|    2 | B    |    2 | B    |
|    3 | C    |    3 | C    |
|    4 | D    |    4 | D    |
|    5 | E    |    5 | E    |
|    1 | A    |    6 | a    |
|    2 | B    |    7 | b    |
|    3 | C    |    8 | c    |
|    4 | D    |    9 | d    |
|    5 | E    |   10 | e    |
+------+------+------+------+
10 rows in set (0.00 sec)

キモいのは、MySQLはデフォルト任せに環境構築すると、この「大文字小文字区別なし」のモードで出来上がってしまうこと。
このテの話が高じて、「ハハパパ問題」「ビールと寿司が同じ問題」にも発展します。

blog.kamipo.net
blog.kamipo.net

Oracleの場合、デフォルトは大文字小文字を識別します。。
ていうか、さっき調べるまでOracleに環境レベルで「大文字小文字を区別しない照合設定」の状態を作れることを知りませんでした。
NLS_COMPとNLS_SORTでできるらしいです。

MySQLで大文字小文字を区別するには

Collation設定は「_bin」を使う。utf8mb4_binにすれば、大文字小文字だけでなく、ハハパパも寿司ビールも解決。

さっきのDDLを、もう一つutf8mb4_binで作ったスキーマに流し込んで確認。

mysql> CREATE DATABASE test_collate_bin
    -> DEFAULT CHARACTER SET = 'utf8mb4' 
    -> DEFAULT COLLATE = 'utf8mb4_bin';
Query OK, 1 row affected (0.00 sec)

mysql> USE test_collate_bin;
Database changed

mysql> CREATE TABLE test_tbl1(
    ->  col1 int,
    ->  col2 varchar(10),
    ->  PRIMARY KEY(col1)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE test_tbl2(
    ->  col1 int,
    ->  col2 varchar(10),
    ->  PRIMARY KEY(col1)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE test_tbl1\G
*************************** 1. row ***************************
       Table: test_tbl1
Create Table: CREATE TABLE `test_tbl1` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test_tbl2\G
*************************** 1. row ***************************
       Table: test_tbl2
Create Table: CREATE TABLE `test_tbl2` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT table_name,table_collation FROM information_schema.tables WHERE table_schema = 'test_collate_bin';
+------------+-----------------+
| table_name | table_collation |
+------------+-----------------+
| test_tbl1  | utf8mb4_bin     |
| test_tbl2  | utf8mb4_bin     |
+------------+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT table_name,table_collation FROM information_schema.tables WHERE table_schema = 'test_collate_bin';
+------------+-----------------+
| table_name | table_collation |
+------------+-----------------+
| test_tbl1  | utf8mb4_bin     |
| test_tbl2  | utf8mb4_bin     |
+------------+-----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test_tbl1 (col1,col2) VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');
Query OK, 5 rows affected (0.21 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_tbl2 (col1,col2) VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_tbl2 (col1,col2) VALUES (6,'a'),(7,'b'),(8,'c'),(9,'d'),(10,'e');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test_tbl1;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from test_tbl2;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | a    |
|    7 | b    |
|    8 | c    |
|    9 | d    |
|   10 | e    |
+------+------+
10 rows in set (0.00 sec)

mysql> select * from test_tbl2 where col2 = 'a';
+------+------+
| col1 | col2 |
+------+------+
|    6 | a    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from test_tbl1 as a JOIN test_tbl2 as b ON a.col2 = b.col2;
+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
|    1 | A    |    1 | A    |
|    2 | B    |    2 | B    |
|    3 | C    |    3 | C    |
|    4 | D    |    4 | D    |
|    5 | E    |    5 | E    |
+------+------+------+------+
5 rows in set (0.01 sec)

期待通りの動きになりました。

環境レベルの設定変更ができない場合

どうしても「ベースはci＝大文字小文字区別なし」としたい環境で、特別に区別をしたい場合、個別のSQLの中で「binary」を使います。

最初に作ったutf8mb4_general_ciのスキーマに対して、以下のようにクエリを投げましょう。

mysql> USE test_collate_ci;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test_tbl1 as a JOIN test_tbl2 as b ON a.col2 = binary b.col2;
+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
|    1 | A    |    1 | A    |
|    2 | B    |    2 | B    |
|    3 | C    |    3 | C    |
|    4 | D    |    4 | D    |
|    5 | E    |    5 | E    |
+------+------+------+------+
5 rows in set (0.00 sec)

通常の検索と同様、使い方を間違えるとINDEX効かなくなってしまうので気をつけましょう。

まとめ

・Collationは、保存文字コードではなく、「照合順序」。
・MySQLは、デフォルトで「大文字小文字区別なし」になりがちなので、このCollation絡みでハマることがある。
・Collationの指定に「utf8mb4_bin」を指定すれば、大体の問題が片付く（暴論極論）
・Collationの指定を「大文字小文字区別なし」にできない場合、SQL単位で「BINARY」を指定することで、大文字小文字を区別できる。

どのレベルでCollationを設定できるか、的な話もソコソコ面倒くさい話になるので、別途エントリを起こそうかと思っています。

MySQLトラブルシューティング

作者: Sveta Smirnova,豊福剛
出版社/メーカー: オライリージャパン
発売日: 2012/10/13
メディア: 大型本
購入: 1人クリック: 3回
この商品を含むブログ (3件) を見る

追記

Collationとパフォーマンスの関係について、公式ドキュメントに記述があるのを見つけたので、こちらに追記しておきます。

ciよりbinの方が速いって言ってますが、どれくらい違いがあるのかはわかりません。
特に走査対象テーブルに入っているデータが全く同じ場合、違いがあるんでしょうかね？

8.4.2.2 文字および文字列型の最適化
文字および文字列カラムの場合、次のガイドラインに従います。
言語固有の照合機能が必要でない場合は、比較およびソート操作を速くするため、バイナリ照合順序を使用します。特定のクエリー内でバイナリ照合順序を使用するには、BINARY 演算子を使用できます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.2.2 文字および文字列型の最適化