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

chenchenchen

发布于 2022-03-09 11:46:12

27.7K0

发布于 2022-03-09 11:46:12

文章被收录于专栏：chenchenchen

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数组是否包含某个字符串

select t.js,t.js->"$.name" from table t 
where js_name(t.js->"$.name","\"b\"")

判断数组中是否存在某个数字

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "2") is not null; 结果返回1

SELECT JSON_SEARCH('["1","2","3","4","5"]', 'one', "6") is not null; 结果返回0

匹配List对象的值

-- 要查找的值
SET @valueMapping = 17;

-- 表字段：id, config
-- config字段格式：
/*
{
    "fieldModels": [{
        "key": 0,
        "guid": "1",
        "field": "Id",
        "dataType": 1,
        "showName": "标识",
        "textFormat": "",
        "valueMapping": 17
    }, {
        "key": 1,
        "guid": "2",
        "field": "orderid",
        "dataType": 0,
        "showName": "orderid",
        "textFormat": "",
        "valueMapping": -1
    }
}
*/
-- 需求：查找 config JSON字段（对象类型）中 fieldModels（数组类型）数组字段中 valueMapping（整形）值等于 17 的记录

-- 1、先提取 config JSON 字段中 fieldModels 属性，得到数组
SELECT JSON_EXTRACT(`config`,'$.fieldModels') fieldModels FROM `sql_model`;

-- 2、再从 fieldModels 数组中查找 valueMapping 的值是否等于查找的值，返回 1 或 0，表示是否已找到
SELECT JSON_CONTAINS(JSON_EXTRACT(`config`,'$.fieldModels'), JSON_OBJECT('valueMapping', @valueMapping)) 是否已找到 FROM `sql_model`;

-- 最后匹配结果
SELECT id, config FROM `sql_model` 
WHERE JSON_CONTAINS(JSON_EXTRACT(`config`,'$.fieldModels'), JSON_OBJECT('valueMapping', @valueMapping)) > 0;

4、查询下级值，key是数字类型

SET @j = '{"0": {"a":1, "b":2}, "other": {"c": 3, "d": 4}}';
SELECT JSON_EXTRACT(@j, '$.0.a') AS '$.0.a'

此时会报异常：Invalid JSON path expression. The error is around character position 3.

查看官方文档，有如下说明：

