目录随着 NoSQL 数据库的兴起，JSON 作为一种轻量级的数据互换格式受到了广泛的关注。为了满意现代应用程序的需求，MySQL 8引入了原生的 JSON 数据范例，提供了一系列强盛的 JSON 函数来处理处罚和查询 JSON 数据。本文将深入探讨 MySQL 8 中JSON 范例的特性、函数、索引以及实际应用场景。
1. 弁言
在早期的 MySQL 版本中，开发者通常将 JSON 数据以字符串的形式存储在数据库中，这导致了查询效率低下和数据处理处罚复杂。为了办理这个问题，MySQL 8 引入了原生的 JSON 数据范例，允许我们以布局化的方式存储和查询 JSON 数据。
2. JSON 数据范例特性
- 验证：当插入或更新 JSON 列时，MySQL 会自动验证数据的 JSON 格式，确保数据的完整性。
- 优化存储：JSON 数据范例以二进制格式存储，相比纯文本存储更加高效。
- 灵活性：JSON 列可以存储数组、对象、嵌套布局等，为数据的表示提供了极大的灵活性。
3. JSON 函数
MySQL 8 提供了一系列内置函数来操作和查询 JSON 数据：
- 提取数据：利用 JSON_EXTRACT() 函数可以从 JSON 文档中提取指定的数据片断。
- 修改数据：JSON_INSERT()、JSON_REPLACE() 和 JSON_REMOVE() 函数允许你向 JSON 文档中添加、替换或删除数据。
- 创建 JSON：JSON_ARRAY() 和 JSON_OBJECT() 函数用于创建 JSON 数组和对象。
- 查询功能：JSON_LENGTH()、JSON_KEYS()、JSON_VALID() 等函数，用于获取 JSON 数据的长度、键或验证 JSON 数据的有用性。
- 其他函数，如 JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() 等等。
下面的例子看看每个函数的具体利用方法：
首先，我们创建一个名为 json_example 的表，并插入一条 JSON 数据：
[code]CREATE TABLE json_example (
id INT AUTO_INCREMENT PRIMARY KEY,
data JSON
);
INSERT INTO json_example (data) VALUES (
'{
"name": "John Doe",
"age": 30,
"address": {
"street": "123 Main St",
"city": "Anytown",
"zip": "12345"
},
"phoneNumbers": ["123-456-7890", "987-654-3210"],
"isActive": true
}'
);
[/code]
表 json_example 中有一条包罗 JSON 数据的记录。接下来，我们将利用差别的 JSON 函数来查询和修改这个数据.
3.1 JSON_EXTRACT()
[code]-- 提取 name 字段的值
SELECT JSON_EXTRACT(data, '$.name') AS extracted_name FROM json_example;
-- 效果: "John Doe"
-- 提取 address 对象的 city 字段的值
SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM json_example;
-- 效果: "Anytown"
[/code]
3.2 JSON_INSERT()
- 向 JSON 数据中插入新的部分，如果路径已存在则不会替换。
[code]-- 在 phoneNumbers 数组后面插入一个新的电话号码
UPDATE json_example
SET data = JSON_INSERT(data, '$.phoneNumbers[2]', '555-123-4567');
-- 此时 phoneNumbers 变为 ["123-456-7890", "987-654-3210", "555-123-4567"]
[/code]
3.3 JSON_REPLACE()
- 替换 JSON 数据中的部分，如果路径不存在则不会添加。
[code]-- 替换 name 字段的值
UPDATE json_example
SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');
-- 此时 name 变为 "Jane Smith"
[/code]
3.4 JSON_REMOVE()
[code]-- 移除 phoneNumbers 数组中的第一个电话号码
UPDATE json_example
SET data = JSON_REMOVE(data, '$.phoneNumbers[0]');
-- 此时 phoneNumbers 变为 ["987-654-3210", "555-123-4567"]
[/code]
3.5 JSON_ARRAY() 和 JSON_OBJECT()
[code]-- 创建一个新的 JSON 数组
SELECT JSON_ARRAY('a', 1, TRUE);
-- 效果: ["a", 1, true]
-- 创建一个新的 JSON 对象
SELECT JSON_OBJECT('key1', 'value1', 'key2', 2);
-- 效果: {"key1": "value1", "key2": 2}
JSON_LENGTH() - 获取 JSON 文档或数组的长度。
sql
-- 获取 phoneNumbers 数组的长度
SELECT JSON_LENGTH(data->'$.phoneNumbers') AS phone_numbers_length FROM json_example;
-- 效果: 2 (由于 phoneNumbers 数组如今有两个元素)
[/code]
3.6 JSON_KEYS()
[code]-- 获取 JSON 对象的全部键
SELECT JSON_KEYS(data) AS object_keys FROM json_example;
-- 效果: ["name", "age", "address", "phoneNumbers", "isActive"]
[/code]
3.7 JSON_VALID()
[code]-- 验证 data 列是否包罗有用的 JSON
SELECT JSON_VALID(data) AS is_valid_json FROM json_example;
-- 效果: 1 (表示 true，由于 data 列包罗有用的 JSON)
[/code]
3.8 JSON_QUOTE() 和 JSON_UNQUOTE()
- 将字符串转换为 JSON 格式的字符串，以及反向操作。
假设json_example 表中存在这样一条数据
[code]INSERT INTO json_example (data) VALUES (
'{
"name": "John",
"interests": ["reading", "music"],
"friends": [
{"name": "Alice", "age": 28},
{"name": "Bob", "age": 32}
]
}'
);
[/code]
如今我们将利用上述函数对这条数据进行操作：
[code]-- 利用 JSON_QUOTE 将寻常字符串转换为 JSON 字符串
SELECT JSON_QUOTE('Hello, World!') AS quoted_string;
-- 效果: ""Hello, World!""
-- 利用 JSON_UNQUOTE 将 JSON 字符串转换回寻常字符串
SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;
-- 效果: Hello, World!
[/code]
请注意，在实际的数据列上利用这些函数时，你通常会对已存储的 JSON 值或要插入的值进行操作。
3.9 JSON_CONTAINS()
[code]-- 检查 interests 数组是否包罗 "reading"
SELECT JSON_CONTAINS(data->'$.interests', '"reading"') AS contains_reading FROM json_example;
-- 效果: 1 (表示 true，由于 interests 数组包罗 "reading")
[/code]
注意，由于 JSON 中的字符串是被双引号困绕的，以是我们在查询时也必要对搜索的字符串值加上双引号。
3.9 JSON_CONTAINS_PATH()
[code]-- 检查是否存在 friends 数组中的对象的 name 路径
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;
-- 效果: 1 (表示 true，由于存在该路径)
[/code]
3.10 JSON_ARRAY_APPEND()
[code]-- 向 interests 数组追加 "traveling"
UPDATE json_example
SET data = JSON_SET(data, '$.interests[2]', 'traveling');
-- 注意：这里利用了 JSON_SET，由于 JSON_ARRAY_APPEND 必要指定路径到具体数组
-- 在 MySQL 8.0.17 及更高版本中，可以利用 JSON_ARRAY_APPEND 精确地追加元素
-- 比方: JSON_ARRAY_APPEND(data, '$.interests', 'traveling')
[/code]
注意：上面的例子中利用了 JSON_SET 而不是 JSON_ARRAY_APPEND，由于在 MySQL 8.0.17 之前，JSON_ARRAY_APPEND 的语法有些差别，它要求指定路径到一个具体的数组元素。从 8.0.17 开始，JSON_ARRAY_APPEND 可以精确地追加到数组末尾。
精确的 JSON_ARRAY_APPEND 用法如下：
[code]-- 向 interests 数组追加 "traveling"（实用于 MySQL 8.0.17 及更高版本）
UPDATE json_example
SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');[/code]
3.11 JSON_ARRAY_INSERT()
[code]-- 在 interests 数组的第一个位置插入 "gaming"
UPDATE json_example
SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');
-- 效果: interests 数组如今是 ["gaming", "reading", "music", "traveling"]
[/code]
4. JSON 索引
为了提高查询性能，MySQL 8 支持为 JSON 列创建索引。但由于 JSON 数据的灵活性，直接对整个 JSON 文档创建索引并不高效。因此，MySQL 引入了虚拟列（Virtual Columns）的概念。
- 虚拟列：虚拟列允许你根据 JSON 列中的值生成一个新的列，并为这个新列创建索引。这样，当你根据 JSON 数据中的某个字段进行查询时，MySQL 可以利用索引来加快查询。（关于虚拟列我将在之后的文章详解）
- 创建索引：通过团结利用 JSON_EXTRACT() 函数和虚拟列，你可以轻松地为 JSON 数据中的特定字段创建索引。
基于上面的json_example 表，我们来看下为json字段创建索引
4.1 添加虚拟列
我们将添加一个名为 first_interest 的虚拟列，该列将存储 interests 数组的第一个元素。
[code]ALTER TABLE json_example
ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;
[/code]
在这里，我们利用了 JSON_EXTRACT() 来获取 interests 数组的第一个元素，并用 JSON_UNQUOTE() 去除引号，由于 JSON_EXTRACT() 返回的是 JSON 格式的字符串。
4.2 为虚拟列创建索引
[code]CREATE INDEX idx_first_interest ON json_example(first_interest);
[/code]
如今，我们为 first_interest 列创建了一个索引，这将加快基于该列的查询。
4.3 查询优化
如今，我们可以基于 first_interest 列进行查询，并利用索引来加快查询过程。
[code]SELECT * FROM json_example WHERE first_interest = 'reading';
[/code]
由于我们为 first_interest 创建了索引，这个查询将会更加高效。但是，请注意，这种方法仅实用于查询 interests 数组的第一个元素。如果你必要查询数组中的其他元素，你大概必要采取其他策略，比如利用全文搜索、倒排索引大概将 JSON 数据规范化到关系型布局中。
5. 实际应用场景
- 配置文件存储：应用程序的配置信息通常以 JSON 格式表示。利用 MySQL 8 的 JSON 数据范例，你可以轻松地将这些配置信息存储在数据库中，并利用 JSON 函数进行查询和修改。
- 日记记录：日记条目通常以布局化的格式存储，JSON 是一个抱负的选择。通过将日记数据存储在 JSON 列中，你可以轻松地分析和查询日记数据。
- 与前端集成：利用 JSON 与后端进行数据互换。利用 MySQL 8 的 JSON 支持，你可以简化数据库与前端之间的数据交互。
6. 注意事项
- 性能：固然 MySQL 8 提供了对 JSON 的支持，但与传统的关系型数据相比，JSON 查询大概仍旧不敷高效。
- 数据验证：固然 MySQL 会验证 JSON 数据的格式，但它不会验证数据的业务规则或完整性。
- 复杂性：JSON 数据的布局大概比传统的关系型数据更复杂，这大概会增加查询和维护的难度。
7. 结语
MySQL 8 的 JSON 数据范例为存储和查询 JSON 数据提供了强盛的支持。通过内置的 JSON 函数和虚拟列索引，开发者可以高效地处理处罚 JSON 数据，满意现代应用程序的需求。如果你正在开发必要存储和查询 JSON 数据的应用程序，不妨思量利用 MySQL 8 的 JSON 功能来简化你的工作。
到此这篇关于MySQL JSON范例的功能与应用的文章就介绍到这了,更多相关MySQL JSON范例内容请搜索脚本之家以前的文章或继续浏览下面的相关文章盼望各人以后多多支持脚本之家！
来源：https://www.jb51.net/database/325827esr.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
/6 
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
