2021-07-20

MySQLのJSONからboolean型のGenerated Columnを定義する時の注意点

確認したバージョンは、MySQL8.0.25。
JSONのtrue/falseを参照してMySQLのboolean(tinyint)にする時、ついうっかりvarchar等と同じ感覚でjson_unquoteをするとエラーになる。

テスト用の定義

create table test1 (
    id      bigint not null,
    data    json not null,
    name    varchar(32) as  (json_unquote(data->'$.name')) stored,
    flag1   boolean as      (json_unquote(data->'$.flag1')) stored, -- ※間違い
    flag2   boolean as      ((data->'$.flag2')) stored,
    constraint pk_test1 primary key(id)
);

間違い

insert into test1 set id = 1, data = '{"name":"hello", "flag1": true, "flag2": true}';

flag1列でエラー。boolean列にjson_unquoteしているから。

ERROR 1366 (HY000): Incorrect integer value: 'true' for column 'flag1' at row 1

正しい

flag2はjson_unquoteをしていないので成功する。

insert into test1 set id = 1, data = '{"name":"hello", "flag2": true}';
insert into test1 set id = 2, data = '{"name":"hello", "flag2": false}';

select * from test1;
+----+-----------------------------------+-------+-------+-------+
| id | data                              | name  | flag1 | flag2 |
+----+-----------------------------------+-------+-------+-------+
|  1 | {"name": "hello", "flag2": true}  | hello |  NULL |     1 |
|  2 | {"name": "hello", "flag2": false} | hello |  NULL |     0 |
+----+-----------------------------------+-------+-------+-------+

型は何になっている??

MySQLにはpg_typeofみたいなものはないので、Selectの結果でTEMPテーブルを作って確認します。

create temporary table tmp1
select
  (json_extract('{"name":"hello", "flag1": true}', '$.flag1')) as c1,
  (json_unquote(json_extract('{"name":"hello", "flag1": true}', '$.flag1'))) as c2
;

desc tmp1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | json     | YES  |     | NULL    | NULL  |
| c2    | longtext | YES  |     | NULL    | NULL  |
+-------+----------+------+-----+---------+-------+

select * from tmp1;
+------+------+
| c1   | c2   |
+------+------+
| true | true |
+------+------+

json_unquoteしたものはlongtextのようです。
つまり、jsonからtinyintへの変換は成功するが、longtextからtinyintへの変換は失敗するということになります。

確認してみます。

select cast(json_extract('{"name":"hello", "flag1": true}', '$.flag1') as signed) as x;
+------+
| x    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

対して、

select cast(json_unquote(json_extract('{"name":"hello", "flag1": false}', '$.flag1')) as signed) as x;
+------+
| x    |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

駄目な上に警告がでています。

 show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

検証の単純化

-- OK
select cast(true as signed);
select cast(cast('true' as json) as signed);

-- NG
select cast('true' as signed)

そんなわけで、JSONのbooleanを扱うときは、気をつけましょうということで。