程序员经验分享-hwhale

一文说透 MySQL JSON 数据类型（收藏）

2023-11-03

JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。

相对字符类型，原生的 JSON 类型具有以下优势：

  1. 在插入时能自动校验文档是否满足 JSON 格式的要求。
  2. 优化了存储格式。无需读取整个文档就能快速访问某个元素的值。

在 JSON 类型引入之前，如果我们想要获取 JSON 文档中的某个元素，必须首先读取整个 JSON 文档，然后在客户端将其转换为 JSON 对象，最后再通过对象获取指定元素的值。

下面是 Python 中的获取方式。

import json

# JSON 字符串:
x =  '{ "name":"John", "age":30, "city":"New York"}'

# 将 JSON 字符串转换为 JSON 对象:
y = json.loads(x)

# 读取 JSON 对象中指定元素的值:
print(y["age"])

这种方式有两个弊端：一、消耗磁盘 IO，二、消耗网络带宽，如果 JSON 文档比较大，在高并发场景，有可能会打爆网卡。

如果使用的是 JSON 类型，相同的需求，直接使用 SQL 命令就可搞定。不仅能节省网络带宽，结合后面提到的函数索引，还能降低磁盘 IO 消耗。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('{ "name":"John", "age":30, "city":"New York"}');
Query OK, 1 row affected (0.01 sec)

mysql> select c1->"$.age" from t;
+-------------+
| c1->"$.age" |
+-------------+
| 30          |
+-------------+
1 row in set (0.00 sec)

本文将从以下几个方面展开：

  1. 什么是 JSON。
  2. JSON 字段的增删改查操作。
  3. 如何对 JSON 字段创建索引。
  4. 如何将存储 JSON 字符串的字符字段升级为 JSON 字段。
  5. 使用 JSON 时的注意事项。
  6. Partial Updates。
  7. 其它 JSON 函数。

一、什么是 JSON

JSON 是 JavaScript Object Notation（JavaScript 对象表示法）的缩写，是一个轻量级的，基于文本的，跨语言的数据交换格式。易于阅读和编写。

JSON 的基本数据类型如下：

  • 数值：十进制数，不能有前导 0，可以为负数或小数，还可以为 e 或 E 表示的指数。

  • 字符串：字符串必须用双引号括起来。

  • 布尔值：true，false。

  • 数组：一个由零或多个值组成的有序序列。每个值可以为任意类型。数组使用方括号[] 括起来，元素之间用逗号,分隔。譬如，

    [1, "abc", null, true, "10:27:06.000000", {"id": 1}]

  • 对象：一个由零或者多个键值对组成的无序集合。其中键必须是字符串，值可以为任意类型。

    对象使用花括号{}括起来，键值对之间使用逗号,分隔，键与值之间用冒号:分隔。譬如，

    {"db": ["mysql", "oracle"], "id": 123, "info": {"age": 20}}

  • 空值：null。

二、JSON 字段的增删改查操作

下面我们看看 JSON 字段常见的增删改查操作：

2.1 插入操作

可直接插入 JSON 格式的字符串。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values('[1, "abc", null, true, "08:45:06.000000"]');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('{"id": 87, "name": "carrot"}');
Query OK, 1 row affected (0.01 sec)

也可使用函数，常用的有 JSON_ARRAY() 和 JSON_OBJECT()，前者用于构造 JSON 数组，后者用于构造 JSON 对象。如，

mysql> select json_array(1, "abc", null, true,curtime());
+--------------------------------------------+
| json_array(1, "abc", null, true,curtime()) |
+--------------------------------------------+
| [1, "abc", null, true, "10:12:25.000000"]  |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_object('id', 87, 'name', 'carrot');
+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+
1 row in set (0.00 sec)

对于 JSON 文档，KEY 名不能重复。

如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。

从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

下面通过一个具体的示例来看看两者的区别。

MySQL 5.7.36

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 10, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.02 sec)

MySQL 8.0.27

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 30, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.00 sec)

2.2 查询操作

JSON_EXTRACT(json_doc, path[, path] ...)

其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

下面我们结合一些具体的示例来看看 path 及 JSON_EXTRACT 的用法。

首先我们看看数组。

数组的路径是通过下标来表示的。第一个元素的下标是 0。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]');
+--------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]') |
+--------------------------------------------------------------+
| [10, 20, 30]                                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

除此之外，还可通过 [M to N] 获取数组的子集。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
+-------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0 to 1]') |
+-------------------------------------------------+
| [10, 20]                                        |
+-------------------------------------------------+
1 row in set (0.00 sec)

# 这里的 last 代表最后一个元素的下标
mysql> select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
+---------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[last-1 to last]') |
+---------------------------------------------------------+
| [20, [30, 40]]                                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

