专栏首页DBA随笔MySQL中的json字段

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: 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)
文章分享自微信公众号:
DBA随笔

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • Mybatis操作mysql 8的Json字段类型

    Json字段是从mysql 5.7起加进来的全新的字段类型,现在我们看看在什么情况下使用该字段类型,以及用mybatis如何操作该字段类型

    算法之名
  • ​MySql之json_extract函数处理json字段

    在db中存储json格式的数据,相信大家都或多或少的使用过,那么在查询这个json结构中的数据时,有什么好的方法么?取出String之后再代码中进行解析?

    一灰灰blog
  • MySQL · 最佳实践 · 如何索引JSON字段

    原文地址: http://mysql.taobao.org/monthly/2017/12/09/

    二狗不要跑
  • MySQL 支持JSON字段的基本操作、相关函数及索引使用如何索引JSON字段

    Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(documen...

    chenchenchen
  • Springboot+Mybatis+MySql下,mysql使用json类型字段存取的处理

    1、mysql5.7开始支持json类型字段; 2、mybatis暂不支持json类型字段的处理,需要自己做处理

    stys35
  • mysql虚拟列(Generated Columns)及JSON字段类型的使用

    mysql 5.7中有很多新的特性,但平时可能很少用到,这里列举2个实用的功能:虚拟列及json字段类型

    菩提树下的杨过
  • MySQL中explain的结果​字段介绍

    昨天说完了执行计划的前四个字段,今天说说后面几个字段吧。我们看看explain的基本语法和输出内容:

    AsiaYe
  • Mysql8之获取JSON字段的值

            要从其它系统数据库中导出一些数据,发现其中有个字段的值是json字符串,而需求要的是该JSON字符串中某个key对应的value值。

    克虏伯
  • mysql 中取得汉字字段的各汉字首字母

    用户7166745
  • mysql 中取得汉字字段的各汉字首字母

    好派笔记
  • MySQL中explain中的结果字段介绍(三)

    之前的文章中对于explain的数据结果中的字段已经进行了一部分介绍了,今天来说一说剩下的几个字段,为了防止忘记,先看看这个表结构:

    AsiaYe
  • mysql替换某个字段中的某个字符

    Msql里面的某个表的某个字段里面存储的是一个人的地址,有一天这个地址的里面的某个地

    似水的流年
  • mysql替换某个字段中的某个字符

    比如:  Msql里面的某个表的某个字段里面存储的是一个人的地址,有一天这个地址的里面的某个地 名变了,那么他的地址也就要变: 比如: 原来是: numb...

    似水的流年
  • mysql替换某个字段中的某个字符

    比如:  Msql里面的某个表的某个字段里面存储的是一个人的地址,有一天这个地址的里面的某个地 名变了,那么他的地址也就要变: 比如:

    似水的流年
  • Mysql 字段常用字段类型

    特殊说明: 解决问题的光鲜,藏着磕Bug的痛苦。 万物皆入轮回,谁也躲不掉! 以上文章,均是我实际操作,写出来的笔记资料,不会出现全文盗用别人文章...

    收心
  • mysql字段名

    字段属性:null/not null,primary key,auto_increment,unique key,comment,default

    十月梦想
  • ​MySQL中explain的结果字段介绍(1)

    我们在使用MySQL的时候,用的最多的情况可能就是select语句了,当我们在一个表查找数据的时候,经常会遇到查找的速度比较慢的情况,作为一名DBA,我...

    AsiaYe
  • MySQL 中定义数据字段的类型

    这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PR...

    用户4988085
  • mysql更新字段中的部分内容

    在实践中,会出现某些字段中的内容变了,比如三方提供的图标地址变了,那么或许对字段中存储的图片域名进行更新,此时就需要用到mysql更新字段中的部分内容的功能。

    程序新视界

扫码关注云+社区

领取腾讯云代金券