百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

MySQL 5.7 JSON 数据类型使用总结

wptr33 2025-02-18 13:39 25 浏览

从MySQL5.7.8开始，MySQL支持原生的JSON数据类型。MySQL 支持RFC 7159定义的全部json 数据类型，具体的包含四种基本类型（strings, numbers, booleans, null）和两种结构化类型（objects and arrays）。

将 JSON 格式的字符串存储在字符串列中相比，该数据类型具有以下优势：

  • 自动验证存储在 JSON列中的 JSON 文档。无效的文档会产生错误。
  • 优化的存储格式。存储在列中的 JSON 文档被转换为允许快速读取文档元素的内部格式。
  • 当读取 JSON 值时，不需要从文本表示中解析该值，使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

JSON类型的存储结构

MySQL为了提供对json对象的支持，提供了一套将json字符串转为结构化二进制对象的存储方式。json会被转为二进制的doc对象存储于磁盘中（在处理JSON时MySQL使用的utf8mb4字符集，utf8mb4是utf8和ascii的超集）。

doc对象包含两个部分，type和value部分。其中type占1字节，可以表示16种类型：大的和小的json object类型、大的和小的 json array类型、literal类型（true、false、null三个值）、number类型（int6、uint16、int32、uint32、int64、uint64、double类型、utf8mb4 string类型和custom data（mysql自定义类型）

JSON数据类型意义

其实，没有JSON数据类型的支持，我们一样可以通过varchar类型或者text等类型来保存这一格式的数据，但是，为什么还要专门增加这一数据格式的支持呢？其中肯定有较varchar或者text来存储此类型更优越的地方。

  • 保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。
  • MySQL同时提供了一组操作JSON类型数据的内置函数。
  • 更优化的存储格式，存储在JSON列中的JSON数据会被转成内部特定的存储格式，允许快速读取。
  • 可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）

JSON 数据类型

JSON 对象

  • 使用对象操作的方法进行查询：字段->'$.json属性'
  • 使用函数进行查询：json_extract(字段, '$.json属性')
  • 获取JSON数组/对象长度：JSON_LENGTH()

JSON 数组

  • 使用对象操作的方法进行查询：字段->'$[0].属性'
  • 使用函数进行查询：JSON_CONTAINS(字段,JSON_OBJECT('json属性', '内容'))
  • 获取JSON数组/对象长度：JSON_LENGTH()

创建 JSON

类似varchar，设置主要将字段的type是json，不能设置长度，可以是NULL但不能有默认值。

CREATE TABLE `tinywan_json` (
 id INT ( 11 ) NOT NULL auto_increment,
 tag json NOT NULL COMMENT '标签列表',
 catagory json NOT NULL COMMENT '分类列表',
 create_time INT ( 11 ) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY ( id ) 
) ENGINE = INNODB DEFAULT charset = utf8mb4;

使用 describe tinywan_json 查看创建的表结构：

mysql> describe tinywan_json;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| tag         | json    | NO   |     | NULL    |                |
| catagory    | json    | NO   |     | NULL    |                |
| create_time | int(11) | YES  |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

写入JSON数据

INSERT INTO tinywan_json ( catagory, tag )
VALUES
 ( '{"0":"厨卫","1":"童装","2":"休闲"}', '["Good","Fine","Bad"]' );

MYSQL也有专门的函数 JSON_OBJECT, JSON_ARRAY函数生成json格式的数据。

INSERT INTO tinywan_json ( catagory, tag )
VALUES
 ( JSON_OBJECT ( "name", "John", "age", 23 ), JSON_ARRAY ( 'Low', 'Middle', 'High' ) )

查看插入的两条数据

mysql> select * from tinywan_json;
+----+---------------------------+-----------------------------------------+-------------+
| id | tag                       | catagory                                | create_time |
+----+---------------------------+-----------------------------------------+-------------+
|  1 | ["Good", "Fine", "Bad"]   | {"0": "厨卫", "1": "童装", "2": "休闲"} | NULL        |
|  2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"}             | NULL        |
+----+---------------------------+-----------------------------------------+-------------+
2 rows in set (0.10 sec)

查询 JSON

查询json中的数据使用 column->path 的形式，其中对象类型path这样表示 $.path，而数组类型则是 $[index]。

查看每一行数据的JSON类型，筛选如下：

mysql> select tag,json_type(tag),catagory,json_type(catagory) from tinywan_json;
+---------------------------+----------------+-----------------------------------------+---------------------+
| tag                       | json_type(tag) | catagory                                | json_type(catagory) |
+---------------------------+----------------+-----------------------------------------+---------------------+
| ["Good", "Fine", "Bad"]   | ARRAY          | {"0": "厨卫", "1": "童装", "2": "休闲"} | OBJECT              |
| ["Low", "Middle", "High"] | ARRAY          | {"age": 23, "name": "John"}             | OBJECT              |
+---------------------------+----------------+-----------------------------------------+---------------------+
2 rows in set (0.07 sec)

