麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
811
2023-07-02
MySQL中json_extract函数怎么使用
1. json_extract 使用场景说明
在日常业务开发中通常mysql数据库中某个字段会需要存储json格式字符串,查询的时候有时json数据较大,每次全部取出再去解析查询效率较低,也较麻烦.
好在Mysql5.7及之后的版本里提供了json_extract函数,可以通过key查询value值(如果是json数组类型,可以通过下标获取对应位置的值),非常方便。
2. MySQL json_extract 函数简介
2.1 函数简介
Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式。 在Json列插入或者更新的时候将会自动验证Json文本,未通过验证的文本将产生一个错误信息。 Json文本采用标准的创建方式,可以使用大多数的比较操作符进行比较操作,例如:=, <, <=, >, >=, <>, != 和 <=>。
2.2 使用方式
数据存储的数据是json字符串(类型是vachar)。
想要查询出来json中某个字段的值,用到方法是:JSON_EXTRACT()。
语法:
**JSON_EXTRACT(json_doc, path[, path] …)**
用法提示:
如果json字符串不是数组,则直接使用 $.字段名如果json字符串是数组[Array],则直接使用 $[对应的索引ID]
2.3 注意事项
JSON_EXTRACT性能验证 , 通过查看执行计划,验证全部都是全表扫描。使用场景:数据量不大json字符串较大则可以采用,数据量较大不建议使用。
3. 数据验证
3.1 提取普通json中的值
说明:
普通字段使用 $.KEY 获取数组字段使用 $.KEY[index] 获取,注意index从0开始实际使用中,如下json字符串只需要换成对应的表字段即可,但要注意需对json判空和替换等ETL转换操作.
select json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.name") as name, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.tel_no") as tel_no, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[0]") as hobby_1, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[1]") as hobby_2, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[2]") as hobby_3, json_extract('{"name":"zhangsan","tel_no":"136-6666-6666","hobbies":["basketball","run","sing"]}',"$.hobbies[3]") as hobby_4;登录后复制
结果:
name | tel_no | hobby_1 | hobby_2 | hobby_3 | hobby_4 |
---|---|---|---|---|---|
“zhangsan” | 136-6666-6666 | “basketball” | “run” | “sing” | NULL |
3.2 提取json数组的值
site_user表
id | name | tags |
---|---|---|
1 | zhangsan | [“COMMON”] |
2 | lisi | [“VIP”] |
3 | wangwu | [“VVIP”,“PLATINUM”] |
4 | zhaoliu |
提取用户的第一个标签:
select id, name, tags, json_extract(if(LENGTH(tags)>0,tags, '[]'),"$[0]") # 如果tags无数据,赋值为空数组from site_user;登录后复制
结果:
id | name | tags |
---|---|---|
1 | zhangsan | “COMMON” |
2 | lisi | “VIP” |
3 | wangwu | “VVIP” |
4 | zhaoliu | NULL |
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。