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 止まらぬ進化に乗り遅れないためのテクニカルガイド（翔泳社）

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

11.6 The JSON Data Type（MySQL 5.7 Reference Manual）

確認

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

※MySQL 5.7.21 Community Editionで検証。

DB・テーブル作成

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> 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> 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型同士で結合してみます。

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型に含まれる文字列で結合してみます。

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を指定すると…

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関連記事