MySQL支持JSON数据类型。相比于Json格式的字符串类型,JSON数据类型的优势有:
- 存储在JSON列中的JSON文档的会被自动验证。无效的文档会产生错误;
- 最佳存储格式。存储在JSON列中的JSON文档会被转换为允许快速读取文档元素的内部格式。
存储在JSON列中的任何JSON文档的大小都受系统变量max_allowed_packet
的值的限制,可以使用JSON_STORAGE_SIZE()
函数获得存储JSON文档所需的空间。
JSON值的局部更新
在MySQL8.0中,优化器可以执行JSON列的局部就地更新,而不用删除旧文档再将整个新文档写入该列。局部更新的条件:
- 正在更新的列被声明为JSON;
- 该UPDATE语句使用任一的三个函数
JSON_SET()
,JSON_REPLACE()
或JSON_REMOVE()
更新列; - 输入列和目标列必须是同一列;
- 所有更改都使用新值替换现有数组或对象值,并且不向父对象或数组添加任何新元素;
- 新值不能大于旧值;
创建JSON值
JSON数组包含在 字符[
和]
字符中,其中为一个由逗号分隔的值列表:
["abc", 10, null, true, false]
{
和}
字符中,其中为一组由逗号分隔的键值对,键必须是字符串:{"k1": "value", "k2": 10}
在JSON数组和JSON对象的值中允许嵌套:
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]] {"k1": "value", "k2": [10, 20]}
t_json
,并向其中添加JSON值:mysql> CREATE TABLE t_json (jdoc JSON) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected, 1 warning (0.73 sec) mysql> INSERT INTO t_json VALUES('[1,2]'); Query OK, 1 row affected (0.17 sec mysql> INSERT INTO t_json VALUES('{"key1":"value1","key2":"value2"}'); Query OK, 1 row affected (0.27 sec) mysql> INSERT INTO t_json VALUES('"HELLO"'); Query OK, 1 row affected (0.20 sec)
若添加的值为非JSON格式,则报错:
mysql> INSERT INTO t_json VALUES("HELLO"); ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 't_json.jdoc'.
查看t_json
:
mysql> SELECT * FROM t_json; +--------------------------------------+ | jdoc | +--------------------------------------+ | [1, 2] | | {"key1": "value1", "key2": "value2"} | | "HELLO" | +--------------------------------------+ 3 rows in set (0.00 sec)
JSON_TYPE()
函数尝试将传入的值其解析为JSON值。如果值有效,则返回值的JSON类型,否则产生错误:
mysql> SELECT JSON_TYPE('["a","b",true,13]'); +--------------------------------+ | JSON_TYPE('["a","b",true,13]') | +--------------------------------+ | ARRAY | +--------------------------------+ 1 row in set (0.04 sec) mysql> SELECT JSON_TYPE('[a,"b",true,13]'); //注意 a ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 1.
JSON_ARRAY()
接收传入的值列表(可以为空),返回包含这些值的JSON数组:
mysql> SELECT JSON_ARRAY('ab',false,13); +---------------------------+ | JSON_ARRAY('ab',false,13) | +---------------------------+ | ["ab", false, 13] | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY(); +--------------+ | JSON_ARRAY() | +--------------+ | [] | +--------------+ 1 row in set (0.00 sec)
JSON_OBJECT()
接收传入的键值对列表(可以为空),并返回包含这些键值对的JSON对象:
mysql> SELECT JSON_OBJECT('key1','a','key2','b'); +------------------------------------+ | JSON_OBJECT('key1','a','key2','b') | +------------------------------------+ | {"key1": "a", "key2": "b"} | +------------------------------------+ 1 row in set (0.03 sec)
如果传入的参数不能组成键值对,则报错:
mysql> SELECT JSON_OBJECT('key1','value1','key2'); ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'
JSON_MERGE_PRESERVE()
获取两个或多个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.03 sec)
因此我们也可以使用以上三种方法向表中添加JSON值,可以一定程度地避免输入格式错误:
mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array')); Query OK, 1 row affected (0.19 sec) mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello')); Query OK, 1 row affected (0.09 sec) mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2))); Query OK, 1 row affected (0.14 sec) mysql> SELECT * FROM t_json; +--------------------------------------+ | jdoc | +--------------------------------------+ | [1, 2] | | {"key1": "value1", "key2": "value2"} | | "HELLO" | | ["json_array"] | | {"key": "hello"} | | [{"key": "hello"}, 1, 2] | +--------------------------------------+ 6 rows in set (0.00 sec)
JSON值的规范化,合并和自动包装
解析字符串并发现字符串是有效的JSON文档时,它在被解析时也会被规范化。对于重复的键(key
),后面的值(value
)会覆盖前面的值。如下:
mysql> SELECT JSON_OBJECT('x',1,'y',2,'x','a','x','b'); +------------------------------------------+ | JSON_OBJECT('x',1,'y',2,'x','a','x','b') | +------------------------------------------+ | {"x": "b", "y": 2} | +------------------------------------------+ 1 row in set (0.07 sec)
这种“覆盖”在向JSON列添加值时也会发生。
在MySQL8.0.3之前的版本中,与此相反,对于被重复的键,它的第一个值会被保留,后添加的值则会被抛弃。
合并JSON值
MySQL8.0.3及更高版本中,有两种合并函数:JSON_MERGE_PRESERVE()
和 JSON_MERGE_PATCH()
。下面具讨论它们的区别。
- 合并数组:
mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]'); +-------------------------------------------------------------------------+ | JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') | +-------------------------------------------------------------------------+ | [true, false] | +-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]'); +----------------------------------------------------------------------------+ | JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[1, 2]', '[true, false]') | +----------------------------------------------------------------------------+ | [1, 2, "a", "b", "c", 1, 2, true, false] | +----------------------------------------------------------------------------+ 1 row in set (0.00 sec)
合并数组时,JSON_MERGE_PRESERVE
只保留最后传入的数组参数,而JSON_MERGE_PRESERVE
则按传入顺序将数组参数连接。
- 合并对象
mysql> SELECT JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}'); +------------------------------------------------------------------------------+ | JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') | +------------------------------------------------------------------------------+ | {"a": 4, "b": 2, "c": 5, "d": 3} | +------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_MERGE_PRESERVE('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}'); +---------------------------------------------------------------------------------+ | JSON_MERGE_PRESERVE('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') | +---------------------------------------------------------------------------------+ | {"a": [3, 4], "b": 2, "c": [3, 5], "d": 3} | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
合并对象时,对于重复键,JSON_MERGE_PRESERVE
只保留最后传入的键值,而JSON_MERGE_PRESERVE
重复键的所有值保留为数组。
搜索和修改JSON值
在了解搜索和修改JSON值之前,先来看看JSON的路径语法。
路径语法
.keyName
:JSON对象中键名为keyName
的值;- 对于不合法的键名(如有空格),在路径引用中必须用双引号
"
将键名括起来,例,."key name"
; [index]
:JSON数组中索引为index
的值,JSON数组的索引同样从0开始;[index1 to index2]
:JSON数组中从index1
到index2
的值的集合;.*
: JSON对象中的所有value
;[*]
: JSON数组中的所有值;prefix**suffix
: 以prefix
开头并以suffix
结尾的路径;**.keyName
为多个路径,如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}'
,'$**.b'
指路径$.a.b
和$.c.b
;- 不存在的路径返回结果为NULL;
- 前导$字符表示当前正在使用的JSON文档
- 例子:对于数组
[3, {"a": [5, 6], "b": 10}, [99, 100]]
$[1]
为{"a": [5, 6], "b": 10}
。[1].a
为[5, 6]
。$[1].a[1]
为6
。$[1].b
为10
。$[2][0]
为99
。
搜索
JSON_EXTRACT
提取JSON值,直接看例子:
- JSON对象
mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name'); +--------------------------------------------------------+ | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') | +--------------------------------------------------------+ | "Taylor" | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*'); +-----------------------------------------------------+ | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') | +-----------------------------------------------------+ | [29, "Taylor"] | +-----------------------------------------------------+ 1 row in set (0.00 sec)
JSON数组
mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]'); +-----------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[1]') | +-----------------------------------------+ | "b" | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]'); +----------------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') | +----------------------------------------------+ | ["b", "c"] | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]'); +-----------------------------------------+ | JSON_EXTRACT('["a", "b", "c"]', '$[*]') | +-----------------------------------------+ | ["a", "b", "c"] | +-----------------------------------------+ 1 row in set (0.00 sec)
修改
JSON_REPLACE
替换值(只替换已经存在的旧值)JSON_SET
设置值(替换旧值,并插入不存在的新值)JSON_INSERT
插入值(插入新值,但不替换已经存在的旧值)JSON_REMOVE
删除JSON数据,删除指定值后的JSON文档
JSON_REPLACE
与JSON_SET
的区别:
// 旧值存在 mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere'); +----------------------------------------------------------------+ | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') | +----------------------------------------------------------------+ | {"id": 29, "name": "Mere"} | +----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere"); +------------------------------------------------------------+ | JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') | +------------------------------------------------------------+ | {"id": 29, "name": "Mere"} | +------------------------------------------------------------+ 1 row in set (0.00 sec) // 旧值不存在 mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere'); +---------------------------------------------------------------+ | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') | +---------------------------------------------------------------+ | {"id": 29, "name": "Taylor"} | +---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere'); +-----------------------------------------------------------+ | JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') | +-----------------------------------------------------------+ | {"id": 29, "cat": "Mere", "name": "Taylor"} | +-----------------------------------------------------------+ 1 row in set (0.00 sec)
JSON_INSERT
和JSON_SET
:
// 旧值存在 mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4); +-------------------------------------+ | JSON_INSERT('[1, 2, 3]', '$[1]', 4) | +-------------------------------------+ | [1, 2, 3] | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[1]', 4) | +----------------------------------+ | [1, 4, 3] | +----------------------------------+ 1 row in set (0.00 sec) //旧值不存在 mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4); +-------------------------------------+ | JSON_INSERT('[1, 2, 3]', '$[4]', 4) | +-------------------------------------+ | [1, 2, 3, 4] | +-------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4); +----------------------------------+ | JSON_SET('[1, 2, 3]', '$[4]', 4) | +----------------------------------+ | [1, 2, 3, 4] | +----------------------------------+ 1 row in set (0.00 sec)
JSON_REMOVE
:
mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[1]'); +----------------------------------+ | JSON_REMOVE('[1, 2, 3]', '$[1]') | +----------------------------------+ | [1, 3] | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_REMOVE('[1, 2, 3]', '$[4]'); +----------------------------------+ | JSON_REMOVE('[1, 2, 3]', '$[4]') | +----------------------------------+ | [1, 2, 3] | +----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name'); +-------------------------------------------------------+ | JSON_REMOVE('{"id": 29, "name": "Taylor"}', '$.name') | +-------------------------------------------------------+ | {"id": 29} | +-------------------------------------------------------+ 1 row in set (0.00 sec)
JSON值的比较和排序
可以使用=
,<
,<=
,>
,>=
,<>
,!=
,和 <=>
对JSON值进行比较。
JSON值的比较先比较值的类型。如果类型不同,则直接 返回类型的优先级的比较结果;如果类型相同,再进行值的内容的比较。
- JSON中值的类型的优先级从高到低为:
BLOB BIT OPAQUE DATETIME TIME DATE BOOLEAN ARRAY OBJECT STRING INTEGER, DOUBLE NULL
JSON值和非JSON值转换
转换规则为:
JSON | 没变化 | 没变化 |
utf8 字符类型(utf8mb4 ,utf8 ,ascii ) | 字符串被解析为JSON值 | JSON值被序列化为utf8mb4 字符串 |
其他字符类型 | 其他字符编码被隐式转换为utf8mb4 ,并按utf8 字符类型进行处理 | JSON值被序列化为utf8mb4 字符串,然后再被转换为其他字符编码。结果可能没有意义。 |
NULL | 结果为JSON类型的NULL值 | 不适用 |
Geometry类型 | ST_AsGeoJSON() 将Geometry值转换为JSON文档 | 非法操作。解决办法: 将CAST(JSON AS other type) 的结果传递给CHAR)ST_GeomFromGeoJSON() |
所有其他类型 | 转换结果是由单个标量值组成的JSON文档 | 如果JSON文档由目标类型的单个标量值组成,并且标量值可以强制转换为目标类型,则成功转换。否则,返回NULL 并发出警告。 |