MySQL之JSON数据类型操作示例

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;

 

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

  1. 从整体而言,来操作行数据(简述)。
  2. 操作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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页