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