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;
笔者接下来会从两个方面来简单示例一下:
- 从整体而言,来操作行数据(简述)。
- 操作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
更多推荐
所有评论(0)