備忘録がてらメモ。
MySQL 5.7から利用できるようになった「JSON型」について、色々と落とし穴がある。
ここから先は、とあるDatabase(スキーマ)に「hoge」というテーブルを作成したものとして説明する。
例: table.sql
CREATE TABLE hoge ( fuga json DEFAULT NULL );
[目次]
- JSON型のカラムには一般的にJSONパースできない値が登録できる
- そのままJSON型から任意の値を取得する際のダブルクォート
- 同名のキーがあってもINSERTは失敗しない
- 値に大きすぎたり小さすぎる数値を与えると内容がおかしくなる
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)の値を与えたところ、同様の症状になった。
※ 実行結果が「一昔前の崩れたアスキーアート」みたいになってしまったのはご愛嬌。
