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.
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.