mysql中json的支持
MySQL中json字段的操作方法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) |
首先我们创建了一个表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的意思是只要包含一个成员，就返回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) |
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组合起来的类型，可以看到是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) |
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字段的资料请关注开心学习网其它相关文章！
原文链接：https://cloud.tencent.com/developer/article/1558311