MySQLで、SQLレベルでCollationを指定する

Collation、色々な所で指定できる

Oracle脳には馴染みの薄い、MySQLの「Collation」にまつわる挙動の話 - なからなLife
MySQLのCollationはどのように決まるか。そして、３つの落とし穴。 - なからなLife
と、立て続けにMySQLのCollationの話をしています。

今度は、Collationを、環境設定ではなく、「SQLレベルで使い分ける」ケースのお話です。

公式ドキュメントでは、以下の様に記載されています。

10.1.7.2 SQL ステートメントでの COLLATE の使用
COLLATE 句では、比較に対するデフォルト照合順序が何であれ、オーバーライドできます。SQL ステートメントのさまざまな個所で COLLATE を使用できます。次にいくつかの例を示します。
https://dev.mysql.com/doc/refman/5.6/ja/charset-collate.html

今回取り扱うのは、ドキュメントで紹介されている中の、「WHERE を指定した場合」として言及されている部分です。

Collationを使わないで大文字小文字を無視して検索する場合、WHERE条件の右辺左辺にUPPER/LOWERで揃える（インデックス効かない）か、格納時点でUPPER/LOWERで格納、検索の時もUPPER/LOWERを使う、といった方法が必要です。
これを、とある検索の時だけ都合よく大文字小文字無視！とかできたらラクじゃん、って話で。

さっそく実験

環境は、Linux版のMySQL5.6.31です。

まずは環境を準備しましょう。

mysql> CREATE TABLE test_coll_utf8mb4(
    ->  col1 int,
    ->  col2 varchar(10),
    ->  PRIMARY KEY(col1)
    -> ) CHARACTER set = 'utf8mb4' COLLATE = 'utf8mb4_bin';
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO test_coll_utf8mb4 (col1,col2) VALUES
    ->  (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),
    ->  (6,'a'),(7,'b'),(8,'c'),(9,'d'),(10,'e');
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_coll_utf8mb4;
+------+------+
| 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 table_name,table_collation
    -> FROM information_schema.tables
    -> WHERE table_name ='test_coll_utf8mb4';
+-------------------+-----------------+
| table_name        | table_collation |
+-------------------+-----------------+
| test_coll_utf8mb4 | utf8mb4_bin     |
+-------------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT table_name,column_name,column_type,character_set_name,collation_name
    -> FROM information_schema.columns
    -> WHERE table_name = 'test_coll_utf8mb4';
+-------------------+-------------+-------------+--------------------+----------------+
| table_name        | column_name | column_type | character_set_name | collation_name |
+-------------------+-------------+-------------+--------------------+----------------+
| test_coll_utf8mb4 | col1        | int(11)     | NULL               | NULL           |
| test_coll_utf8mb4 | col2        | varchar(10) | utf8mb4            | utf8mb4_bin    |
+-------------------+-------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

準備出来たので、SQLをレベルでCOLLATIONを指定するケースを試してみましょう。

mysql> select * from test_coll_utf8mb4 where col2  = 'a' COLLATE utf8mb4_general_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'

mysql> select * from test_coll_utf8mb4 where col2  = 'a' COLLATE utf8mb4_bin;
ERROR 1253 (42000): COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8'

えっ！？エラー？

データは「utf8mb4」で保存しているのに、SQLレベルでのCOLLAION指定では、utf8mb4系は一切使えないの？

エラーメッセージの内容が「utf8mb4_***のCOLLATIONはutf8には対応してないよ」って。

utf8？Character setはutf8mb4だし、Collationもutf8mb4系で揃えているんだよ？

試しに、MySQL様がご所望の通り、SQLで指定するCOLLATIONを「utf8」系にしてみる。

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

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

通った。
どういうことだってばよ。

データがutf8mb4（4バイトまで）なのに、照合をutf8（3バイトまで）でやったら、頭3バイトが一緒な4バイト文字が全部ヒットしちゃうじゃん。
寿司＝ビール問題再発じゃん。

魔法の言葉「SET NAMES」

ものは試しで、以下のコマンドを実行したら、SQLレベルで指定する「COLLATE utf8mb4_bin」が通っちゃいました。
「SET NAMES utf8mb4;」

公式ドキュメントにはこのように書いてあります。

10.1.4 接続文字セットおよび照合順序
（略）
2 つのステートメントは、接続関連の文字セット変数にグループとして影響します。

SET NAMES 'charset_name' [COLLATE 'collation_name']

SET NAMES は、クライアントからサーバーへの SQL ステートメントの送信に使用される文字セットを示します。したがって、SET NAMES 'cp1251' は、「このクライアントから今後受信するメッセージが文字セット cp1251 で送信される」ことを、サーバーに知らせます。また、クライアントに結果を返信するときにサーバーが使用する文字セットも指定します。(たとえば、SELECT ステートメントを使用する場合に、カラム値に使用する文字セットを指定します。)
SET NAMES 'charset_name' ステートメントは次の 3 つのステートメントと同等です。

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序

うーん。SET NAMESを発行するまでもなく、サーバーパラメータのレベルで3つとも「utf8mb4_bin」にしてあるんだけどなあ。

まさか、グローバルとセッションで値がずれてる？

mysql> show global variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | utf8mb4                                   |
| character_set_results    | utf8mb4                                   |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.23.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.03 sec)

mysql> show session variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | utf8mb4                                   |
| character_set_results    | utf8                                      |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.23.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.03 sec)

mysql> show global variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | utf8mb4                                   |
| character_set_results    | utf8mb4                                   |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.23.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.02 sec)

mysql> show session variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8mb4                                   |
| character_set_connection | utf8mb4                                   |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | utf8mb4                                   |
| character_set_results    | utf8mb4                                   |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.23.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.03 sec)

やっぱりね。

SET NAMES以外に、接続時のオプションで「--default-character-set=utf8mb4」とやってもOK。

なお、SET NAMESは「CHARACTER SET」しか変更してくれないです。

例によって、CHARACTER SETだけを指定すると、COLLTIONはCHARACTER SETに応じたデフォルトになるので、このケースだとutf8mb4_general_ciになってしまいます。

そのくせ、mysqlクライアントの起動オプションにCOLLATIONは存在していないので、COLLATION=utf8mb4_binにしたい場合は、