MySQL之JSON数据类型操作示例

justry_deng

已于 2022-01-19 23:30:02 修改

阅读量8.3k

点赞数 5

分类专栏： MySQL 文章标签： MYSQL的json字段类型 MYSQL使用json MYSQL查询json MYSQL修改json字段 java

于 2018-12-19 16:58:54 首次发布

本文链接：https://blog.csdn.net/justry_deng/article/details/85099193

版权

MySQL 专栏收录该内容

8 篇文章 3 订阅

订阅专栏

MYSQL5.7.8开始，就支持JSON数据类型的存储了。

对于行数据整体而言，基本的增删改查是一样的；对于json内部数据内容的操作函数，有:

准备工作：

一、确认MYSQL版本支持json字段类型

在MYSQL中执行select version();

二、创建一个含JSON类型字段的表

CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `params_info` json DEFAULT NULL COMMENT 'json类型的字段',
  `status` char(1) DEFAULT '1' COMMENT '此条记录的状态: 1.有效  0.无效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

笔者接下来会从两个方面来简单示例一下：

从整体而言，来操作行数据(简述)。
操作JSON内部的数据。

从整体行数据示例(示例增、改；基本操作，可选择性跳过)：

增:

INSERT INTO `test_table` ( `params_info`, `status` )
VALUES
	( '{"name":"张三", "gender":"男", "hobby":"女","age":24, "motto":"我是一只小小小小鸟年"}', '1' );

可看见，表中已经有数据了:

改：

UPDATE `test_table`
SET `params_info`='{"name":"邓沙利文", "gender":"男", "hobby":"女","age":24, "motto":"帅气值爆表！"}' WHERE id=1

查看表数据，可见修改成功:

对json字段内部内容的操作(示例)：

总体思路说明：

$代表这个json字符串；

如果json是一个对象，那么$.key可获取到对应的值；可嵌套使用。

如果json是一个数组，那么$[i]可获取到对应的值；可嵌套使用。

JSON_EXTRACT(json_doc, path[, path] ...)获取json中的指定的值：

提示：从MYSQL5.7.9版本开始，提供了一种新的column->path的方式，也可以达到JSON_EXTRACT(column, path)的
效果。

根据key获取value：

示例所涉及数据：

SQL:

-- 如果key中含有特殊字符，那么需要双引号将key引起来,并使用转移符 \
SELECT
	JSON_EXTRACT ( `params_info`, '$."a\ bird"' ) AS bird,
	JSON_EXTRACT ( `params_info`, '$."a\'fish"' ) AS fish 
FROM
	`test_table` 
WHERE
	id = 2;

--- 等价于

SELECT
	 `params_info` -> '$."a\ bird"'  AS bird,
	 `params_info` -> '$."a\'fish"'  AS fish 
FROM
	`test_table` 
WHERE
	id = 2;

运行结果为：

获取部分(或所有)key对应的value值数组：

示例所涉及数据:

获取部分key对应的value值数组SQL:

SELECT
	JSON_EXTRACT ( `params_info`, '$.a', '$.b') AS result
FROM
	`test_table` 
WHERE
	id = 3;

运行结果为：

获取所有key对应的value值数组SQL：

提示：如果json是对象，那么形如$.*来匹配所有；如果是数组，那么形如$[*]来匹配所有。

-- 如果想要获取所有value,那么可以使用*
SELECT
	JSON_EXTRACT ( `params_info`, '$.*' ) AS result
FROM
	`test_table` 
WHERE
	id = 3;

-- 等价于
SELECT
	`params_info` -> '$.*' AS result
FROM
	`test_table` 
WHERE
	id = 3;

运行结果为：

获取数组中的某个位置的值：

示例所涉及数据:

SQL:

SELECT
	JSON_EXTRACT ( `params_info`, '$.c[2]' ) AS result
FROM
	`test_table` 
WHERE
	id = 3;

运行结果为：

复杂json的value获取：

示例所涉及数据:

SQL:

SELECT
	JSON_EXTRACT (`params_info`, '$[0]') AS one,
	JSON_EXTRACT (`params_info`, '$[1]') AS two,
	JSON_EXTRACT (`params_info`, '$[2]') AS three,
	JSON_EXTRACT (`params_info`, '$[3]') AS four,  -- 注: 没有的话，返回null
	JSON_EXTRACT (`params_info`, '$[1].a') AS five,
	JSON_EXTRACT (`params_info`, '$[1].a[1]') AS six,
	JSON_EXTRACT (`params_info`, '$[1].b') AS seven,
	JSON_EXTRACT (`params_info`, '$[2][1]') AS eight
FROM
	`test_table` 
WHERE
	id = 4;


--- 等价于

SELECT
	`params_info`-> '$[0]' AS one,
	`params_info`-> '$[1]' AS two,
	`params_info`-> '$[2]' AS three,
	`params_info`-> '$[3]' AS four,  -- 注: 没有的话，返回null
	`params_info`-> '$[1].a' AS five,
	`params_info`-> '$[1].a[1]' AS six,
	`params_info`-> '$[1].b' AS seven,
	`params_info`-> '$[2][1]' AS eight