也可通过 [*] 获取数组中的所有元素。

mysql> select json_extract('[10, 20, [30, 40]]', '$[*]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[*]') |
+--------------------------------------------+
| [10, 20, [30, 40]]                         |
+--------------------------------------------+
1 row in set (0.00 sec)

接下来，我们看看对象。

对象的路径是通过 KEY 来表示的。

mysql> set @j='{"a": 1, "b": [2, 3], "a c": 4}';
Query OK, 0 rows affected (0.00 sec)

# 如果 KEY 在路径表达式中不合法（譬如存在空格），则在引用这个 KEY 时，需用双引号括起来。
mysql> select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
+-------------------------+-----------------------------+----------------------------+
| json_extract(@j, '$.a') | json_extract(@j, '$."a c"') | json_extract(@j, '$.b[1]') |
+-------------------------+-----------------------------+----------------------------+
| 1                       | 4                           | 3                          |
+-------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

除此之外，还可通过 .* 获取对象中的所有元素。

mysql> select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
+--------------------------------------------------------+
| json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*') |
+--------------------------------------------------------+
| [1, [2, 3], 4]                                         |
+--------------------------------------------------------+
1 row in set (0.00 sec)

# 这里的 $**.b 匹配 $.a.b 和 $.c.b
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

column->path

column->path，包括后面讲到的 column->>path，都是语法糖，在实际使用的时候都会转化为 JSON_EXTRACT。

column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。

create table t(c2 json);

insert into t values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');

mysql> select c2, c2->"$.ename" from t;
+----------------------------------+---------------+
| c2                               | c2->"$.ename" |
+----------------------------------+---------------+
| {"empno": 1001, "ename": "jack"} | "jack"        |
| {"empno": 1002, "ename": "mark"} | "mark"        |
+----------------------------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from t where c2->"$.empno" = 1001;
+------+----------------------------------+
| c1   | c2                               |
+------+----------------------------------+
|    1 | {"empno": 1001, "ename": "jack"} |
+------+----------------------------------+
1 row in set (0.00 sec)

column->>path

同 column->path 类似，只不过其返回的是字符串。以下三者是等价的。

  • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
  • JSON_UNQUOTE(column -> path)
  • column->>path
mysql> select c2->'$.ename',json_extract(c2, "$.ename"),json_unquote(c2->'$.ename'),c2->>'$.ename' from t;
+---------------+-----------------------------+-----------------------------+----------------+
| c2->'$.ename' | json_extract(c2, "$.ename") | json_unquote(c2->'$.ename') | c2->>'$.ename' |
+---------------+-----------------------------+-----------------------------+----------------+
| "jack"        | "jack"                      | jack                        | jack           |
| "mark"        | "mark"                      | mark                        | mark           |
+---------------+-----------------------------+-----------------------------+----------------+
2 rows in set (0.00 sec)

2.3 修改操作

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

插入新值。

仅当指定位置或指定 KEY 的值不存在时，才执行插入操作。另外，如果指定的 path 是数组下标，且 json_doc 不是数组，该函数首先会将 json_doc 转化为数组，然后再插入新值。

下面我们看几个示例。

mysql> select json_insert('1','$[0]',"10");
+------------------------------+
| json_insert('1','$[0]',"10") |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.00 sec)

mysql> select json_insert('1','$[1]',"10");
+------------------------------+
| json_insert('1','$[1]',"10") |
+------------------------------+
| [1, "10"]                    |
+------------------------------+
1 row in set (0.01 sec)

