MySQL 5.7小ネタ／JSONデータ型のCollationの注意点

Amazon AuroraもMySQL 5.7互換に、ということで、あらためてMySQL 5.7のJSONデータ型について調べ直しているところですが、非常に地味な小ネタがあったので書き残しておきます。

# MySQL 5.7 JSONデータ型の文字コードとCollation

通常のテーブル・カラム等を作成するときに、文字コードとして`utf8mb4`を指定すると、デフォルトのCollation（照合順序）は`utf8mb4_general_ci`（大文字小文字の区別をしない）となります。
一方、**JSONデータ型の場合は文字コードが`utf8mb4`、Collationが`utf8mb4_bin`扱い**となります（いずれも固定）。
この認識が抜けていると、文字列比較する際、思わぬ落とし穴に…。

なお、JSONデータ型のCollationについては、この本の223ページで解説されています。

 * **[詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド](http://www.shoeisha.co.jp/book/detail/9784798147406)（翔泳社）**

本家のマニュアルではこのページです（**「Creating JSON Values」**の前半）。

 * **[11.6 The JSON Data Type](https://dev.mysql.com/doc/refman/5.7/en/json.html)（MySQL 5.7 Reference Manual）**

# 確認

文字コード`utf8mb4`を指定して作成したテーブルで（Collationはデフォルトの`utf8mb4_general_ci`）、通常の`VARCHAR`型同士での結合と`VARCHAR`型と`JSON`型に含まれる（キーとなる）文字列での結合を比較してみます。

※MySQL 5.7.21 Community Editionで検証。

## DB・テーブル作成

```mysql:DB・テーブル作成
mysql> CREATE DATABASE json_test;
Query OK, 1 row affected (0.00 sec)

mysql> USE json_test;
Database changed
mysql> CREATE TABLE no_json (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, json_key VARCHAR(10) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE no_json2 (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, no_json_key VARCHAR(10) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE contains_json (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, json_doc JSON) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
```

## テーブルにテストデータを登録

```mysql:テーブルにテストデータを登録
mysql> INSERT INTO no_json SET json_key = 'abc';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO no_json SET json_key = 'ABC';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO no_json2 SET no_json_key = 'abc';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO no_json2 SET no_json_key = 'aaa';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO no_json2 SET no_json_key = 'Abc';
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO contains_json SET json_doc = '{"json_key": "abc", "json_value": 1}';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO contains_json SET json_doc = '{"json_key": "aaa", "json_value": 10}';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO contains_json SET json_doc = '{"json_key": "Abc", "json_value": 100}';
Query OK, 1 row affected (0.00 sec)
```

## 比較してみる

まずはテーブルステータスを確認します。

```mysql:テーブルステータス確認
mysql> SHOW TABLE STATUS FROM json_test;
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| contains_json | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2018-02-25 10:58:51 | 2018-02-25 10:59:37 | NULL       | utf8mb4_general_ci |     NULL |                |         |
| no_json       | InnoDB |      10 | Dynamic    |    2 |           8192 |       16384 |               0 |            0 |         0 |              3 | 2018-02-25 10:57:21 | 2018-02-25 10:58:08 | NULL       | utf8mb4_general_ci |     NULL |                |         |
| no_json2      | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2018-02-25 10:59:54 | 2018-02-25 11:00:26 | NULL       | utf8mb4_general_ci |     NULL |                |         |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
3 rows in set (0.00 sec)
```

省略したCollationには`utf8mb4_general_ci`が入っていることがわかります。

まずは`VARCHAR`型同士で結合してみます。

```mysql:VARCHAR同士で結合
mysql> SELECT * FROM no_json nj, no_json2 nj2 WHERE nj.json_key = nj2.no_json_key;
+----+----------+----+-------------+
| id | json_key | id | no_json_key |
+----+----------+----+-------------+
|  1 | abc      |  1 | abc         |
|  2 | ABC      |  1 | abc         |
|  1 | abc      |  3 | Abc         |
|  2 | ABC      |  3 | Abc         |
+----+----------+----+-------------+
4 rows in set (0.00 sec)
```

大文字・小文字が区別されていません。

次に、`VARCHAR`型と`JSON`型に含まれる文字列で結合してみます。

```mysql:VARCHARとJSONで結合
mysql> SELECT * FROM no_json nj, contains_json cj WHERE nj.json_key = cj.json_doc->'$.json_key';
+----+----------+----+--------------------------------------+
| id | json_key | id | json_doc                             |
+----+----------+----+--------------------------------------+
|  1 | abc      |  1 | {"json_key": "abc", "json_value": 1} |
+----+----------+----+--------------------------------------+
1 row in set (0.00 sec)
```

`JSON`型のCollationが`utf8mb4_bin`なので、大文字・小文字が一致した行だけが抽出されました。

因みに、`WHERE`句で`COLLATE`を指定すると…

```mysql:WHERE句でCOLLATE指定
mysql> SELECT * FROM no_json nj, contains_json cj WHERE nj.json_key = cj.json_doc->'$.json_key' COLLATE utf8mb4_general_ci;
Empty set (0.00 sec)
```

こうなってしまうので注意しましょう。

---

 * **[Qiitaに投稿したMySQL 5.7関連記事](http://hmatsu47.hatenablog.com/qiita_mysql57)**