首页

MySQL中的JSON类型数据

— JSON类型数据的处理 —

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide based on https://dev.mysql.com/doc/refman/8.0/en/json.html

http://forums.mysql.com/

目录

JSON

JSON (JavaScript Object Notation)

MySQL 从 5.7 开始引入了 JSON 类型.

CREATE TABLE my_table (
id INT PRIMARY KEY,
json_data JSON
);

INSERT INTO my_table (id, json_data) VALUES (1, '{"key1": "value1", "key2": "value2"}');

mysql> select * from my_table;
+----+--------------------------------------+
| id | json_data                            |
+----+--------------------------------------+
|  1 | {"key1": "value1", "key2": "value2"} |
+----+--------------------------------------+
1 row in set (0.00 sec)

json_data 这一列存储的 JSON 类型的数据实际上是字符串. 其格式当然需要符合 JSON 的语法. 其最长能存储的长度取决于系统变量 max_allowed_packet的值.

mysql> SELECT length(json_data) FROM my_table WHERE id=1;
+-------------------+
| length(json_data) |
+-------------------+
|                36 |
+-------------------+
1 row in set (0.04 sec)

要取出 json_data 这列中 "key1" 所对应的值 "value1", 使用 JSON_EXTRACT() 函数.

mysql> SELECT JSON_EXTRACT(json_data, '$.key1') FROM my_table;
+-----------------------------------+
| JSON_EXTRACT(json_data, '$.key1') |
+-----------------------------------+
| "value1"                          |
+-----------------------------------+
1 row in set (0.00 sec)

系统变量 max_allowed_packet

下面是 MySQL 5.7.23 中的 max_allowed_packet 变量的值.

mysql> SHOW VARIABLES LIKE "%max_allowed%";
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set, 1 warning (0.01 sec)

在 MySQL 8.4.2 中, max_allowed_packet 变量的值为 67108864.

mysql> show variables like "%max_allowed%";
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| max_allowed_packet         | 67108864   |
| mysqlx_max_allowed_packet  | 67108864   |
| replica_max_allowed_packet | 1073741824 |
| slave_max_allowed_packet   | 1073741824 |
+----------------------------+------------+
4 rows in set, 1 warning (0.21 sec)

JSON 数据类型

JSON 数据类型

JSON 数组(JSON array)是指用中括号界定的以逗号为间隔符的有限个值组成的序列. 这些值可以是字符串、数值、布尔值、null、time、date、datetime 或JSON对象.

这里 null 是 JSON 的字面空值, 不能写为 NULL

• ["hello", 0, true, false, null]
• ["10:28:36.000000", "2024-12-04", "2024-12-04 10:28:36.000000"]
• [1, {"id":"007", "name":"Jason Bourne"}]

JSON 对象 (JSON object) 是指以花括号界定的以逗号为间隔符的有限个键值对组成的序列. 这里键值对形如 "key":"value" 或 "key":10 等. 这里的键必须是字符串, 值可以是字符串、数值、null、JSON数组.

例如:

• {"k1":"value", "k2":10}

JSON 数组和 JSON 对象可以包含象字符串或数值的数量值(scalar value)、JSON的字面null、JSON的字面布尔值(true和false).

JSON 对象中的键必须是字符串. 允许使用临时（日期、时间或日期时间）的标量值：

JSON数组中的元素可以是JSON对象, 而JSON对象中的键值对, 其值又可以是JSON数组. 因此允许合法的嵌套. 例如:

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

在插入数据到某一JSON类型的列时, 如果所插入的字符串被验证不是 JSON 类型, 则返回错误.

mysql> INSERT INTO my_table (id, json_data)
    -> VALUE (2, '[1, 2,');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 'my_table.json_data'.

mysql> INSERT INTO my_table (id, json_data)
    -> VALUE (2, '["hello", 0, true, NULL, false]');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 19 in value for column 'my_table.json_data'.

这里应使用小写的 null.

mysql> INSERT INTO my_table (id, json_data)
    -> VALUE (2, '["hello", 0, true, null, false]');
Query OK, 1 row affected (0.00 sec)

JSON_TYPE

使用JSON_TYPE()判别JSON的数据类型

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+
1 row in set (0.00 sec)

