逐浪水花·数据库系列 | 玩转MySQL操作JSON数据类型

vlambda
2021-04-02

逐浪水花·数据库系列 | 玩转MySQL操作JSON数据类型

说起关系型数据库，不得不提的就是Oracle和MySQL了，DB Engine网站(https://db-engines.com/en /ranking)的数据库排行显示，Oracle和MySQL的受欢迎程度遥遥领先。今天我们将会认识MySQL的一种数据类型：JSON数据类型。

简述

MySQL在5.5.7版本加入了对JSON数据类型的操作。

对于JSON类型的数据，它有以下特点：

自动验证JSON数据的格式，如果格式错误，则会产生错误警告。
优化存储格式。JSON列中存储的JSON文档将转换为内部格式，以允许快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的JSON值时，则无需从文本表示形式解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

JSON数据类型占用的空间：

JSON列的存储要求与LONGBLOB或 LONGTEXT列的存储要求基本相同。但是，存储在JSON文档中的各个值的二进制编码（包括查找所需的元数据和字典）会产生开销。例如，存储在JSON文档中的字符串需要4到10个字节的额外存储空间，具体取决于字符串的长度以及存储该字符串的对象或数组的大小。

JSON数据类型

创建t1表，表中jdoc字段为JSON类型：

CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `jdoc` json DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入数据：

test> INSERT INTO t1(jdoc) VALUES('{"name": "Tom", "email": "[email protected]"}') 1 row affected in 125 ms

当插入的数据不符合JSON规范格式的时候，会报详细的错误信息，如下：

test> INSERT INTO t1(jdoc) VALUES('[1, 2,')[3140] Data truncation: Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.

JSON函数介绍

●

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

该函数中第一个参数是JSON数据，后面的参数可以为一个，也可以为多个。

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');//执行结果：20

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');//执行结果：[20, 10]

使用通配符 * 可以查询所有元素。

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[*]');//执行结果：[10, 20, [30, 40]]

●

该函数与JSON_EXTRACT() 函数类似，用法如下：

INSERT INTO t1(jdoc) VALUES('{"id": "3", "name": "Barney"}');INSERT INTO t1(jdoc) VALUES('{"id": "4", "name": "Betty"}');INSERT INTO t1(jdoc) VALUES('{"id": "2", "name": "Wilma"}');INSERT INTO t1(jdoc) VALUES('{"id": "5", "name": "Mary"}');
select jdoc->"$.name" from t1;
/*执行结果："Barney""Betty""Wilma""Mary"*/

●

->>

->> 和 ->的区别是：->查询回的结果会带上双引号，而->>则会去掉双引号，效果如下：

select jdoc->>"$.name" from t1;/*执行结果：BarneyBettyWilmaMary*/

●

函数功能

->
评估路径后从JSON列返回值；等效于JSON_EXTRACT（）。
->>
在评估路径并取消引用结果后，从JSON列返回值；等效于JSON_UNQUOTE（JSON_EXTRACT（））
JSON_ARRAY()
创建JSON数组
JSON_ARRAY_APPEND()
将数据追加到JSON文档
JSON_ARRAY_INSERT()
插入JSON数组
JSON_CONTAINS()
JSON文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()
JSON文档是否在路径中包含任何数据
JSON_DEPTH()
JSON文档的最大深度
JSON_EXTRACT()
从JSON文档返回数据
JSON_INSERT()
将数据插入JSON文档
JSON_KEYS()
JSON文档中的键数组
JSON_LENGTH()
JSON文档中的元素数
JSON_MERGE() （已弃用）
合并JSON文档，保留重复的键。JSON_MERGE_PRESERVE（）的已弃用同义词
JSON_MERGE_PATCH()
合并JSON文档，替换重复键的值
JSON_MERGE_PRESERVE()
合并JSON文档，保留重复的键
JSON_OBJECT()
创建JSON对象
JSON_OVERLAPS() （8.0.17引入）
比较两个JSON文档，如果它们具有相同的键值对或数组元素，则返回TRUE（1），否则返回FALSE（0）
JSON_PRETTY()
以易于阅读的格式打印JSON文档
JSON_QUOTE()
引用JSON文档
JSON_REMOVE()
从JSON文档中删除数据
JSON_REPLACE()
替换JSON文档中的值
JSON_SCHEMA_VALID() （8.0.17引入）
根据JSON模式验证JSON文档；如果文档针对架构进行验证，则返回TRUE / 1；否则，则返回FALSE / 0
JSON_SCHEMA_VALIDATION_REPORT() （8.0.17引入）
根据JSON模式验证JSON文档；以JSON格式返回有关验证结果的报告，包括成功或失败以及失败原因
JSON_SEARCH()
JSON文档中值的路径
JSON_SET()
将数据插入JSON文档
JSON_STORAGE_FREE()
部分更新后，JSON列值的二进制表示形式中的可用空间
JSON_STORAGE_SIZE()
用于存储JSON文档的二进制表示形式的空间
JSON_TABLE()
从JSON表达式返回数据作为关系表
JSON_TYPE()
JSON值类型
JSON_UNQUOTE()
取消引用JSON值
JSON_VALID()
JSON值是否有效
JSON_VALUE()（8.0.21引入）
在提供的路径所指向的位置从JSON文档中提取值；以VARCHAR（512）或指定的类型返回此值
MEMBER OF()（8.0.17引入）
如果第一个操作数与作为第二个操作数传递的JSON数组的任何元素匹配，则返回true（1），否则返回false（0）

JSON数据类型建索引

给JSON类型字段建索引和给一般类型字段建索引的方式不太一样。

我们先展示一种错误的用法：

CREATE TABLE employees (  data JSON,  INDEX ((data->>'$.name')));

上面创建的索引是不会生效的，因为：

->> 等效于 JSON_UNQUOTE(JSON_EXTRACT(...))，JSON_UNQUOTE()返回LONGTEXT类型的数据，MySQL不能为LONGTEXT类型数据建立没有前缀长度的索引，并且功能键部分不允许前缀长度。

如果要为JSON类型数据列建立索引，可以用下面的写法：

CREATE TABLE employees (  data JSON,  INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin)));

向employees表中插入测试数据：

INSERT INTO employees VALUES  ('{ "name": "james", "salary": 9000 }'),  ('{ "name": "James", "salary": 10000 }'),  ('{ "name": "Mary", "salary": 12000 }'),  ('{ "name": "Peter", "salary": 8000 }');

查看查询名字为James的语句是否使用了索引：

EXPLAIN SELECT * FROM employees WHERE data->>'$.name' = 'James';

结果如下图所示，我们可以看到是使用索引了的。

小结

MySQL的JSON数据类型还有很多其他的特性，MySQL 8.0的官方文档链接供大家进行参考：

https://dev.mysql.com/doc/refman/8.0/en/

内容比较多，大家按需进行查看和使用！

（责任编辑：张子鑫）

”

作者/ 葛建

葛建，任职于北银金融科技有限责任公司银行转型业务开发部。主要擅长Java后端开发、数据库、微服务等相关技术。

招聘启事

北银金融科技有限责任公司根植于北京银行，是一家致力于大数据、人工智能、云计算、区块链、物联网等新技术创新与金融科技应用的科技企业，公司充分发挥北京银行企业文化和技术积淀先天优势，通过对技术、场景、生态的完美融合，输出科技创新产品和技术服务。

现诚邀优秀人才加盟

共享金融科技时代硕果

扫描此二维码

期待您的加入

vlambda博客
学习文章列表