筛选出catagory中 name = John的那条记录的所有tag:

mysql> select tag from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+
| tag                       |
+---------------------------+
| ["Low", "Middle", "High"] |
+---------------------------+
1 row in set (0.06 sec)

通过json_type确定了tag的数据类型是ARRAY，那么就可以使用数组索引的方式查询：

mysql> select tag->'$[0]',tag->'$[2024]',tag->'$.notexist' from tinywan_json where catagory->'$.name' = 'John';
+-------------+----------------+-------------------+
| tag->'$[0]' | tag->'$[2024]' | tag->'$.notexist' |
+-------------+----------------+-------------------+
| "Low"       | NULL           | NULL              |
+-------------+----------------+-------------------+
1 row in set (0.05 sec)

上面的例子中，可以看到，使用 tag->'$[0]' 的方式如期获取了tag数组内的第一个数据。另外两个，一个tag->'$[2024]'是不存在的数组元素，一个tag->'$.notexist'是不存在的对象键，都返回了 null，这与预期的一致。

还要一个小小的问题，返回的 tag->'$[0]' = "Low"，并且左右有一对双引号，可否去掉呢？使用 JSON_UNQUOTE 函数

mysql> select JSON_UNQUOTE(tag->'$[0]'),tag->'$[2024]',tag->'$.notexist' from tinywan_json where catagory->'$.name' = 'John';
+---------------------------+----------------+-------------------+
| JSON_UNQUOTE(tag->'$[0]') | tag->'$[2024]' | tag->'$.notexist' |
+---------------------------+----------------+-------------------+
| Low                       | NULL           | NULL              |
+---------------------------+----------------+-------------------+
1 row in set (0.06 sec)

JSON 条件查询

因为JSON不同于字符串，所以如果用字符串和JSON字段进行比较，是不会相等的。这时可以使用CAST函数，将字符串转成JSON的形式。

mysql> select * from tinywan_json where catagory = '{"age": 23, "name": "John"}';
Empty set

mysql> select * from tinywan_json where catagory = CAST('{"age": 23, "name": "John"}' as JSON);
+----+---------------------------+-----------------------------+-------------+
| id | tag                       | catagory                    | create_time |
+----+---------------------------+-----------------------------+-------------+
|  2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL        |
+----+---------------------------+-----------------------------+-------------+
1 row in set (0.07 sec)

对应地，select字段和where筛选条件中，均可以使用 column->path 的方式操作。

mysql> select tag,catagory->'$.age' from tinywan_json where tag->'$[1]' = 'Middle';
+---------------------------+-------------------+
| tag                       | catagory->'$.age' |
+---------------------------+-------------------+
| ["Low", "Middle", "High"] | 23                |
+---------------------------+-------------------+
1 row in set (0.07 sec)

特别注意的是。JSON中的元素是严格区分变量类型的，比如说整型和字符串是严格区分的。

mysql> select * from tinywan_json where catagory->'$.age' = 23;
+----+---------------------------+-----------------------------+-------------+
| id | tag                       | catagory                    | create_time |
+----+---------------------------+-----------------------------+-------------+
|  2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL        |
+----+---------------------------+-----------------------------+-------------+
1 row in set (0.06 sec)

mysql> select * from tinywan_json where catagory->'$.age' = '23';
Empty set

除了用 column->path 的形式搜索，还可以用JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现错误nvalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

mysql> select * from tinywan_json where JSON_CONTAINS(catagory,'23','$.age');
+----+---------------------------+-----------------------------+-------------+
| id | tag                       | catagory                    | create_time |
+----+---------------------------+-----------------------------+-------------+
|  2 | ["Low", "Middle", "High"] | {"age": 23, "name": "John"} | NULL        |
+----+---------------------------+-----------------------------+-------------+
1 row in set (0.08 sec)

mysql> select * from tinywan_json where JSON_CONTAINS(catagory,23,'$.age');
Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

上面打印的第一行，才是正确的写法。整数应该写成 'int'，字符串则要看值内的情况，很多情况下需要带上双引号，'"string"'，这样写。如下：

mysql> select * from tinywan_json where JSON_CONTAINS(tag,'"Fine"');
+----+-------------------------+-----------------------------------------+-------------+
| id | tag                     | catagory                                | create_time |
+----+-------------------------+-----------------------------------------+-------------+
|  1 | ["Good", "Fine", "Bad"] | {"0": "厨卫", "1": "童装", "2": "休闲"} | NULL        |
+----+-------------------------+-----------------------------------------+-------------+
1 row in set (0.07 sec)