注意这里字符串一定要用双引号界定, 外面再用单引号界定. 其他形式都不正确.

mysql>  SELECT JSON_TYPE('["a", 'b', 1]');
ERROR 1064 (42000): Erreur de syntaxe près de 'b', 1]')' à la ligne 1

mysql>  SELECT JSON_TYPE("['a', 'b', 1]");
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 1.

mysql> SELECT JSON_TYPE('hello');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

mysql> SELECT JSON_TYPE("'hello'");
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

mysql> SELECT JSON_TYPE(""hello"");
ERROR 1064 (42000): Erreur de syntaxe près de '"")' à la ligne 1

mysql> SELECT JSON_TYPE(''hello'');
ERROR 1064 (42000): Erreur de syntaxe près de ''')' à la ligne 1

字符集

MySQL 处理 JSON 字符串时使用的字符集

MySQL 8.0 处理 JSON 字符串时使用的字符集是utf8mb4, 校对字符集使用的是 utf8mb4_bin.

使用其他字符集的字符串会被转换为utf8mb4. 对于 ascii 和 utf8mb3 字符集, 则没有必要转换到utf8mb4. 因为 ascii 和 utf8mb3 是 utf8mb4 的子集.

JSON_ARRAY

JSON_ARRAY()

JSON_ARRAY()函数将一列值转换为一个 JSON 数组.

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2024-12-04 11:02:53.000000"] |
+----------------------------------------+
1 row in set (0.02 sec)

如果参数为空, 则返回空的JSON数组.

mysql> SELECT JSON_ARRAY();
+--------------+
| JSON_ARRAY() |
+--------------+
| []           |
+--------------+
1 row in set (0.00 sec)

JSON_OBJECT

JSON_OBJECT()

JSON_OBJECT()函数将一列键值对转换为一个包含这一列键值对的JSON对象.

如果参数为空, 则返回空的JSON对象.

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT JSON_OBJECT();
+---------------+
| JSON_OBJECT() |
+---------------+
| {}            |
+---------------+
1 row in set (0.00 sec)

如果参数个数不是偶数个, 则返回错误.

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key3');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()

JSON_MERGE_PRESERVE() 将两个或更多的 JSON 文本合并, 返回组合后的 JSON 数据.

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.04 sec)

如果是若干个JSON对象进行合并, 则合并成单个对象, 其成员是各JSON对象的键值对.

mysql> SELECT JSON_MERGE_PRESERVE('{"pi": 3.1415926}', '{"key": "value"}');
+--------------------------------------------------------------+
| JSON_MERGE_PRESERVE('{"pi": 3.1415926}', '{"key": "value"}') |
+--------------------------------------------------------------+
| {"pi": 3.1415926, "key": "value"}                            |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

合并规则

关于合并规则, 参见 Normalization, Merging, and Autowrapping of JSON Values

将JSON值赋予给用户变量

将JSON值赋予给用户变量()

用户变量可以存储JSON值.

mysql> SET @j = JSON_OBJECT('country', 'China');
Query OK, 0 rows affected (0.00 sec)

mysql> select @j;
+----------------------+
| @j                   |
+----------------------+
| {"country": "China"} |
+----------------------+
1 row in set (0.00 sec)

需要注意的是, 尽管这里用户变量 @j 可以存储 JSON 值, 并且与 JSON 值具有相同的字符集和排序规则, 但它并不是 JSON 数据类型（指JSON数组和JSON对象）.

当分配给变量时, JSON_OBJECT("key","value")在将 "key","value" 生成JSON对象, 然后转换为字符串类型并赋予变量 @j.

实验(MySQL >= 5.7)

mysql> SET @k = JSON_OBJECT('key','value');
Query OK, 0 rows affected (0.00 sec)

mysql> select @j, @k;
+----------------------+------------------+
| @j                   | @k               |
+----------------------+------------------+
| {"country": "China"} | {"key": "value"} |
+----------------------+------------------+
1 row in set (0.00 sec)

将这两个变量进行合并.

mysql> select JSON_merge_preserve(@j, @k);
+--------------------------------------+
| JSON_merge_preserve(@j, @k)          |
+--------------------------------------+
| {"key": "value", "country": "China"} |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

JSON_MERGE_PRESERVE() 在合并JSON对象时, 对键值对中的键进行排序.

mysql> select JSON_MERGE_PRESERVE(@k, @j);
+--------------------------------------+
| JSON_merge_preserve(@k, @j)          |
+--------------------------------------+
| {"key": "value", "country": "China"} |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SET @L = JSON_OBJECT('1', 'math');
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT JSON_MERGE_PRESERVE(@j, @k, @L);
+---------------------------------------------------+
| JSON_MERGE_PRESERVE(@j, @k, @L)                   |
+---------------------------------------------------+
| {"1": "math", "key": "value", "country": "China"} |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @m = JSON_OBJECT("case", "[2,true,null]");
Query OK, 0 rows affected (0.00 sec)


mysql> select JSON_MERGE_PRESERVE(@j, @k, @L, @m);
+----------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE(@j, @k, @L, @m)                                        |
+----------------------------------------------------------------------------+
| {"1": "math", "key": "value", "case": "[2,true,NULL]", "country": "China"} |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select JSON_MERGE_PRESERVE(@k, @m, @j, @L);
+----------------------------------------------------------------------------+
| JSON_MERGE_PRESERVE(@k, @m, @j, @L)                                        |
+----------------------------------------------------------------------------+
| {"1": "math", "key": "value", "case": "[2,true,NULL]", "country": "China"} |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON数据使用的字符集

JSON数据使用的字符集

使用 CHARSET() 函数和 COLLATION() 函数查看变量 @j 所用的字符集和校对集.

mysql> SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+
1 row in set (0.01 sec)

由于 utf8mb4_bin 是二进制校对字符集, 因此 JSON 值的比较是大小写敏感的.

mysql> SELECT JSON_ARRAY('x')=JSON_ARRAY('X');
+---------------------------------+
| JSON_ARRAY('x')=JSON_ARRAY('X') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

对于JSON字面值 null, true, false, 也是区分大小写的, 非小写的都是无效JSON值.

mysql> SELECT JSON_VALID('null'), JSON_VALID(null);
+--------------------+------------------+
| JSON_VALID('null') | JSON_VALID(null) |
+--------------------+------------------+
|                  1 |             NULL |
+--------------------+------------------+
1 row in set (0.04 sec)

mysql> SELECT JSON_VALID('Null'), JSON_VALID(NULL);
+--------------------+------------------+
| JSON_VALID('Null') | JSON_VALID(NULL) |
+--------------------+------------------+
|                  0 |             NULL |
+--------------------+------------------+
1 row in set (0.00 sec)

MySQL 中的 CAST() 函数将某个值转换为另一种类型.

mysql> SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.01 sec)

如果不是小写的 null, 都会产生错误.

mysql> SELECT CAST('NULL' AS JSON);

mysql> SELECT CAST('Null' AS JSON);

mysql> SELECT CAST('NulL' AS JSON);

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

对比之下, SQL 中的 NULL, TRUE, FALSE 等是大小写不敏感的.

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+
1 row in set (0.00 sec)

JSON数据中有双引号

JSON数据中有双引号

有时在 JSON 文档中有必要插入一些引号(双引号"和单引号'). 此时要用转义符\. 即 \" 表示字符串中的双引号.

切换到 test_students 数据库.

mysql> use test_students;
Database changed

mysql> select * from my_table;
+----+--------------------------------------+
| id | json_data                            |
+----+--------------------------------------+
|  1 | {"key1": "value1", "key2": "value2"} |
|  2 | ["hello", 0, true, null, false]      |
|  3 | [null, 12, false, null, "hello"]     |
+----+--------------------------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO my_table VALUES
    -> (4, JSON_OBJECT("Sowya", "Sowya is a computing software that runs on \"Windows\" and \"Linux\"."));
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_table;
+----+------------------------------------------------------------------------------------+
| id | json_data                                                                          |
+----+------------------------------------------------------------------------------------+
|  1 | {"key1": "value1", "key2": "value2"}                                               |
|  2 | ["hello", 0, true, null, false]                                                    |
|  3 | [null, 12, false, null, "hello"]                                                   |
|  4 | {"Sowya": "Sowya is a computing software that runs on \"Windows\" and \"Linux\"."} |
+----+------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()

JSON_MERGE_PRESERVE

JSON_MERGE_PRESERVE()

End

Thanks very much!