FROM
	`test_table` 
WHERE
	id = 4;

运行结果为：

直接定位二级key：

示例所涉及数据：

SQL：

SELECT JSON_EXTRACT(`params_info`, '$**.b') as result from test_table where id = 5;

--- 等价于

SELECT `params_info` -> '$**.b' as result from test_table where id = 5;

运行结果为：

JSON_INSERT(json_doc, path, val[, path, val] ...)往json中插入value值：

示例所涉及数据：

SQL：

SELECT JSON_INSERT (`params_info`, '$[1].b[2]', true, '$[2][2]', 2, '$[2][2]', 1) FROM test_table WHERE id=6;

运行结果为：

注:插入值的位置必须是不存在的,如果该位置已经被占用了，那么对应的插入会无效。

注:只要指定的插入的位置，大于(无论是刚好大于1还是大于多少)已占用了的位置，都会插入到原来值的后面。

JSON_SET(json_doc, path, val[, path, val] ...)修改json的value值或插入value值:

示例所涉及数据：

SQL：

SELECT JSON_SET (`params_info`,'$[1].b[0]',1,'$[2][2]',2) FROM test_table WHERE id=6;

运行结果为：

注:相比起JSON_INSERT,JSON_SET可以修改json中已被占用了的位置的value值。

JSON_REPLACE(json_doc, path, val[, path, val] ...)修改json的某个value值：

示例所涉及数据：

SQL：

SELECT JSON_REPLACE (`params_info`, '$[1].b[0]', 1, '$[2][1]', 2) FROM test_table WHERE id=6;

运行结果为：

JSON_REMOVE(json_doc, path[, path] ...)移除json中的某个value值：

示例所涉及数据：

SQL：

SELECT JSON_REMOVE (`params_info`, '$[2]', '$[1].b[1]') FROM test_table WHERE id=6;

运行结果为：

JSON_CONTAINS(json_doc, val[, path])检查json字符串中的某个key的值是否包含某个值：

示例所涉及数据：

SQL：

SELECT JSON_CONTAINS(`params_info`,'{"d": 4}', '$.c') FROM test_table WHERE id=7;

运行结果为：

注：验证params_info所代表的json字符串中的’$.c’所代表的key的值是否包含{“d”:4}。如果包含，结果为1；如果不包
含结果为0。

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)查询json是否至少含有一个/全含有给出的path(即:key)：

示例所涉及数据：

判断至少包含一个示例SQL：

-- 只要含有的路径数  大于等于1，就会返回1,一个也不含有才会返回0
SELECT
	JSON_CONTAINS_PATH ( `params_info`, 'one', '$.a', '$.e' ) 
FROM
	test_table 
WHERE
	id =8;

运行结果为：

判断全部包含示例SQL：

-- 只要含有的路径数 等于 给出的路径数时，才会返回1；否者返回0
SELECT
	JSON_CONTAINS_PATH ( `params_info`, 'all', '$.a', '$.e' ) 
FROM
	test_table 
WHERE
	id =8;

运行结果为：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])搜索json里面满足条件的value值，并返回key的path：

示例所涉及数据：

返回值为abc的第一个key的path，SQL：

-- 返回值为abc的第一个key的path
SELECT
	JSON_SEARCH (`params_info`, 'one', 'abc')
FROM
	test_table 
WHERE
	id =9;

运行结果为：

返回值为abc的所有key的path，SQL为：

-- 返回值为abc的所有key的path
SELECT
	JSON_SEARCH (`params_info`, 'all', 'abc')
FROM
	test_table 
WHERE
	id =9;

运行结果为：

模糊匹配，SQL：

-- 返回 所有满足%c_模糊匹配的值的key的path
SELECT
	JSON_SEARCH (`params_info`, 'all', '%c_')
FROM
	test_table 
WHERE
	id =9;

运行结果为：

指定路径下的查找，SQL：

-- 返回 在路径$[3]下，所有满足%c%模糊匹配的值的key的path
SELECT
	JSON_SEARCH (`params_info`, 'all', '%c%', null, '$[3]')
FROM
	test_table 
WHERE
	id =9;

运行结果为：

排序：

如果想根据json中的数据值等来排序的话，那么可参考：

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

说明:
JSON函数较多，用法更多，也很灵活；上面列出的只是本人认为相对来说比较常用的函数(且这些函数的用法也没有列举全)。更多函数更多用法详见MYSQL官方文档，推荐阅读。

^_^ 如有不当之处，欢迎指正
^_^ 参考资料：
《mysql8.0官方文档.pdf》
^_^ 本文已经被收录进《程序员成长笔记(四)》，笔者JustryDeng

justry_deng

关注

5
点赞
踩
20

收藏

觉得还不错? 一键收藏
2
评论
MySQL之JSON数据类型操作示例

MYSQL5.7.8开始，就支持JSON数据类型的存储了。对于行数据整体而言，基本的增删改查是一样的；对于json内部数据内容的操作函数，有:准备工作：一、确认MYSQL版本支持json字段类型在MYSQL中执行select version();二、创建一个含JSON类型字段的表CREATE TABLE `test_table` ( `id` bigin...
复制链接

扫一扫