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ページで解説されています。
本家のマニュアルではこのページです（**「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)
こうなってしまうので注意しましょう。