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字段的资料请关注好吧啦网其它相关文章！