MySQL实战——JSON数据类型_mysql jsonb类型
MySQL JSON类型简介
JavaScript Object Notation(JSON)是一种轻量级的、基于文本的，独立于语言的数据交换格式。它源自ECMAScript编程语言标准。
对于MySQL数据库，我们往往是将它作为一种关系型数据库，存放关系型数据库二维表。其实，从MySQL 5.7.8开始，MySQL已支持JSON数据类型，能够高效访问JSON文档中的数据。可以说是向着NoSQL存储功能迈出了一步。
MySQL 8 大幅改进了对 JSON 的支持，为 schema-less 模式的 JSON 文档提供了多文档事务支持和完整的 ACID 合规性。还添加了基于路径查询参数从 JSON 字段中抽取数据的JSON_EXTRACT()函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG()聚合函数。
MySQL数据库原本可以将JSON格式的字符串当做String类型存储，但与之相比，JSON数据类型有以下优势：
(1)自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。
(2)优化了存储格式。存储在JSON类型中的JSON文档被转换为允许对文档元素进行快速读取访问的内部格式。当读取以这种二进制格式存储的JSON值时，不需要从文本内容中解析该值，而能够直接通过键或数组索引查找子对象或嵌套值。
存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同；MySQL使用utf8mb4字符集和utf8mb4_bin排序规则处理JSON类型的数据，因为utf8mb4_bin是二进制排序规则，所以JSON值是区分大小写的。
下面介绍JSON类型数据的创建、查询、修改以及相关内置函数。
创建
首先创建一个带有JSON类型字段的表，在一个表中，基本数据类型和JSON类型是可以共存的。示例为了简单起见，创建一个只包含JSON类型字段的表，如下：
create table sys_doc (doc json);
有两种方法将JSON数据存入该表。
- 第一种方法，使用MySQL JSON_OBJECT()函数，这种方式下，若JSON值原本存在双引号，需要使用反斜杠对每个引号字符进行转义，例如，将如下这个键值对作为JSON数据存入表中：
mascot: The MySQL mascot is a dolphin named "Sakila".
INSERT语句为：
//JSON_OBJECT()函数构造JSON数据对象
//JSON数据内容中原本的引号需要用反斜杠转义
INSERT INTO sys_doc VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));- 第二种方法，将JSON字符串作为参数传入INSERT语句，这种情况下，需要使用双反斜杠转义JSON值中原本存在的双引号，如下所示：
//直接将JSON字符串存入JSON类型
//使用双反斜杠可以防止MySQL执行转义处理，而是将字符串文本传递给存储引擎进行处理。
INSERT INTO sys_doc VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');通过执行SELECT，可以看到查询出的JSON列值中存在反斜杠，如下所示：
mysql> SELECT doc FROM sys_doc;
+---------------------------------------------------------+
| doc |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+若要查找这个键值对对应的值，可以使用列路径操作符->，如下：
select doc->"$.mascot" from sys_doc;查询结果：
"Our mascot is a dolphin named \"Sakila\"."
这个查询结果保留了反斜杠以及值起始位置的引号。要想只显示所需的值，而不包括起始位置引号和转义符，可以使用内联路径运算符->>，如下所示：
select doc->>"$.mascot" from sys_doc;查询结果：
Our mascot is a dolphin named "Sakila".
查询
将下面这个JSON对象存入上文所建数据库表sys_doc，并以这个JSON对象展示相关JSON函数使用(该示例JSON对象表示了一个学生的高考成绩记录，包括姓名、年龄、总分、语文数学英语以及理综各科分数)：
{
"name":"王小虎",
"age":17,
"totalScore":582,
"gender":"男",
"chinese":120,
"math":105,
"english":118,
"science":[
88,
85,
66
]
}存入以上JSON数据的INSERT语句为：
//JSON_OBJECT()函数创建JSON数据对象
INSERT INTO sys_doc VALUES (JSON_OBJECT('name', '王小虎', 'age', 17, 'totalScore', 582, 'gender', '男', 'chinese', 120, 'math', 105, 'english', 118, 'science', JSON_ARRAY(88, 85, 66)));- 查询“王小虎”同学的总分：
select JSON_EXTRACT(doc, "$.totalScore") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';- 查询“王小虎”同学的物理分数(假设"science":[88,85,66]分别表示理科综合中的物理、化学、生物各科的分数)：
select JSON_EXTRACT(doc, "$.science[0]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';可以看到，访问数组元素的位置索引是以0开头的整数。
- 查询“王小虎”同学的理综各科分数，以下三种方式都实现了相同的效果：
//数组元素索引与to关键字配合使用
select JSON_EXTRACT(doc, "$.science[0 to 2]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';
//数组元素索引last关键字
select JSON_EXTRACT(doc, "$.science[last-2 to last]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';
//表示全部数组元素的通配符*
select JSON_EXTRACT(doc, "$.science[*]") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';- 除了JSON_EXTRACT()函数，还可以用JSON_VALUE()函数，如下：
//JSON_VALUE()函数查询某个字段的值
select JSON_VALUE(doc, "$.science") FROM sys_doc WHERE JSON_VALUE(doc, "$.name") = '王小虎';- 查询出JSON文档中所有的KEY值：
//JSON_KEYS()函数列出JSON中所有key
select JSON_KEYS(doc) from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';- 查询出JSON文档中所有的VALUE值：
//通配符查出JSON中所有value
select JSON_EXTRACT(doc, "$.*") from sys_doc WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';这些JSON相关的函数中，$表示正在操作的文档。下文还将展示更多范例。
修改
- JSON_REPLACE()函数，将“王小虎”的年龄(age)改为20，“生物”分数修改为100：
//JSON_REPLACE()函数
update sys_doc set doc = JSON_REPLACE(doc, '$.age', 20, '$.science[last]', 100) WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';- JSON_SET()函数，将年龄(age)改为21，“生物”分数修改为99：
//JSON_SET()函数
update sys_doc set doc = JSON_SET(doc, '$.age', 21, '$.science[last]', 99) WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';此外，JSON_INSERT()函数也可以实现修改功能，他们三者的区别是函数处理文档中现有值和不存在值的方式不同。具体如下：
- JSON_SET()将替换现有路径的值，并为不存在的路径添加值。
- JSON_INSERT()添加新值，但不替换现有值。
- JSON_REPLACE()替换现有值并忽略新值。
删除
JSON_REMOVE()实现对JSON文档内容删除，参数为一个或多个指定要从文档中删除的值的路径。
- 删除“王小虎”的年龄(age)、生物科目分数，如下：
//JSON_REMOVE()函数
update sys_doc set doc = JSON_REMOVE(doc, '$.age', '$.science[last]') WHERE JSON_EXTRACT(doc, "$.name") = '王小虎';删除后的文档如下，没有了“age”字段以及“science”字段中的最后一个分数：
{
"math": 105,
"name": "王小虎",
"gender": "男",
"chinese": 120,
"english": 118,
"science": [
88,
85
],
"totalScore": 582
} 格式化
JSON_PRETTY()以可读格式打印JSON文档，例如：
select JSON_PRETTY('{"name":{"CN":"张三", "EN":"zhangsan"},"science":[120,98,90]}');将会输入下面格式化的JSON文档：
{
"name": {
"CN": "张三",
"EN": "zhangsan"
},
"science": [
120,
98,
90
]
}