Names of keys must be double-quoted strings or valid ECMAScript identifiers (see http://www.ecma-international.org/ecma-262/5.1/#sec-7.6). Path expressions, like JSON text, should be encoded using the ascii, utf8, or utf8mb4 character set. Other character encodings are implicitly coerced to utf8mb4.

解决方法：数字键必须单独使用双引号包围，如下：

SELECT JSON_EXTRACT(@j, '$."0".a') AS `$.0.a`;

其他对JSON的操作可以参考：

mysql使用json注意事项 https://blog.csdn.net/u010757785/article/details/62422679

mysql json类型快速入门 https://blog.csdn.net/qq_16414307/article/details/50595255

mysql根据json字段内容检索数据 https://blog.csdn.net/qq_21187515/article/details/90760337

mysql查询json数组 https://www.cnblogs.com/jardeng/p/13725298.html

MySQL支持JSON字段的意义

1.可以直接过滤记录

避免了要将所有记录都读取出来，在客户端进行过滤。

select * from t where js->'$.num'=1

2.可以直接update，而无须先读取

单条原子更新：update t set js=json_set(js,'.num',js->'

跨表更新：update t,t1 set t.js=json_merage(t.js,t1.js) where t.id=t1.id

完成多条纪录更新：update t set js=json_set(js,'$.num',1) where id in(1,2)

3.通过json类型，完美的实现了表结构的动态变化 除了一般意义上的增加表字段，还包括嵌套其他对象与数组

例如增加一个子节点到sonAry时，无须添加子表

update t set js=json_array_append(js,'$.newNum',123) where id =1

参考mysql json字段的使用与意义 https://blog.csdn.net/qq_16414307/article/details/50600489

JSON相关函数

MySQL官方列出json相关的函数，完整列表如下:

分类	函数	描述
创建json	json_array	创建json数组
	json_object	创建json对象
	json_quote	将json转成json字符串类型
查询json	json_contains	判断是否包含某个json值
	json_contains_path	判断某个路径下是否包json值
	json_extract	提取json值
	column->path	json_extract的简洁写法，MySQL 5.7.9开始支持
	column->>path	json_unquote(column -> path)的简洁写法
	json_keys	提取json中的键值为json数组
	json_search	按给定字符串关键字搜索json，返回匹配的路径
修改json	json_append	废弃，MySQL 5.7.9开始改名为json_array_append
	json_array_append	末尾添加数组元素，如果原有值是数值或json对象，则转成数组后，再添加元素
	json_array_insert	插入数组元素
	json_insert	插入值（插入新值，但不替换已经存在的旧值）
	json_merge	合并json数组或对象
	json_remove	删除json数据
	json_replace	替换值（只替换已经存在的旧值）
	json_set	设置值（替换旧值，并插入不存在的新值）
	json_unquote	去除json字符串的引号，将值转成string类型
返回json属性	json_depth	返回json文档的最大深度
	json_length	返回json文档的长度
	json_type	返回json值得类型
	json_valid	判断是否为合法json文档

在Mysql5.7版本及之后的版本可以使用column->path作为JSON_EXTRACT(column, path)的快捷方式。这个函数可以作为列数据的别名出现在SQL语句中的任意位置，包括WHERE,ORDER BY,和GROUP BY语句。同样包含SELECT， UPDATE， DELETE，CREATE TABLE和其他SQL语句。->左边的参数为JSON数据的列名而不是一个表达式，其右边参数JSON数据中的某个路径表达式。

具体语法规则可以参考：

MySQL 5.7新增对JSON支持 https://blog.csdn.net/szxiaohe/article/details/82772881

如何索引JSON字段

MySQL并没有提供对JSON对象中的字段进行索引的功能，我们将利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

虚拟列语法如下

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式

加完虚拟列的建表语句如下，此时虚拟字段还没加索引

CREATE TABLE `t` (  
    `id` INT UNSIGNED NOT NULL,
    `js` JSON NOT NULL,
    js_num int(11) GENERATED ALWAYS AS (json_extract(js,'$.num')) STORED,
--   `js_num` int(11) GENERATED ALWAYS AS (`js` ->> '$.num'), 
    PRIMARY KEY (id)
);

查看表t的字段

SHOW COLUMNS FROM `t`;

+------------------+------------------+------+-----+---------+-------------------+
| Field            | Type             | Null | Key | Default | Extra             |
+------------------+------------------+------+-----+---------+-------------------+
| id               | int(10) unsigned | NO   | PRI | NULL    |                   |
| js               | json             | NO   |     | NULL    |                   |
| js_num           | int(11)          |      |     | NULL    | VIRTUAL GENERATED |
+------------------+------------------+------+-----+---------+-------------------+

我们看到虚拟字段js_num的类型是VIRTUAL GENERATED。MySQL只是在数据字典里保存该字段元数据，并没有真正的存储该字段的值。这样表的大小并没有增加。我们可以利用索引把这个字段上的值进行物理存储。

添加索引之前，查询的执行计划

EXPLAIN SELECT * FROM `t` WHERE `js_num` = 1  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: NULL  
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

添加索引

CREATE INDEX `index_js_num` ON `t`(`js_num`);

再执行上面的查询语句，我们将得到不一样的执行计划

EXPLAIN SELECT * FROM `t` WHERE `js_num` = 1  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: names_idx  
          key: names_idx
      key_len: 22
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

如我们所见，最新的执行计划走了新建的索引。

json_extract还可利用path的通配符，发掘更多类型索引。甚至还可利用JSON_CONTAINS/JSON_CONTAINS_PATH来建立索引。

参考：MySQL如何索引JSON字段 https://developer.aliyun.com/article/303208