MySQL 支持JSON字段的基本操作、相关函数及索引使用

Mysql5.7版本以后新增的功能，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式，并提供了不少内置函数,通过计算列，甚至还可以直接索引json中的数据。

在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：=, <, <=, >, >=, <>, != 和 <=>。

 

 

JSON字段基本操作

示例数据

表的基本结构 

CREATE TABLE `t` (  
    `id` INT UNSIGNED NOT NULL,
    `js` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

 js字段保存的JSON对象结构

{
    "num": 1,  
    "name": "abc",  
    "age": 16,  
    "newNum": 123,  
    "class":{    
       "one": {
          "num": 1
        },                                                                                                                          
       "tow": {
          "num": 2
        },  
       "three": {
          "num": 3
        }
     }
 }

 

 

插入数据

直接提供字符串即可。还可以用JSON_Array和JSON_Object函数来构造

insert into t values(1,'{"num":1,"name":"abc"}')

注意事项：

1、JSON列存储的必须是JSON格式数据，否则会报错

2、JSON数据类型是没有默认值的

3、字段保持统一，存的时候就定好字段名和类型，做好注释并用文档记录

4、JSON是中文时不要进行转码，转码之后导致查询非常麻烦，入库时后面可以多带一个参数json_encode(array(),JSON_UNESCAPED_UNICODE)

5、能存一维数组绝对不要存二维数组，二维数组不可控。对一维数组的使用也要考虑清楚，JSON字段对必须整个数组更新，查询数组中的某个值也比较困难

 

修改数据 

JSON_SET(json_doc, path, val[, path, val] ...)

path中$就代表整个doc,然后可以用javascript的方式指定对象属性或者数组下标等.

值存在就修改，值不存在就设置，路径不存在将直接被忽略。

update t set js=json_set('{"num":1,"name":"abc"}','$.num',2,'$.age',16,'$.class.id',1) where id=1

结果js={"num":2,"name":"abc","age":16}

JSON_INSERT(json_doc, path, val[, path, val] ...)
如果不存在对应属性则插入,否则不做任何变动

JSON_REPLACE(json_doc, path, val[, path, val] ...)
如果存在则替换,否则不做任何变动

JSON_REMOVE(json_doc, path[, path] ...)
如果存在则删除对应属性,否则不做任何变动

 

 

查询数据

1、使用json_extract函数查询，获得doc中某个或多个节点的值。

JSON_EXTRACT(json_doc, path[, path] ...)，json_doc为字段，path"$.json"为属性路径)

 

2、使用 字段->'$.json属性'进行查询条件

mysql5.7.9开始增加了一种简写方式：column->path

select json_extract('{"num":1,"name":"abc"}','$.num')，结果1

等价于
select js->'$.num' from t where id=1，结果1

select id,js->'$.name' 
from t 
where js->'$.age'=16 
order by js->'$.num'

等价于
select id,json_extract(js,'$.name') 
from t 
where json_extract(js,'$.age')=16
order by json_extract(js,'$.num')

 

3、根据json数组查询，用JSON_CONTAINS(字段,JSON_OBJECT('json属性', "内容"))

select * from t where JSON_CONTAINS(js,JSON_OBJECT('num', 1))

 

数组查询

查找json数组是否包含某个字符串