Unknown Region

プログラムでハマったエラーとその解決方法についての備忘録メモ

【MySQL】JSON型のカラムを操作する場合の落とし穴

備忘録がてらメモ。

MySQL 5.7から利用できるようになった「JSON型」について、色々と落とし穴がある。

ここから先は、とあるDatabase(スキーマ)にhogeというテーブルを作成したものとして説明する。

例: table.sql

CREATE TABLE hoge (
  fuga json DEFAULT NULL
);

 


[目次]

 


JSON型のカラムには一般的にJSONパースできない値が登録できる


一番つまづきやすいポイントがここ。

以下の一連のINSERT文は、一見通らない様で通ってしまう。(Mysql 5.7で確認)

例:

# 値に単純な文字列を与える
mysql> INSERT INTO hoge (fuga) VALUES ("\"string\"");
Query OK, 1 row affected (0.04 sec)

# 値に単純な数値を与える
mysql> INSERT INTO hoge (fuga) VALUES ("1");
Query OK, 1 row affected (0.04 sec)

# 値に単純な真偽値を与える
mysql> INSERT INTO hoge (fuga) VALUES ("true");
Query OK, 1 row affected (0.04 sec)

 

つまりは、以下のSELECT文を投げた結果はこうなる

例:

mysql> SELECT fuga FROM hoge;
+----------+
| fuga     |
+----------+
| "string" |
| 1        |
| true     |
+----------+
3 rows in set (0.00 sec)

おわかりいただけただろうか。

もし仮にこの結果をJSONとして取得しようとするような処理が接続元のアプリケーションにおいて控えていた場合、JSONパースのエラーが高確率で発生する。

ちなみにJSON_TYPE」という関数を使うと「値の型」をチェックすることができる。

例:

mysql> SELECT fuga, JSON_TYPE(fuga) FROM hoge;
+----------------+-----------------+
| fuga           | JSON_TYPE(fuga) |
+----------------+-----------------+
| {"foo": "bar"} | OBJECT          |
| ["piyo"]       | ARRAY           |
| "string"       | STRING          |
| 1              | INTEGER         |
| true           | BOOLEAN         |
| NULL           | NULL            |
+----------------+-----------------+
6 rows in set (0.00 sec)

「OBJECT」「ARRAY」の場合だけ、接続元のアプリケーションではJSONパースを実施すれば良いだろうか。

 

 


そのままJSON型から任意の値を取得する際のダブルクォート


これ、個人的には結構謎な仕様。

単純に以下の様なSELECT文でJSON内の文字列の結果を取得すると、結果にダブルクォートが付随する。

例:

mysql> SELECT JSON_EXTRACT(`fuga`, '$.foo') FROM hoge;
+-------------------------------+
| JSON_EXTRACT(`fuga`, '$.foo') |
+-------------------------------+
| "bar"                         |
+-------------------------------+
1 row in set (0.00 sec)

専用のJSON_UNQUOTE」関数を使ってあげることで、ダブルクォートが取得結果から取り除かれる。

例:

mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(`fuga`, '$.foo')) FROM hoge;
+---------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(`fuga`, '$.foo')) |
+---------------------------------------------+
| bar                                         |
+---------------------------------------------+
1 row in set (0.00 sec)

 

 


同名のキーがあってもINSERTは失敗しない


割と初歩的なミスではあるが、大量のキーが存在する場合はこれで時間を浪費してしまうかもしれない。

以下のINSERT文は、エラー無しに通ってしまう(Mysql 5.7で確認)

例:

mysql> INSERT INTO hoge (fuga) VALUES ("{\"foo\": \"bar1\", \"foo\": \"bar2\"}");
Query OK, 1 row affected (0.03 sec)

そして、これの取得結果は次の通り。

例:

mysql> SELECT fuga FROM hoge;
+-----------------+
| fuga            |
+-----------------+
| {"foo": "bar1"} |
+-----------------+
1 row in set (0.00 sec)

前部分の値「bar1」しか残っていないので、「bar2」を反映させたかった場合はハマってしまうかもしれない。

 


値に大きすぎたり小さすぎる数値を与えると内容がおかしくなる


タイトルの通り。

例:

mysql> INSERT INTO hoge (fuga) VALUES ("{\"piyo\": 18446744073709551616}");
Query OK, 1 row affected (0.05 sec)

mysql> SELECT fuga FROM hoge;
+---------------------------------+
| fuga                            |
+---------------------------------+
| {"piyo": 1.8446744073709552e19} |
+---------------------------------+
1 row in set (0.02 sec)

(符号無しのbigintの最大値 + 1)を値に与えたところ、こうなった。

これ以上の値を使用したい場合は、文字列として格納するべきだろう。

ちなみに最小値の場合は(符号ありbigintの最小値 - 1)の値を与えたところ、同様の症状になった。

 

 

 

※ 実行結果が「一昔前の崩れたアスキーアート」みたいになってしまったのはご愛嬌。

 

連絡先: plugout777★yahoo.co.jp (クローラー対策のため★を@に変更してください)