小结

  • JSON类型无须预定义字段，适合拓展信息的存储
  • 单个JSON文档的大小不能超过4G；单个KEY的大小不能超过两个字节，即64K
  • JSON类型适合应用于不常更新的静态数据
  • 对搜索较频繁的数据建议增加虚拟列并建立索引

相关推荐

oracle数据导入导出_oracle数据导入导出工具

关于oracle的数据导入导出，这个功能的使用场景，一般是换服务环境，把原先的oracle数据导入到另外一台oracle数据库，或者导出备份使用。只不过oracle的导入导出命令不好记忆，稍稍有点复杂...

继续学习Python中的while true/break语句

上次讲到if语句的用法，大家在微信公众号问了小编很多问题，那么小编在这几种解决一下，1.else和elif是子模块，不能单独使用2.一个if语句中可以包括很多个elif语句，但结尾只能有一个else解...

python continue和break的区别_python中break语句和continue语句的区别

python中循环语句经常会使用continue和break，那么这2者的区别是？continue是跳出本次循环，进行下一次循环；break是跳出整个循环；例如：...

简单学Python——关键字6——break和continue

Python退出循环，有break语句和continue语句两种实现方式。break语句和continue语句的区别：break语句作用是终止循环。continue语句作用是跳出本轮循环，继续下一次循...

2-1，0基础学Python之 break退出循环、 continue继续循环 多重循

用for循环或者while循环时，如果要在循环体内直接退出循环，可以使用break语句。比如计算1至100的整数和，我们用while来实现：sum=0x=1whileTrue...

Python 中 break 和 continue 傻傻分不清

大家好啊，我是大田。今天分享一下break和continue在代码中的执行效果是什么，进一步区分出二者的区别。一、continue例1：当小明3岁时不打印年龄，其余年龄正常循环打印。可以看...

python中的流程控制语句:continue、break 和 return使用方法

Python中，continue、break和return是控制流程的关键语句，用于在循环或函数中提前退出或跳过某些操作。它们的用途和区别如下：1.continue（跳过当前循环的剩余部分，进...

L017:continue和break - 教程文案

continue和break在Python中，continue和break是用于控制循环（如for和while）执行流程的关键字，它们的作用如下：1.continue：跳过当前迭代，...

作为前端开发者，你都经历过怎样的面试?

已经裸辞1个月了，最近开始投简历找工作，遇到各种各样的面试，今天分享一下。其实在职的时候也做过面试官，面试官时，感觉自己问的问题很难区分候选人的能力，最好的办法就是看看候选人的github上的代码仓库...

面试被问 const 是否不可变?这样回答才显功底

作为前端开发者，我在学习ES6特性时，总被const的"善变"搞得一头雾水——为什么用const声明的数组还能push元素？为什么基本类型赋值就会报错？直到翻遍MDN文档、对着内存图反...

2023金九银十必看前端面试题!2w字精品!

导文2023金九银十必看前端面试题！金九银十黄金期来了想要跳槽的小伙伴快来看啊CSS1.请解释CSS的盒模型是什么，并描述其组成部分。答案：CSS的盒模型是用于布局和定位元素的概念。它由内容区域...

前端面试总结_前端面试题整理

记得当时大二的时候，看到实验室的学长学姐忙于各种春招，有些收获了大厂offer，有些还在苦苦面试，其实那时候的心里还蛮忐忑的，不知道自己大三的时候会是什么样的一个水平，所以从19年的寒假放完，大二下学...

由浅入深，66条JavaScript面试知识点(七)

作者：JakeZhang转发链接：https://juejin.im/post/5ef8377f6fb9a07e693a6061目录由浅入深，66条JavaScript面试知识点（一）由浅入深，66...

2024前端面试真题之—VUE篇_前端面试题vue2020及答案

添加图片注释，不超过140字（可选）1.vue的生命周期有哪些及每个生命周期做了什么？beforeCreate是newVue()之后触发的第一个钩子，在当前阶段data、methods、com...

今年最常见的前端面试题，你会做几道?

在面试或招聘前端开发人员时，期望、现实和需求之间总是存在着巨大差距。面试其实是一个交流想法的地方，挑战人们的思考方式，并客观地分析给定的问题。可以通过面试了解人们如何做出决策，了解一个人对技术和解决问...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12本站文章收集于网络以及网友投稿，其版权归原创者所有，如有侵害到你的权益，或有不妥之处，请联系删除。