好的，我们开始今天的讲座。
今天我们将深入探讨 MySQL 中处理 JSON 数据时常用的三个高级函数：JSON_INSERT()、JSON_REPLACE() 和 JSON_SET()。 这三个函数都用于修改 JSON 文档中的值，但它们在处理现有键和新键时有所不同。 理解这些差异对于有效地管理和操作存储在 MySQL 数据库中的 JSON 数据至关重要。
1. JSON 数据类型简介
在深入研究这些函数之前，我们先简单回顾一下 MySQL 中的 JSON 数据类型。 JSON (JavaScript Object Notation) 是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。 MySQL 5.7.22 引入了原生 JSON 数据类型，允许在数据库中存储和操作 JSON 文档。
JSON 文档可以包含以下基本数据类型：
- 字符串 (string)
- 数字 (number)
- 布尔值 (boolean)
- 空值 (null)
- 数组 (array)
- 对象 (object)
MySQL 提供了一系列函数来操作 JSON 数据，包括创建、查询、修改和删除 JSON 文档中的元素。 今天的重点是修改函数。
2. JSON_INSERT() 函数
JSON_INSERT() 函数用于将新键值对插入到 JSON 文档中。 关键特点是，它只插入新的键值对，如果指定的路径已经存在，则不会进行任何修改。
语法:
JSON_INSERT(json_doc, path, val[, path, val] ...)json_doc: 要修改的 JSON 文档。path: 一个或多个 JSON 路径，指定要插入的位置。val: 与每个路径对应的值。
示例:
假设我们有一个包含以下 JSON 数据的表 users:
| id | data |
|---|---|
| 1 | {"name": "Alice", "age": 30, "address": {"street": "123 Main St", "city": "Anytown"}} |
| 2 | {"name": "Bob", "age": 25, "skills": ["Java", "Python"]} |
现在，我们使用 JSON_INSERT() 向用户 1 的 JSON 数据中插入一个新的键 email:
UPDATE users
SET data = JSON_INSERT(data, '$.email', '[email protected]')
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 30,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]"
}可以看到，email 键值对已成功插入。
现在，尝试插入一个已存在的键 age:
UPDATE users
SET data = JSON_INSERT(data, '$.age', 31)
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 30,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]"
}age 的值没有被修改，因为 JSON_INSERT() 只插入新的键。
处理数组:
JSON_INSERT() 也可以用于在数组中插入元素。 要在数组的开头插入元素，可以使用路径 $[0]，但需要注意的是，这会将现有元素向后移动。
例如，向用户 2 的 skills 数组中添加 "SQL":
UPDATE users
SET data = JSON_INSERT(data, '$.skills[0]', 'SQL')
WHERE id = 2;
SELECT data FROM users WHERE id = 2;结果:
{
"name": "Bob",
"age": 25,
"skills": [
"SQL",
"Java",
"Python"
]
}"SQL" 被插入到数组的开头，并将 "Java" 和 "Python" 向后移动。
3. JSON_REPLACE() 函数
JSON_REPLACE() 函数用于替换 JSON 文档中现有键的值。 它只替换现有键的值，如果指定的路径不存在，则不会进行任何修改。
语法:
JSON_REPLACE(json_doc, path, val[, path, val] ...)json_doc: 要修改的 JSON 文档。path: 一个或多个 JSON 路径，指定要替换的位置。val: 与每个路径对应的新值。
示例:
继续使用上面的 users 表。 现在，我们使用 JSON_REPLACE() 修改用户 1 的 age：
UPDATE users
SET data = JSON_REPLACE(data, '$.age', 31)
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 31,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]"
}age 的值已成功替换为 31。
现在，尝试替换一个不存在的键 phone:
UPDATE users
SET data = JSON_REPLACE(data, '$.phone', '123-456-7890')
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 31,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]"
}phone 键没有被添加到 JSON 文档中，因为 JSON_REPLACE() 只替换现有键。
处理数组:
JSON_REPLACE() 也可以用于替换数组中的元素。
例如，将用户 2 的 skills 数组中的 "Java" 替换为 "C++":
UPDATE users
SET data = JSON_REPLACE(data, '$.skills[1]', 'C++')
WHERE id = 2;
SELECT data FROM users WHERE id = 2;结果:
{
"name": "Bob",
"age": 25,
"skills": [
"SQL",
"C++",
"Python"
]
}"Java" 已被 "C++" 替换。
4. JSON_SET() 函数
JSON_SET() 函数是这三个函数中最通用的。 它可以插入新的键值对，也可以替换现有键的值。 换句话说，如果指定的路径存在，它会替换该路径的值；如果路径不存在，它会插入一个新的键值对。
语法:
JSON_SET(json_doc, path, val[, path, val] ...)json_doc: 要修改的 JSON 文档。path: 一个或多个 JSON 路径，指定要设置的位置。val: 与每个路径对应的值。
示例:
继续使用上面的 users 表。 现在，我们使用 JSON_SET() 修改用户 1 的 age：
UPDATE users
SET data = JSON_SET(data, '$.age', 32)
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 32,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]"
}age 的值已成功替换为 32。
现在，尝试添加一个不存在的键 phone:
UPDATE users
SET data = JSON_SET(data, '$.phone', '123-456-7890')
WHERE id = 1;
SELECT data FROM users WHERE id = 1;结果:
{
"name": "Alice",
"age": 32,
"address": {
"street": "123 Main St",
"city": "Anytown"
},
"email": "[email protected]",
"phone": "123-456-7890"
}phone 键值对已成功添加到 JSON 文档中。
处理数组:
JSON_SET() 也可以用于操作数组。
例如，在用户 2 的 skills 数组中添加 "Go":
UPDATE users
SET data = JSON_SET(data, '$.skills[3]', 'Go')
WHERE id = 2;
SELECT data FROM users WHERE id = 2;结果:
{
"name": "Bob",
"age": 25,
"skills": [
"SQL",
"C++",
"Python",
"Go"
]
}"Go" 已被添加到数组的末尾。 注意，如果索引跳跃（例如，直接设置 $.skills[5]，而 $.skills[4] 不存在），MySQL会自动在中间插入 NULL 值。
5. 总结：三个函数的比较
为了更清楚地理解这三个函数之间的差异，我们用表格来总结一下：
| 函数 | 行为 |
|---|---|
JSON_INSERT() |
只插入新的键值对。 如果指定的路径已存在，则不进行任何修改。 |
JSON_REPLACE() |
只替换现有键的值。 如果指定的路径不存在，则不进行任何修改。 |
JSON_SET() |
如果指定的路径存在，则替换该路径的值。 如果路径不存在，则插入一个新的键值对。 |
6. 实际应用场景
-
JSON_INSERT(): 当需要确保只添加新的信息，避免覆盖现有数据时，可以使用JSON_INSERT()。 例如，在用户注册时，可以安全地添加用户的其他信息，而不会意外覆盖已有的核心数据。 -
JSON_REPLACE(): 当需要更新现有信息，但不希望添加任何新的键时，可以使用JSON_REPLACE()。 例如，更新用户的地址信息，但不想添加任何新的字段。 -
JSON_SET(): 当需要灵活地更新或添加信息时，可以使用JSON_SET()。 例如，根据用户的操作，动态地更新用户信息，或者添加新的配置项。
7. 路径表达式 (Path Expressions)
在使用这三个函数时，正确使用路径表达式至关重要。 路径表达式用于指定 JSON 文档中要操作的位置。
$: 表示 JSON 文档的根。.key: 表示对象中的一个键。[index]: 表示数组中的一个元素。[*]: 表示数组中的所有元素 (通常与JSON_ARRAYAGG()等函数结合使用)。**.key: 表示所有层级的key键 (MySQL 8.0.17 引入)。
例如：
$.name: 访问根对象中的name键。$.address.city: 访问根对象中address对象的city键。$.skills[1]: 访问根对象中skills数组的第二个元素。$."first name": 如果键名包含空格或其他特殊字符，需要用双引号括起来。
8. 注意事项
- NULL 值: 如果
val为NULL，则会将指定的键设置为NULL。 - 数据类型: 确保
val的数据类型与目标键的数据类型兼容。 否则，可能会导致数据类型转换错误。 - 性能: 频繁地修改大型 JSON 文档可能会影响性能。 考虑使用更有效的数据结构或优化查询。
- 错误处理: 如果路径表达式无效，或者发生其他错误，这些函数通常会返回
NULL。 建议在生产环境中使用适当的错误处理机制。 - MySQL 版本: 确保使用的 MySQL 版本支持 JSON 数据类型和这些函数。
9. 实例演示
假设我们有一个存储产品信息的表 products:
| id | product_data |
|---|---|
| 1 | {"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]} |
| 2 | {"name": "Mouse", "price": 25} |
| 3 | {"name": "Keyboard", "price": 75, "layout": "US"} |
- 添加一个新特性到 Laptop：
UPDATE products
SET product_data = JSON_SET(product_data, '$.features[2]', 'Backlit Keyboard')
WHERE id = 1;- 更新 Mouse 的价格：
UPDATE products
SET product_data = JSON_REPLACE(product_data, '$.price', 30)
WHERE id = 2;- 为 Keyboard 添加制造商信息：
UPDATE products
SET product_data = JSON_INSERT(product_data, '$.manufacturer', 'Logitech')
WHERE id = 3;- 批量更新价格 (使用 JSON_SET 结合 CASE 语句):
UPDATE products
SET product_data = JSON_SET(product_data, '$.price',
CASE
WHEN id = 1 THEN 1300
WHEN id = 2 THEN 35
ELSE JSON_EXTRACT(product_data, '$.price') -- Maintain existing price
END)
WHERE id IN (1, 2);
这个例子展示了如何结合 JSON_SET 和 CASE 语句来根据不同的条件批量更新 JSON 数据。 JSON_EXTRACT 用于提取当前价格，以便在不满足条件时保持原始值。
10. 进一步探讨
除了 JSON_INSERT(), JSON_REPLACE(), 和 JSON_SET()， MySQL 还提供了其他有用的 JSON 函数，例如：
JSON_EXTRACT(): 从 JSON 文档中提取数据。JSON_REMOVE(): 从 JSON 文档中删除数据。JSON_ARRAYAGG(): 将多个值聚合到一个 JSON 数组中。JSON_OBJECT(): 创建一个 JSON 对象。JSON_VALID(): 验证一个字符串是否是有效的 JSON 文档。
掌握这些函数可以更有效地处理和操作 MySQL 中的 JSON 数据。
总而言之，JSON_INSERT(), JSON_REPLACE(), 和 JSON_SET() 是 MySQL 中用于修改 JSON 数据的三个重要函数。 了解它们的区别和用法，可以帮助你更灵活和高效地管理存储在数据库中的 JSON 文档。 记住，选择正确的函数取决于你的具体需求，以及你希望如何处理现有键和新键。通过本文的学习，希望大家能够熟练使用这三个函数，在实际开发中灵活运用。