Description:
Below is a demonstration of behavior that, in my opinion, is not
explained anywhere in the user manual:
mysql> create table t(a json);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t values ('{"a": 127}'), ('{"a": 255}'), ('{"a": 32767}'), ('{"a": 32768}'), ('{"a": 8388607}'), ('{"a": 16777215}'), ('{"a": 9223372036854775807}'), ('{"a": 18446744073709551615}'), ('{"a": 18446744073709551616}'), ('{"a": 111.111}'), (JSON_OBJECT('a', 1.0));
Query OK, 11 rows affected (0.04 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select a->'$.a', json_type(a->'$.a'), json_storage_size(a->'$.a') from t;
+-----------------------+---------------------+-----------------------------+
| a->'$.a' | json_type(a->'$.a') | json_storage_size(a->'$.a') |
+-----------------------+---------------------+-----------------------------+
| 127 | INTEGER | 3 |
| 255 | INTEGER | 3 |
| 32767 | INTEGER | 3 |
| 32768 | INTEGER | 5 |
| 8388607 | INTEGER | 5 |
| 16777215 | INTEGER | 5 |
| 9223372036854775807 | UNSIGNED INTEGER | 9 |
| 18446744073709551615 | UNSIGNED INTEGER | 9 |
| 1.8446744073709552e19 | DOUBLE | 9 |
| 111.111 | DOUBLE | 9 |
| 1.0 | DECIMAL | 7 |
+-----------------------+---------------------+-----------------------------+
11 rows in set (0.00 sec)
https://dev.mysql.com/doc/refman/8.0/en/json-attribute-functions.html#function_json-type
lists various supported JSON value types, including the numeric
ones.
However, that page does not:
- mention their storage sizes;
- mention UNSIGNED INTEGER;
- explain how the types and their storage sizes are inferred from string
values when parsing a string into JSON;
- explain why 127 and 32767 have the same storage size;
- explain why 9223372036854775807 is stored as an UNSIGNED INTEGER
- explain if there is a way to parse and store a string value into a
JSON DECIMAL value, rather than using JSON_OBJECT()
How to repeat:
See above.