当前位置:数据库 > > 正文

mysql中json的支持(MySQL中json字段的操作方法)

时间:2022-03-30 09:28:09类别:数据库

mysql中json的支持

MySQL中json字段的操作方法

   MySQL5.7.8中引入了json字段,这种类型的字段使用的频率比较低,但是在实际操作中,有些业务仍然在用,我们以此为例,介绍下json字段的操作方法:

还是从例子看起:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 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字段,插入了三条数据,如上:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 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

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 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值

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 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个数,如果想取到中间的某一层,则可以使用$的方法,如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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文件的深度,测试例子如下:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 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中是否有一个或者多个成员。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 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结合起来使用。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 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、*的作用,所有的值,看下面的例子。

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • {
  •  "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

    标签:
    上一篇下一篇

    猜您喜欢

    热门推荐