Loading [MathJax]/jax/output/CommonHTML/fonts/TeX/AMS-Regular.js
部署DeepSeek模型,进群交流最in玩法!
立即加群
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >MySQL 支持JSON字段的基本操作、相关函数及索引使用如何索引JSON字段

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

作者头像
chenchenchen
发布于 2022-03-09 03:46:12
发布于 2022-03-09 03:46:12
30.7K02
代码可运行
举报
文章被收录于专栏:chenchenchenchenchenchen
运行总次数:2
代码可运行

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

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

JSON字段基本操作

示例数据

表的基本结构

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE `t` (  
    `id` INT UNSIGNED NOT NULL,
    `js` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

js字段保存的JSON对象结构

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
{
    "num": 1,  
    "name": "abc",  
    "age": 16,  
    "newNum": 123,  
    "class":{    
       "one": {
          "num": 1
        },                                                                                                                          
       "tow": {
          "num": 2
        },  
       "three": {
          "num": 3
        }
     }
 }

插入数据

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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的方式指定对象属性或者数组下标等.

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select json_extract('{"num":1,"name":"abc"}','$.num'),结果1

等价于
select js->'$.num' from t where id=1,结果1
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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属性', "内容"))

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select * from t where JSON_CONTAINS(js,JSON_OBJECT('num', 1))

数组查询

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select t.js,t.js->"$.name" from table t 
where js_name(t.js->"$.name","\"b\"")

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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对象的值

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 要查找的值
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 的值是否等于查找的值,返回 10,表示是否已找到
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是数字类型

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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.

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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.可以直接过滤记录

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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时,无须添加子表

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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对象中的字段进行索引。

虚拟列语法如下

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
<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默认的方式

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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的字段

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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只是在数据字典里保存该字段元数据,并没有真正的存储该字段的值。这样表的大小并没有增加。我们可以利用索引把这个字段上的值进行物理存储。

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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

添加索引

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE INDEX `index_js_num` ON `t`(`js_num`);  

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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

MyBatis Plus查询json字段

https://blog.csdn.net/yelangkingwuzuhu/article/details/109943552

https://www.cnblogs.com/liangweiping/p/12835377.html

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

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

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
MySQL的JSON数据类型介绍以及JSON的解析查询
MySQL从5.7后引入了json数据类型以及json函数,可以有效的访问json格式的数据。json数据类型相对于字符串,具有以下优点:
Java技术债务
2022/08/09
11.9K0
MySQL的JSON数据类型介绍以及JSON的解析查询
oracle mysql5.7 Json函数
oracle mysql 5.7.8 之后增加了对json数据格式的函数处理,可更加灵活的在数据库中操作json数据,如可变属性、自定义表单等等都使用使用该方式解决。
兜兜毛毛
2019/10/23
2.5K0
oracle mysql5.7  Json函数
json查询解析在mysql5.7+有多方便?
在mysql5.7之前我们查询解析json需要先查出json字符串,再通过程序进行解析。(如果你使用的navicat版本太低的话,看不到json类型的) 现在mysql5.7引入了json类型格式,大大方便了我们的查询解析。 MySQL官方列出json相关的函数,完整列表如下: 分类 函数 描述 创建json json_array 创建json数组 json_object 创建json对象 json_quote 将json转成json字符串类型 查询json json_contains 判断是否包含某个
友儿
2022/09/11
2.5K0
一文说透MySQL JSON数据类型
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前,只能通过字符类型(CHAR,VARCHAR 或 TEXT )来保存 JSON 文档。
星哥玩云
2022/06/06
5.2K0
一文说透MySQL JSON数据类型
MySQL8.0 JSON函数之搜索JSON值(五)
之前的几篇文章介绍了JSON数据类型,相信大家已经对JSON有了一定的了解,上面一篇文章介绍了《MySQL8.0 JSON函数之创建与返回JSON属性(四)》JSON函数的使用;本节中的函数对JSON值执行搜索或比较操作,以从中提取数据;
SEian.G
2021/07/07
8K0
mysql5.7强势支持原生json格式!!全面掌握
mysql一直是如此优秀,但是随着最近一些nosql的强劲发展,甚为关系型数据库的mysql,也不例外在某些层面稍有逊色。其中,是否支持json格式是最常被用来比较的。
后端技术探索
2018/08/09
8470
MySQL5.7对json的操作
Mysql中需要存储用户的相关证件信息,即存储图片信息, 数量不定。这时候通常采用的做法:
有一只柴犬
2024/01/25
2660
MySQL5.7对json的操作
mysql支持原生json使用说明
MySQL在5.7.8开始对json原生支持,本文将对MySQL中json类型的用法简单说明,希望对你有用。
我的小碗汤
2018/08/22
3.6K0
mysql支持原生json使用说明
MySQL 5.7 JSON 数据类型使用总结
从MySQL5.7.8开始,MySQL支持原生的JSON数据类型。MySQL 支持RFC 7159定义的全部json 数据类型,具体的包含四种基本类型(strings, numbers, booleans, null)和两种结构化类型(objects and arrays)。
Tinywan
2024/02/22
7130
MySQL 5.7 JSON 数据类型使用总结
Mysql json 数据查询
MySQL 5.7 之后提供了Json类型,是MySQL 结合结构化存储和非结构化存储设计出来的一个类型。
chuchur
2022/10/25
4.9K0
mysql中保存并操作json对象
"At the end of the day, we can endure much more than we think we can.—— Frida Kahlo"
小闫同学啊
2020/03/25
14.5K0
MySQL 5.7新特性| Json Column 和 Generated Column(上)
MySQL5.7 新增两种字段类型:Json 和 Generated,Generated 型的产生和 Json 的关系密不可分,如果没有Generated 类型,Json 类型在强大,生产中可能也无法使用,因为 Json 不支持索引,但是如果要查询 Json 里的数据,没有索引就是全表扫描,在执行效率上肯定是不能用于生产环境的,但是有了 Generated 类型就不同了,Generated 类型简单地说是一个虚拟字段,值是不可更新的,值来源其他字段或者字段间计算或是转化而来的,这种类型是可以创建索引,利用 Generated 的特性,就可以间接的给 Json 类型中的 key 创建索引,解决 Json 不能创建索引的问题。简而言之, Generated 类型的产生,为 Json 类型在索引方面的问题提供了支持。JSON 的值包含单个值、数组、元组、标注的 Json 格式等几种格式。
阿炳数记
2019/02/27
2.1K0
MySQL之Json类型
MySQL 5.7 之后提供了Json类型,是MySQL 结合结构化存储和非结构化存储设计出来的一个类型。
伍六七AI编程
2022/03/23
2.4K0
MySQL之Json类型
[408]MySQL 5.7 新特性 JSON 的创建,插入,查询,更新
类似 varchar,设置 JSON 主要将字段的 type 是 json, 不能设置长度,可以是 NULL 但不能有默认值。
周小董
2022/04/12
11.1K0
用 MySQL 实现 JSON 格式的数据存储
在绝大多数业务场景中,分布式数据库并非必需。事实上,约80%的应用程序都可以在单机环境下高效运行。
贺春旸的技术博客
2024/08/05
3770
用 MySQL 实现 JSON 格式的数据存储
MySQL 5.7的原生JSON数据类型使用
要特别注意的是,JSON 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的
全栈程序员站长
2022/07/19
1.2K0
体验 Mysql 操作 JSON 文档
新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下 创建带有 JSON 字段的表 比如一个‘文章’表,字段包括 id、标题 title、标签 tags 一篇文章会有多个标签,tags 就可以设为 JSON 类型 建表语句如下: CREATE TABLE `article` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title
dys
2018/04/03
2.3K0
体验 Mysql 操作 JSON 文档
MySQL 5.7 JSON 实现简介
腾讯云数据库团队
2016/11/09
15.7K4
MySQL 5.7 JSON 实现简介
MySQL中的JSON
JSON类型的加入模糊了关系型数据库与NoSQL之间的界限,给日常开发也带来了很大的便利。
valineliu
2022/08/28
10.6K1
MySQL中的JSON
很重要!MySQL的复杂数据类型,学会使用再也不怕复杂场景了、枚举(ENUM)、集合(SET)、JSON、空间数据类型
MySQL数据库作为关系型数据库管理系统(RDBMS)中的佼佼者,提供了多种数据类型以满足不同应用场景的需求。本文将深入探讨MySQL中的枚举类型(ENUM)、集合类型(SET)、JSON类型以及空间数据类型
小白的大数据之旅
2024/11/25
3420
相关推荐
MySQL的JSON数据类型介绍以及JSON的解析查询
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验