MySQL5.7.8中引入了json字段,这种类型的字段使用的频率比较低,但是在实际操作中,有些业务仍然在用,我们以此为例,介绍下json字段的操作方法:
还是从例子看起:
mysql> create table test1(id int,info json); Query OK, 0 rows affected (0.02 sec) mysql> insert into test1 values (1,'{"name":"yeyz","age":26}'),(2,'{"name":"zhangsan","age":30}'),(3,'{"name":"lisi","age":35}'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test1; +------+---------------------------------+ | id | info | +------+---------------------------------+ | 1 | {"age": 26, "name": "yeyz"} | | 2 | {"age": 30, "name": "zhangsan"} | | 3 | {"age": 35, "name": "lisi"} | +------+---------------------------------+ 3 rows in set (0.00 sec)
首先我们创建了一个表test1,其中id是int字段,info是json字段,插入了三条数据,如上:
mysql> select * from test1 where json_extract(info,"$.age")>=30; +------+---------------------------------+ | id | info | +------+---------------------------------+ | 2 | {"age": 30, "name": "zhangsan"} | | 3 | {"age": 35, "name": "lisi"} | +------+---------------------------------+ 2 rows in set (0.00 sec)
我们可以通过json_extract的方法得到json中的内容。其中:
1、$符号代表的是json的根目录,
2、我们使用$.age相当于取出来了json中的age字段,
3、当然,在函数最前面,应该写上字段名字info
下面来看json中常用的函数:
a、json_valid判断是否是json字段,如果是,返回1,如果不是,返回0
mysql> select json_valid(2); +---------------+ | json_valid(2) | +---------------+ | 0 | +---------------+ 1 row in set (0.01 sec) mysql> select json_valid('{"num":2}'); +-------------------------+ | json_valid('{"num":2}') | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.00 sec) mysql> select json_valid('2'); +-----------------+ | json_valid('2') | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> select json_valid('name'); +--------------------+ | json_valid('name') | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec)
这里需要注意的是,如果传入了字符串2,那么,返回结果是1
b、json_keys传回执行json字段最上一层的key值
mysql> select json_keys('{"name":"yeyz","score":100}'); +------------------------------------------+ | json_keys('{"name":"yeyz","score":100}') | +------------------------------------------+ | ["name", "score"] | +------------------------------------------+ 1 row in set (0.01 sec) mysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}'); +----------------------------------------------------------------+ | json_keys('{"name":"yeyz","score":{"math":100,"English":95}}') | +----------------------------------------------------------------+ | ["name", "score"] | +----------------------------------------------------------------+ 1 row in set (0.00 sec) #如果有多层,可以在最后面使用$的方法,拿到其中的某一层的目录 mysql> select json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score'); +--------------------------------------------------------------------------+ | json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score') | +--------------------------------------------------------------------------+ | ["math", "English"] | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)
c、json_length函数,返回最上一层的key个数,如果想取到中间的某一层,则可以使用$的方法,如下:
mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}'); +---------------------------------------------------------------------------+ | json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') | +---------------------------------------------------------------------------+ | 3 | +---------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score'); +-------------------------------------------------------------------------------------+ | json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score') | +-------------------------------------------------------------------------------------+ | 2 | +-------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
d、json_depth函数,json文件的深度,测试例子如下:
mysql> select json_depth('{"aaa":1}'),json_depth('{}'); +-------------------------+------------------+ | json_depth('{"aaa":1}') | json_depth('{}') | +-------------------------+------------------+ | 2 | 1 | +-------------------------+------------------+ 1 row in set (0.00 sec) mysql> select json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}'); +--------------------------------------------------------------------------+ | json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}') | +--------------------------------------------------------------------------+ | 3 | +--------------------------------------------------------------------------+ 1 row in set (0.00 sec)
这里需要注意的是,形如{'aa':1}这种形式的json,其深度是2
e、json_contains_path函数检索json中是否有一个或者多个成员。
mysql> set @j='{"a":1,"b":2,"c":{"d":4}}'; Query OK, 0 rows affected (0.00 sec) #one的意思是只要包含一个成员,就返回1 mysql> select json_contains_path(@j,'one','$.a','$.e'); +------------------------------------------+ | json_contains_path(@j,'one','$.a','$.e') | +------------------------------------------+ | 1 | +------------------------------------------+ 1 row in set (0.00 sec) #all的意思是所有的成员都包含,才返回1 mysql> select json_contains_path(@j,'all','$.a','$.e'); +------------------------------------------+ | json_contains_path(@j,'all','$.a','$.e') | +------------------------------------------+ | 0 | +------------------------------------------+ 1 row in set (0.01 sec) mysql> select json_contains_path(@j,'one','$.c.d'); +--------------------------------------+ | json_contains_path(@j,'one','$.c.d') | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select json_contains_path(@j,'one','$.a.d'); +--------------------------------------+ | json_contains_path(@j,'one','$.a.d') | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.00 sec)
f、json_type函数,判断json中的成员的类型,需要和json_extract结合起来使用。
mysql> select * from test1; +------+---------------------------------+ | id | info | +------+---------------------------------+ | 1 | {"age": 26, "name": "yeyz"} | | 2 | {"age": 30, "name": "zhangsan"} | | 3 | {"age": 35, "name": "lisi"} | +------+---------------------------------+ 3 rows in set (0.00 sec) #判断name的类型 mysql> select json_type(json_extract(info,"$.name")) from test1; +----------------------------------------+ | json_type(json_extract(info,"$.name")) | +----------------------------------------+ | STRING | | STRING | | STRING | +----------------------------------------+ 3 rows in set (0.00 sec) #判断age的类型 mysql> select json_type(json_extract(info,"$.age")) from test1; +---------------------------------------+ | json_type(json_extract(info,"$.age")) | +---------------------------------------+ | INTEGER | | INTEGER | | INTEGER | +---------------------------------------+ 3 rows in set (0.00 sec) #判断name和age组合起来的类型,可以看到是array mysql> select json_type(json_extract(info,"$.name","$.age")) from test1; +------------------------------------------------+ | json_type(json_extract(info,"$.name","$.age")) | +------------------------------------------------+ | ARRAY | | ARRAY | | ARRAY | +------------------------------------------------+ 3 rows in set (0.00 sec)
g、*的作用,所有的值,看下面的例子。
{ "a":1, "b":2, "c": { "d":4 } "e": { "d": { "ddd": "5" } } } mysql> set @j='{"a":1,"b":2,"c":{"d":4},"e":{"d":{"ddd":"5"}}}'; Query OK, 0 rows affected (0.00 sec) #所有成员 mysql> select json_extract(@j,'$.*'); +---------------------------------------+ | json_extract(@j,'$.*') | +---------------------------------------+ | [1, 2, {"d": 4}, {"d": {"ddd": "5"}}] | +---------------------------------------+ 1 row in set (0.00 sec) #所有成员中的d成员 mysql> select json_extract(@j,'$.*.d'); +--------------------------+ | json_extract(@j,'$.*.d') | +--------------------------+ | [4, {"ddd": "5"}] | +--------------------------+ 1 row in set (0.00 sec)
以上就是MySQL中json字段的操作方法的详细内容,更多关于MySQL json字段的资料请关注其它相关文章!
华山资源网 Design By www.eoogi.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
华山资源网 Design By www.eoogi.com
暂无评论...
更新日志
2024年12月23日
2024年12月23日
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]