MYSQL中的JSON类型 -云博客

MYSQL中的JSON类型

# 数据库 2024-05-05 03:51 0 14 来源：云博客

JSON是mysql5.7新增的数据类型，打破了我对mysql数据存储的概念，在列中还能存储结构化的数据。

官方文档是很好理解的学习资源。

JSON类型格式，虽然插入的时候我们可以用字符串表示插入，但是mysql内部会被识别为json格式，也有一系列特殊处理方法。

在这之前，对mysql处理json数据的path类型的参数需要有个了解

‘$‘这个代表json对象本身
‘$.a‘代表json对象的key为a的对象
‘$[0]‘，json对象是个数组时，为数组的第一个元素

当然表达式可以串联，如‘$.a[0]‘，代表

{"a": [1, 2, 3],"b": "hello"}

中的1数字。

创建

`JSON_ARRAY([val[, val] ...])`

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());+---------------------------------------------+| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |+---------------------------------------------+| [1, "abc", null, true, "11:30:24.000000"] |+---------------------------------------------+

`JSON_OBJECT([key, val[, key, val] ...])`

mysql> SELECT JSON_OBJECT(‘id‘, 87, ‘name‘, ‘carrot‘);+-----------------------------------------+| JSON_OBJECT(‘id‘, 87, ‘name‘, ‘carrot‘) |+-----------------------------------------+| {"id": 87, "name": "carrot"} |+-----------------------------------------+

字符串

直接使用字符串插入数据库中，我发现也是可以的，mysql可以识别。
比如表json_test中jdata字段如果为JSON格式，则：

insert into json_test (jdata) values ( ‘{"a": 1, "b": 2, "c": {"d": 4}}‘);

也能插入有效json

`JSON_QUOTE(string)`

mysql> SELECT JSON_QUOTE(‘null‘), JSON_QUOTE(‘"null"‘);+--------------------+----------------------+| JSON_QUOTE(‘null‘) | JSON_QUOTE(‘"null"‘) |+--------------------+----------------------+| "null" | "\"null\"" |

被quote后的字符串，似乎能作为合法的json对象直接写入

查询

mysql有一些在json中查找的函数，select中使用比较方便

`JSON_CONTAINS(target, candidate[, path])`

返回1或0是否找到

mysql> SET @j = ‘{"a": 1, "b": 2, "c": {"d": 4}}‘;mysql> SET @j2 = ‘1‘;mysql> SELECT JSON_CONTAINS(@j, @j2, ‘$.a‘);+-------------------------------+| JSON_CONTAINS(@j, @j2, ‘$.a‘) |+-------------------------------+| 1 |+-------------------------------+mysql> SELECT JSON_CONTAINS(@j, @j2, ‘$.b‘);+-------------------------------+| JSON_CONTAINS(@j, @j2, ‘$.b‘) |+-------------------------------+| 0 |+-------------------------------+

这里的path变量，就是json中的路径表示。

`JSON_EXTRACT(json_doc, path[, path] ...)`

从json中选组部分内容，可以简写为->操作。

mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g > FROM jemp > WHERE JSON_EXTRACT(c, "$.id") > 1 > ORDER BY JSON_EXTRACT(c, "$.name");+-------------------------------+-----------+------+| c | c->"$.id" | g |+-------------------------------+-----------+------+| {"id": "3", "name": "Barney"} | "3" | 3 || {"id": "4", "name": "Betty"} | "4" | 4 || {"id": "2", "name": "Wilma"} | "2" | 2 |+-------------------------------+-----------+------+3 rows in set (0.00 sec)mysql> SELECT c, c->"$.id", g > FROM jemp > WHERE c->"$.id" > 1 > ORDER BY c->"$.name";+-------------------------------+-----------+------+| c | c->"$.id" | g |+-------------------------------+-----------+------+| {"id": "3", "name": "Barney"} | "3" | 3 || {"id": "4", "name": "Betty"} | "4" | 4 || {"id": "2", "name": "Wilma"} | "2" | 2 |+-------------------------------+-----------+------+3 rows in set (0.00 sec)

`JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])`

搜索返回匹配的path

mysql> SET @j = ‘["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]‘;mysql> SELECT JSON_SEARCH(@j, ‘one‘, ‘abc‘);+-------------------------------+| JSON_SEARCH(@j, ‘one‘, ‘abc‘) |+-------------------------------+| "$[0]" |+-------------------------------+

`JSON_KEYS(json_doc[, path])`

mysql> SELECT JSON_KEYS(‘{"a": 1, "b": {"c": 30}}‘);+---------------------------------------+| JSON_KEYS(‘{"a": 1, "b": {"c": 30}}‘) |+---------------------------------------+| ["a", "b"] |+---------------------------------------+mysql> SELECT JSON_KEYS(‘{"a": 1, "b": {"c": 30}}‘, ‘$.b‘);+----------------------------------------------+| JSON_KEYS(‘{"a": 1, "b": {"c": 30}}‘, ‘$.b‘) |+----------------------------------------------+| ["c"] |+----------------------------------------------+