MySQL中json字段的操作方法
16lz
2021-12-15
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: 0mysql> 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)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)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)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)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)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)e、json_contains_path函数检索json中是否有一个或者多个成员。
mysql> set @j='{"a":1,"b":2,"c":{"d":4}}';Query OK, 0 rows affected (0.00 sec)#one的意思是只要包含一个成员，就返回1mysql> select json_contains_path(@j,'one','$.a','$.e');+------------------------------------------+| json_contains_path(@j,'one','$.a','$.e') |+------------------------------------------+| 1 |+------------------------------------------+1 row in set (0.00 sec)#all的意思是所有的成员都包含，才返回1mysql> 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)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组合起来的类型，可以看到是arraymysql> 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){ "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)
更多相关文章
- Android(安卓)初识Retrofit
- android图片上传springMvc
- android 之simpleAdapter详解
- Android(安卓)微博登录
- Android中读取电话本Contacts联系人的所有电话号信息
- Android模拟 HTTP multipart/form-data 请求协议信息实现图片上
- Android模拟 HTTP multipart/form-data 请求协议信息实现图片上
- Android中读取电话本Contacts联系人的所有电话号信息
- Android中读取电话本Contacts联系人的所有电话号信息