mysql> select json_insert('["1","2"]','$[2]',"10");
+--------------------------------------+
| json_insert('["1","2"]','$[2]',"10") |
+--------------------------------------+
| ["1", "2", "10"]                     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_insert(@j, '$.a', 10, '$.c
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库

json

一文说透 MySQL JSON 数据类型（收藏） 的相关文章

随机推荐

  • 文件系统FATFS使用 总结

    最近在使用FATFS 现将使用的方法记录下来 f open 函数 此函数用来打开或创建文件 重点 是这个函数的最后一个参数所代表的访问的模式 例子 state f open mfileinfo bmp name FA WRITE FA RE

  • Socket 关于设置Socket连接超时时间

    做网络编程的人对setSoTimeout方法一定很熟悉 都知道是设置连接的超时时间 但是我在网上找资料时发现很多人把这个超时时间理解成了链路的超时时间 我看了一下JDK 关于这个方法的说明 其实根本不是链路的超时时间 Java代码 setS

  • Android ImageView使用详解（系列教程三）

    目录 一 ImageView简介 二 ImageView基本使用 三 ImageView常用属性 四 几种图片的加载方法 五 ImageView的缩放类型 一 ImageView简介 ImageView是Android开发中最常用的组件之一

  • 华为OD机试 Python 报数问题

    描述 你和你的朋友们围成一个圈玩游戏 从第一个人开始 依次报数 1 2 3 当数到3的时候 那个人就得退出游戏 然后从他的下一个朋友继续开始 1 2 3 同样的 数到3的人又得退出 这样一直进行下去 直到圈里只剩下一个人 人会是谁 任务 给

  • 华为OD机试真题 Java 实现【Linux 发行版的数量】【2023Q1 100分】

    目录 一 题目描述 二 输入描述 三 输出描述 四 解题思路 五 Java算法源码 六 效果展示 1 输入 2 输出 3 说明 一 题目描述 Linux 操作系统有多个发行版 distrowatch com 提供了各个发行版的资料 这些发行

  • 集合转换为Jsoin存入redis

    重温复习redis 要将对象集合存入redis作为缓存 上网找了个json串转集合的工具类 这里记录一下 import java io IOException import java util ArrayList import java u

  • js获取input上传文件的文件名和扩展名的方法

  • WLAN配置

    SW1 sysname SW1 修改名称 undo info center enable 关闭提示 vlan batch 100 to 102 批量创建vlan 100 101 102 interface GigabitEthernet0

  • Ethereum geth 同步区块的三种模式

    Ethereum 以太坊 当前交易多 截止当前 2018 02 04 已经有5029238个区块 区块大小在150G左右 如果全部同步 并且严格逐个验证 需要太多的时间和计算 作者曾经用一台实体机 8核 16GB内存 2TB机械硬盘的del

  • leetcode1921.消灭怪物的最大数量（中等）

    解法 排序 贪心 具体 计算出每个怪物到达城市的时间 然后排序 class Solution public int eliminateMaximum vector

  • 深度学习论文笔记（可解释性）——CAM与Grad-CAM

    文章目录 主要工作 Global Average Pooling的工作机制 CAM Grad CAM 主要工作 CAM与Grad CAM用于解释CNN模型 这两个算法均可得出 c l a s s

  • 【JVM】Java垃圾回收机制（GC）详解

    Java垃圾回收机制 GC 详解 一 为什么需要垃圾回收 如果不进行垃圾回收 内存迟早都会被消耗空 因为我们在不断的分配内存空间而不进行回收 除非内存无限大 我们可以任性的分配不回收 但是事实并非如此 所以 垃圾回收是必须的 二 哪些内存需

  • 【kali】kali环境下安装dvwa

    STEP1 从github下载dvwa git clone https github com ethicalhack3r DVWA Q 我要自己安装git吗 A kali不用啦 一般都自带有 但是普通的ubuntu和debian上是没有的哦

  • Eclipse4.3 swt 插件在线安装

    到eclipse官网下载swt插件 1 点击该网站主菜单 Downloads gt Project 在出现的插件列表中找到 WindowBuilder 并点击 出现如下网页 复制该链接地址 当然该网页讲的就是如何安装swt designer

  • MATLAB地理数据处理 25：植被物候提取及分析模型优化（Savitzky-Golay）

    物候提取模型优化 1 前提 2 MATLAB代码 1 前提 之前我写过一篇使用Savitzky Golay处理遥感数据 获取地面物候信息的MATLAB代码 Python地理数据处理 十七 植被物候提取和分析 Savitzky Golay 但

  • MySQL数据表的约束

    数据表约束 对于某一列的值能添加哪些内容做了一定的限制 这种限制的手段就称为约束 一 约束的类型 NOT NULL 指示某列不能存储 NULL 值 UNIQUE 保证某列的每行必须有唯一的值 DEFAULT 规定没有给列赋值时的默认值 PR

  • Visual Studio 2022编译CMake工程

    用VS2022打开CMakeLists txt文件所在的文件夹 配置缓存 生成完毕 选择启动项 调试启动 运行输出 进入CMake项目视图 启动参数设置 增加args

  • Dll 编程入门指南

    我正在 学习 DLLs 谈不上对其有什么高屋建瓴的见解 本文只是 通过 编码让你看到并想知道代码是如何运行的 在本文中 我假定你知道如何使用你的编译器特性 比如设置目录路径等等 为了建立项目 请选择Win32 控制台项目 Win32 Con

  • 飞机游戏初步

    步骤 1 创建 hellogame项目 tools gt cocos2d console gt bin gt shift 右键 gt 在此处打开命令窗口 gt 路径pythoncocos py new hellogame p com gam

  • 一文说透 MySQL JSON 数据类型（收藏）

    JSON 数据类型是 MySQL 5 7 8 开始支持的 在此之前 只能通过字符类型 CHAR VARCHAR 或 TEXT 来保存 JSON 文档 相对字符类型 原生的 JSON 类型具有以下优势 在插入时能自动校验文档是否满足 JSON

热门标签