MySQL中json字段的操作方法
MySQL5.7.8中引入了json字段，这种类型的字段使用的频率比较低，但是在实际操作中，有些业务仍然在用，我们以此为例，介绍下json字段的操作方法：
还是从例子看起：
mysql>createtabletest1(idint,infojson);
QueryOK,0rowsaffected(0.02sec)
mysql>insertintotest1values(1,'{"name":"yeyz","age":26}'),(2,'{"name":"zhangsan","age":30}'),(3,'{"name":"lisi","age":35}');
QueryOK,3rowsaffected(0.02sec)
Records:3Duplicates:0Warnings:0
mysql>select*fromtest1;
+------+---------------------------------+
|id|info|
+------+---------------------------------+
|1|{"age":26,"name":"yeyz"}|
|2|{"age":30,"name":"zhangsan"}|
|3|{"age":35,"name":"lisi"}|
+------+---------------------------------+
3rowsinset(0.00sec)
首先我们创建了一个表test1，其中id是int字段，info是json字段，插入了三条数据，如上：
mysql>select*fromtest1wherejson_extract(info,"$.age")>=30;
+------+---------------------------------+
|id|info|
+------+---------------------------------+
|2|{"age":30,"name":"zhangsan"}|
|3|{"age":35,"name":"lisi"}|
+------+---------------------------------+
2rowsinset(0.00sec)
我们可以通过json_extract的方法得到json中的内容。其中：
1、$符号代表的是json的根目录，
2、我们使用$.age相当于取出来了json中的age字段，
3、当然，在函数最前面，应该写上字段名字info
下面来看json中常用的函数：
a、json_valid判断是否是json字段，如果是，返回1，如果不是，返回0
mysql>selectjson_valid(2);
+---------------+
|json_valid(2)|
+---------------+
|0|
+---------------+
1rowinset(0.01sec)
mysql>selectjson_valid('{"num":2}');
+-------------------------+
|json_valid('{"num":2}')|
+-------------------------+
|1|
+-------------------------+
1rowinset(0.00sec)
mysql>selectjson_valid('2');
+-----------------+
|json_valid('2')|
+-----------------+
|1|
+-----------------+
1rowinset(0.00sec)
mysql>selectjson_valid('name');
+--------------------+
|json_valid('name')|
+--------------------+
|0|
+--------------------+
1rowinset(0.00sec)
这里需要注意的是，如果传入了字符串2，那么，返回结果是1
b、json_keys传回执行json字段最上一层的key值
mysql>selectjson_keys('{"name":"yeyz","score":100}');
+------------------------------------------+
|json_keys('{"name":"yeyz","score":100}')|
+------------------------------------------+
|["name","score"]|
+------------------------------------------+
1rowinset(0.01sec)
mysql>selectjson_keys('{"name":"yeyz","score":{"math":100,"English":95}}');
+----------------------------------------------------------------+
|json_keys('{"name":"yeyz","score":{"math":100,"English":95}}')|
+----------------------------------------------------------------+
|["name","score"]|
+----------------------------------------------------------------+
1rowinset(0.00sec)
#如果有多层，可以在最后面使用$的方法，拿到其中的某一层的目录
mysql>selectjson_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score');
+--------------------------------------------------------------------------+
|json_keys('{"name":"yeyz","score":{"math":100,"English":95}}','$.score')|
+--------------------------------------------------------------------------+
|["math","English"]|
+--------------------------------------------------------------------------+
1rowinset(0.00sec)
c、json_length函数，返回最上一层的key个数，如果想取到中间的某一层，则可以使用$的方法，如下：
mysql>selectjson_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+---------------------------------------------------------------------------+
|json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}')|
+---------------------------------------------------------------------------+
|3|
+---------------------------------------------------------------------------+
1rowinset(0.00sec)
mysql>selectjson_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score');
+-------------------------------------------------------------------------------------+
|json_length('{"name":"yeyz","score":{"math":100,"English":95},"age":26}','$.score')|
+-------------------------------------------------------------------------------------+
|2|
+-------------------------------------------------------------------------------------+
1rowinset(0.00sec)
d、json_depth函数，json文件的深度，测试例子如下：
mysql>selectjson_depth('{"aaa":1}'),json_depth('{}');
+-------------------------+------------------+
|json_depth('{"aaa":1}')|json_depth('{}')|
+-------------------------+------------------+
|2|1|
+-------------------------+------------------+
1rowinset(0.00sec)
mysql>selectjson_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}');
+--------------------------------------------------------------------------+
|json_depth('{"name":"yeyz","score":{"math":100,"English":95},"age":26}')|
+--------------------------------------------------------------------------+
|3|
+--------------------------------------------------------------------------+
1rowinset(0.00sec)
这里需要注意的是，形如{'aa':1}这种形式的json，其深度是2
e、json_contains_path函数检索json中是否有一个或者多个成员。
mysql>set@j='{"a":1,"b":2,"c":{"d":4}}';
QueryOK,0rowsaffected(0.00sec)
#one的意思是只要包含一个成员，就返回1
mysql>selectjson_contains_path(@j,'one','$.a','$.e');
+------------------------------------------+
|json_contains_path(@j,'one','$.a','$.e')|
+------------------------------------------+
|1|
+------------------------------------------+
1rowinset(0.00sec)
#all的意思是所有的成员都包含，才返回1
mysql>selectjson_contains_path(@j,'all','$.a','$.e');
+------------------------------------------+
|json_contains_path(@j,'all','$.a','$.e')|
+------------------------------------------+
|0|
+------------------------------------------+
1rowinset(0.01sec)
mysql>selectjson_contains_path(@j,'one','$.c.d');
+--------------------------------------+
|json_contains_path(@j,'one','$.c.d')|
+--------------------------------------+
|1|
+--------------------------------------+
1rowinset(0.00sec)
mysql>selectjson_contains_path(@j,'one','$.a.d');
+--------------------------------------+
|json_contains_path(@j,'one','$.a.d')|
+--------------------------------------+
|0|
+--------------------------------------+
1rowinset(0.00sec)
f、json_type函数，判断json中的成员的类型，需要和json_extract结合起来使用。
mysql>select*fromtest1;
+------+---------------------------------+
|id|info|
+------+---------------------------------+
|1|{"age":26,"name":"yeyz"}|
|2|{"age":30,"name":"zhangsan"}|
|3|{"age":35,"name":"lisi"}|
+------+---------------------------------+
3rowsinset(0.00sec)
#判断name的类型
mysql>selectjson_type(json_extract(info,"$.name"))fromtest1;
+----------------------------------------+
|json_type(json_extract(info,"$.name"))|
+----------------------------------------+
|STRING|
|STRING|
|STRING|
+----------------------------------------+
3rowsinset(0.00sec)
#判断age的类型
mysql>selectjson_type(json_extract(info,"$.age"))fromtest1;
+---------------------------------------+
|json_type(json_extract(info,"$.age"))|
+---------------------------------------+
|INTEGER|
|INTEGER|
|INTEGER|
+---------------------------------------+
3rowsinset(0.00sec)
#判断name和age组合起来的类型，可以看到是array
mysql>selectjson_type(json_extract(info,"$.name","$.age"))fromtest1;
+------------------------------------------------+
|json_type(json_extract(info,"$.name","$.age"))|
+------------------------------------------------+
|ARRAY|
|ARRAY|
|ARRAY|
+------------------------------------------------+
3rowsinset(0.00sec)
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"}}}';
QueryOK,0rowsaffected(0.00sec)
#所有成员
mysql>selectjson_extract(@j,'$.*');
+---------------------------------------+
|json_extract(@j,'$.*')|
+---------------------------------------+
|[1,2,{"d":4},{"d":{"ddd":"5"}}]|
+---------------------------------------+
1rowinset(0.00sec)
#所有成员中的d成员
mysql>selectjson_extract(@j,'$.*.d');
+--------------------------+
|json_extract(@j,'$.*.d')|
+--------------------------+
|[4,{"ddd":"5"}]|
+--------------------------+
1rowinset(0.00sec)
以上就是MySQL中json字段的操作方法的详细内容，更多关于MySQLjson字段的资料请关注毛票票其它相关文章！