介绍
从5.7版本开始，MySQL支持JSON类型字段，如果值是有效的JSON值，则插入成功，但如果该值不是有效的JSON字符串，则报错：
mysql> CREATE TABLE t1 (jdoc JSON);mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');mysql> INSERT INTO t1 VALUES('[1, 2,');ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.
JSON_TYPE函数，输入一个JSON字符串，返回JSON类型，如果输入的是无效JSON字符串，则报错：
mysql> SELECT JSON_TYPE('["a", "b", 1]');+----------------------------+| JSON_TYPE('["a", "b", 1]') |+----------------------------+| ARRAY |+----------------------------+mysql> SELECT JSON_TYPE('"hello"');+----------------------+| JSON_TYPE('"hello"') |+----------------------+| STRING |+----------------------+mysql> SELECT JSON_TYPE('hello');ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.
JSON_ARRAY函数，用于构造一个JSON数组：
mysql> SELECT JSON_ARRAY("a", "b", "c");+---------------------------+| JSON_ARRAY("a", "b", "c") |+---------------------------+| ["a", "b", "c"] |+---------------------------+1 row in set (0.00 sec)mysql> SELECT JSON_ARRAY('a', 1, NOW());+----------------------------------------+| JSON_ARRAY('a', 1, NOW()) |+----------------------------------------+| ["a", 1, "2015-07-27 09:43:47.000000"] |+----------------------------------------+
JSON_OBJECT函数，用于构造一个JSON对象：
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');+---------------------------------------+| JSON_OBJECT('key1', 1, 'key2', 'abc') |+---------------------------------------+| {"key1": 1, "key2": "abc"} |+---------------------------------------+
JSON_OBECT与JSON_ARRAY也可以配合嵌套使用：
mysql> select JSON_OBJECT("key1", JSON_ARRAY("a", "b"), "key2", JSON_OBJECT("key2-1", "a"));+-------------------------------------------------------------------------------+| JSON_OBJECT("key1", JSON_ARRAY("a", "b"), "key2", JSON_OBJECT("key2-1", "a")) |+-------------------------------------------------------------------------------+| {"key1": ["a", "b"], "key2": {"key2-1": "a"}} |+-------------------------------------------------------------------------------+
JSON_MERGE函数，将两个或多个JSON合并：
mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');+--------------------------------------------+| JSON_MERGE('["a", 1]', '{"key": "value"}') |+--------------------------------------------+| ["a", 1, {"key": "value"}] |+--------------------------------------------+mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3), JSON_ARRAY(4,5));+------------------------------------------------+| JSON_MERGE(JSON_ARRAY(1,2,3), JSON_ARRAY(4,5)) |+------------------------------------------------+| [1, 2, 3, 4, 5] |+------------------------------------------------+
自动规范化与合并
当对象的值重复时，会自动丢弃重复的，如下：
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');+------------------------------------------------------+| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |+------------------------------------------------------+| {"key1": 1, "key2": "abc"} |+------------------------------------------------------+
对象的自动合并：
mysql> SELECT JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}');+----------------------------------------------------+| JSON_MERGE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}') |+----------------------------------------------------+| {"a": [1, 4], "b": 2, "c": 3} |+----------------------------------------------------+
数组的自动合并：
mysql> SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');+-----------------------------------------------------+| JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]') |+-----------------------------------------------------+| [1, 2, "a", "b", true, false] |+-----------------------------------------------------+mysql> SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');+------------------------------------------------+| JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}') |+------------------------------------------------+| [10, 20, {"a": "x", "b": "y"}] |+------------------------------------------------+mysql> SELECT JSON_MERGE('1', '2');+----------------------+| JSON_MERGE('1', '2') |+----------------------+| [1, 2] |+----------------------+
在进行插入操作时，会自动规范化与合并：
mysql> CREATE TABLE t1 (c1 JSON);mysql> INSERT INTO t1 VALUES> ('{"x": 17, "x": "red"}'),> ('{"x": 17, "x": "red", "x": [3, 5, 7]}');mysql> SELECT c1 FROM t1;+-----------+| c1 |+-----------+| {"x": 17} || {"x": 17} |+-----------+
搜索与修改
JSON路径表达式获取JSON中的一个值，以指定在该文档中的操作位置。例如，以下操作获取JSON对象中Key值为name的内容：
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');+---------------------------------------------------------+| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |+---------------------------------------------------------+| "Aztalan" |+---------------------------------------------------------+
以下操作将JSON文档的第一个值设置为a：
mysql> SELECT JSON_SET('"x"', '$[0]', 'a');+------------------------------+| JSON_SET('"x"', '$[0]', 'a') |+------------------------------+| "a" |+------------------------------+
可以包含通配符或通配符*：
.[*]匹配JSON对象中所有成员的值[*]匹配JSON数组中所有元素的值
示例JSON文档：
[3, {"a": [5, 6], "b": 10}, [99, 100]]
$[0]计算结果为3$[1]计算结果为{"a": [5, 6], "b": 10}$[2]计算结果为[99, 100]$[3]计算为NULL（不存在）$[1].a计算结果为[5, 6]$[1].a[1]计算结果为6$[1].b计算结果为10$[2][0]计算结果为99
使用通配符获取多个结果示例：
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');+---------------------------------------------------------+| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |+---------------------------------------------------------+| [1, 2, [3, 4, 5]] |+---------------------------------------------------------+mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');+------------------------------------------------------------+| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |+------------------------------------------------------------+| [3, 4, 5] |+------------------------------------------------------------+
在下面的示例中，$**.b相当于生成了$.a.b与 $.c.b，它会匹配JSON对象中所有属性名称为b的值：
mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');+---------------------------------------------------------+| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |+---------------------------------------------------------+| [1, 2] |+---------------------------------------------------------+
使用JSON_SET可以修改JSON文档中指定路径的值：
mysql> SELECT JSON_SET('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);+---------------------------------------------------------------------------------+| JSON_SET('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2) |+---------------------------------------------------------------------------------+| ["a", {"b": [1, false]}, [10, 20, 2]] |+---------------------------------------------------------------------------------+1 row in set (0.00 sec)
使用JSON_INSERT可以在JSON文档中指定路径插入值，它只会添加新的值，不会替换已存在的值：
mysql> SELECT JSON_INSERT('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);+------------------------------------------------------------------------------------+| JSON_INSERT('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2) |+------------------------------------------------------------------------------------+| ["a", {"b": [true, false]}, [10, 20, 2]] |+------------------------------------------------------------------------------------+
使用JSON_REPLACE替换已经存在的值：
mysql> SELECT JSON_REPLACE('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2);+-------------------------------------------------------------------------------------+| JSON_REPLACE('["a", {"b": [true, false]}, [10, 20]]', '$[1].b[0]', 1, '$[2][2]', 2) |+-------------------------------------------------------------------------------------+| ["a", {"b": [1, false]}, [10, 20]] |+-------------------------------------------------------------------------------------+
使用JSON_REMOVE删除JSON文档中指定路径的值：
mysql> SELECT JSON_REMOVE('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[1]', '$[1].b[1]');;+----------------------------------------------------------------------------------------+| JSON_REMOVE('["a", {"b": [true, false]}, [10, 20]]', '$[2]', '$[1].b[1]', '$[1].b[1]') |+----------------------------------------------------------------------------------------+| ["a", {"b": [true]}] |+----------------------------------------------------------------------------------------+1 row in set (0.00 sec)
若有收获，就点个赞吧
0 人点赞
