MySQL高级函数之：`JSON_INSERT()`：其在向`JSON`文档插入值时的应用。

MySQL JSON_INSERT() 函数详解：实战应用与最佳实践

大家好，今天我们来深入探讨 MySQL 中强大的 JSON 函数之一：JSON_INSERT()。在处理半结构化数据时，JSON 数据类型越来越受欢迎，而 JSON_INSERT() 函数为我们提供了向 JSON 文档中添加新值的高效方法。本次讲座将详细介绍 JSON_INSERT() 的语法、工作原理、使用场景，并通过丰富的示例代码展示如何在实际项目中应用它。

1. JSON_INSERT() 函数概述

JSON_INSERT() 函数用于将新键值对插入到现有的 JSON 文档中。与 JSON_REPLACE() 和 JSON_SET() 不同，JSON_INSERT() 仅在指定路径不存在时才插入新值。如果路径已经存在，则 JSON_INSERT() 不会修改该路径的值，并保持原始值不变。

语法:

JSON_INSERT(json_doc, path, val[, path, val] ...)

• json_doc: 要修改的 JSON 文档。
• path: JSON 文档中要插入值的路径。路径使用 $. 表示根节点，. 用于访问对象属性，[index] 用于访问数组元素。
• val: 要插入的值。可以是一个常量、表达式或其他 JSON 文档。

JSON_INSERT() 函数可以接受多个 path-val 对，允许一次性插入多个键值对。

2. JSON_INSERT() 的工作原理

JSON_INSERT() 按照从左到右的顺序处理 path-val 对。对于每个 path-val 对，它首先检查指定的 path 是否存在于 json_doc 中。

• 如果 path 不存在: JSON_INSERT() 会将 val 插入到 json_doc 中指定的 path 下。
• 如果 path 已经存在: JSON_INSERT() 不会执行任何操作，保持 json_doc 中该 path 的值不变。

JSON_INSERT() 返回修改后的 JSON 文档。如果 json_doc 为 NULL，则返回 NULL。

3. 实际应用场景与示例

为了更好地理解 JSON_INSERT() 的用法，我们通过一些实际的例子进行演示。

3.1 向 JSON 对象添加新的键值对

假设我们有一个存储产品信息的表 products，其中 details 列存储 JSON 格式的产品详情。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    details JSON
);

INSERT INTO products (name, details) VALUES
('Product A', '{"price": 100, "color": "red"}'),
('Product B', '{"price": 200, "size": "medium"}');

现在，我们想为 Product A 添加一个 weight 属性。

UPDATE products
SET details = JSON_INSERT(details, '$.weight', 1.5)
WHERE name = 'Product A';

SELECT * FROM products WHERE name = 'Product A';

执行结果：

id | name      | details
---|-----------|--------------------------------------
 1 | Product A | {"price": 100, "color": "red", "weight": 1.5}

JSON_INSERT() 成功地将 weight: 1.5 添加到了 Product A 的 details JSON 对象中。

如果我们尝试再次执行相同的 UPDATE 语句，weight 的值将不会改变，因为 $.weight 路径已经存在。

3.2 向 JSON 数组添加元素

假设我们的 products 表中，details JSON 对象的 tags 属性是一个数组。

UPDATE products
SET details = JSON_SET(details, '$.tags', JSON_ARRAY('electronics', 'gadget'))
WHERE name = 'Product B';

SELECT * FROM products WHERE name = 'Product B';

执行结果：

id | name      | details
---|-----------|--------------------------------------------------
 2 | Product B | {"price": 200, "size": "medium", "tags": ["electronics", "gadget"]}

现在，我们想向 tags 数组添加一个新标签 featured。

UPDATE products
SET details = JSON_INSERT(details, '$.tags[2]', 'featured')
WHERE name = 'Product B';

SELECT * FROM products WHERE name = 'Product B';

执行结果：

id | name      | details
---|-----------|-----------------------------------------------------------------
 2 | Product B | {"price": 200, "size": "medium", "tags": ["electronics", "gadget"]}

可以看到，并没有将featured插入到数组中，因为索引为2的位置已经超出范围，所以 JSON_INSERT没有进行操作。如果想插入，需要使用JSON_ARRAY_APPEND函数，或者使用大于当前数组长度的索引，JSON_INSERT会插入到对应的位置，中间用null填充。

例如：

UPDATE products
SET details = JSON_INSERT(details, '$.tags[5]', 'new_tag')
WHERE name = 'Product B';

SELECT * FROM products WHERE name = 'Product B';

结果：

id | name      | details
---|-----------|---------------------------------------------------------------------------------------
 2 | Product B | {"price": 200, "size": "medium", "tags": ["electronics", "gadget", null, null, null, "new_tag"]}

3.3 向嵌套的 JSON 对象添加键值对

考虑一个更复杂的 JSON 结构，其中 details 对象包含嵌套的子对象。

UPDATE products
SET details = JSON_SET(details, '$.manufacturer', JSON_OBJECT('name', 'ABC Corp', 'location', 'USA'))
WHERE name = 'Product A';

SELECT * FROM products WHERE name = 'Product A';

执行结果：

id | name      | details
---|-----------|---------------------------------------------------------------------------------------------------------
 1 | Product A | {"price": 100, "color": "red", "weight": 1.5, "manufacturer": {"name": "ABC Corp", "location": "USA"}}

现在，我们想向 manufacturer 对象添加一个 founded_year 属性。

UPDATE products
SET details = JSON_INSERT(details, '$.manufacturer.founded_year', 1980)
WHERE name = 'Product A';

SELECT * FROM products WHERE name = 'Product A';

执行结果：

id | name      | details
---|-----------|----------------------------------------------------------------------------------------------------------------------
 1 | Product A | {"price": 100, "color": "red", "weight": 1.5, "manufacturer": {"name": "ABC Corp", "location": "USA", "founded_year": 1980}}

JSON_INSERT() 成功地将 founded_year: 1980 添加到了 manufacturer 对象中。

3.4 同时插入多个键值对

JSON_INSERT() 允许一次性插入多个键值对，这可以提高效率。

UPDATE products
SET details = JSON_INSERT(details, '$.discount', 0.1, '$.in_stock', true)
WHERE name = 'Product B';

SELECT * FROM products WHERE name = 'Product B';

执行结果：

id | name      | details
---|-----------|---------------------------------------------------------------------------------------------------------------------------------
 2 | Product B | {"price": 200, "size": "medium", "tags": ["electronics", "gadget", null, null, null, "new_tag"], "discount": 0.1, "in_stock": true}

JSON_INSERT() 同时插入了 discount 和 in_stock 两个属性。

3.5 与其他 JSON 函数结合使用

JSON_INSERT() 通常与其他 JSON 函数结合使用，以实现更复杂的操作。例如，我们可以先使用 JSON_EXTRACT() 检查路径是否存在，然后使用 JSON_INSERT() 插入新值。

-- 检查 '$.description' 是否存在
SELECT JSON_EXTRACT(details, '$.description') FROM products WHERE name = 'Product A';

-- 如果不存在，则插入 '$.description'
UPDATE products
SET details = JSON_INSERT(details, '$.description', 'A high-quality product.')
WHERE name = 'Product A' AND JSON_EXTRACT(details, '$.description') IS NULL;

SELECT * FROM products WHERE name = 'Product A';

3.6 处理 NULL 值

如果插入的值为 NULL，JSON_INSERT() 会将 NULL 值插入到 JSON 文档中。

UPDATE products
SET details = JSON_INSERT(details, '$.shipping_date', NULL)
WHERE name = 'Product B';

SELECT * FROM products WHERE name = 'Product B';

执行结果：

id | name      | details
---|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------
 2 | Product B | {"price": 200, "size": "medium", "tags": ["electronics", "gadget", null, null, null, "new_tag"], "discount": 0.1, "in_stock": true, "shipping_date": null}

4. JSON_INSERT() 的局限性与注意事项

• 只能插入新值: JSON_INSERT() 的主要限制是它只能插入新值。如果路径已经存在，则不会修改现有值。如果需要替换现有值，应使用 JSON_REPLACE() 或 JSON_SET()。
• 路径表达式: 正确使用路径表达式对于 JSON_INSERT() 的成功至关重要。确保路径表达式准确地指向要插入值的位置。错误的路径表达式可能导致插入失败或插入到错误的位置。
• 数据类型: JSON_INSERT() 会自动将插入的值转换为适当的 JSON 数据类型。例如，插入一个数字会转换为 JSON 数字，插入一个字符串会转换为 JSON 字符串。
• 性能: 对于大型 JSON 文档，频繁地使用 JSON_INSERT() 可能会影响性能。应尽量避免在循环中或高并发场景下过度使用 JSON_INSERT()。可以考虑使用批量更新或优化 JSON 文档结构来提高性能。
• 错误处理: JSON_INSERT() 在遇到错误时可能会返回 NULL。建议在代码中添加适当的错误处理机制，以处理 NULL 值或潜在的异常情况。

5. JSON_INSERT() 与 JSON_REPLACE() 和 JSON_SET() 的区别

函数	功能	路径存在时	路径不存在时
JSON_INSERT()	插入新值	不修改	插入
JSON_REPLACE()	替换现有值	替换	不修改
JSON_SET()	插入或替换值	替换	插入

理解这些函数的区别有助于选择最适合特定场景的函数。JSON_INSERT() 适用于只添加新值而不修改现有值的场景。JSON_REPLACE() 适用于只替换现有值的场景。JSON_SET() 适用于需要同时添加新值和替换现有值的场景。

6. 最佳实践

• 谨慎使用路径表达式: 仔细检查路径表达式，确保它们准确地指向要插入值的位置。使用 JSON_EXTRACT() 验证路径是否存在，以避免意外的插入。
• 批量更新: 如果需要插入多个键值对，尽量使用单个 JSON_INSERT() 语句，而不是多个单独的语句。这可以减少数据库的访问次数，提高性能。
• 索引优化: 如果经常需要根据 JSON 文档中的某个属性进行查询，可以考虑在该属性上创建索引。这可以加快查询速度。MySQL 5.7.22 及更高版本支持在 JSON 列上创建虚拟列索引。
• 数据验证: 在插入 JSON 数据之前，进行数据验证，确保数据的格式和类型符合预期。这可以避免数据质量问题。
• 监控性能: 监控 JSON_INSERT() 的性能，特别是在处理大型 JSON 文档或高并发场景下。如果发现性能瓶颈，可以考虑优化 JSON 文档结构或使用其他方法来提高性能。
• 考虑使用JSON_ARRAY_APPEND： 在数组中追加元素时，优先考虑使用JSON_ARRAY_APPEND函数，而不是使用JSON_INSERT函数，因为JSON_ARRAY_APPEND函数更适合在数组末尾追加元素，且不会出现索引越界的情况。

7. 示例代码：用户资料管理

让我们通过一个更完整的示例来演示 JSON_INSERT() 在实际项目中的应用。假设我们正在开发一个用户资料管理系统，其中用户资料存储在 users 表的 profile 列中，profile 列的数据类型为 JSON。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) UNIQUE,
    profile JSON
);

INSERT INTO users (username, profile) VALUES
('john.doe', '{"email": "[email protected]", "age": 30}'),
('jane.smith', '{"email": "[email protected]"}');

现在，我们需要实现以下功能：

1. 添加用户的电话号码: 如果用户资料中没有电话号码，则添加电话号码。
2. 添加用户的地址: 如果用户资料中没有地址，则添加地址。
3. 添加用户的兴趣爱好: 如果用户资料中没有兴趣爱好，则添加一个包含默认兴趣爱好的数组。

-- 添加用户的电话号码
UPDATE users
SET profile = JSON_INSERT(profile, '$.phone', '123-456-7890')
WHERE username = 'john.doe' AND JSON_EXTRACT(profile, '$.phone') IS NULL;

-- 添加用户的地址
UPDATE users
SET profile = JSON_INSERT(profile, '$.address', JSON_OBJECT('street', '123 Main St', 'city', 'Anytown'))
WHERE username = 'jane.smith' AND JSON_EXTRACT(profile, '$.address') IS NULL;

-- 添加用户的兴趣爱好
UPDATE users
SET profile = JSON_INSERT(profile, '$.interests', JSON_ARRAY('reading', 'hiking'))
WHERE username = 'john.doe' AND JSON_EXTRACT(profile, '$.interests') IS NULL;

SELECT * FROM users;

执行结果：

id | username    | profile
---|-------------|--------------------------------------------------------------------------------------------------------------------------------------------------
 1 | john.doe    | {"email": "[email protected]", "age": 30, "phone": "123-456-7890", "interests": ["reading", "hiking"]}
 2 | jane.smith  | {"email": "[email protected]", "address": {"street": "123 Main St", "city": "Anytown"}}

这个示例展示了如何使用 JSON_INSERT() 函数来管理用户资料，并根据需要添加新的属性。

8. JSON路径表达式详解

JSON路径表达式是访问JSON文档中特定元素的关键。以下是一些常用的路径表达式：

• $: 表示根节点。
• .key: 访问JSON对象中的键为key的属性。
• [index]: 访问JSON数组中索引为index的元素。索引从0开始。
• [*]: 访问JSON数组中的所有元素。 (通常与JSON_EXTRACT()结合使用)
• **.key: 递归地访问所有名为 key 的属性，无论其嵌套深度如何。(MySQL 8.0.17 及更高版本支持)

例如，对于以下JSON文档：

{
  "name": "Product X",
  "details": {
    "price": 199.99,
    "colors": ["red", "blue", "green"]
  }
}

• $.name: 访问产品名称，返回 "Product X"。
• $.details.price: 访问产品价格，返回 199.99。
• $.details.colors[0]: 访问第一个颜色，返回 "red"。
• $.details.colors[*]: 访问所有颜色，返回 ["red", "blue", "green"]。(需要配合 JSON_EXTRACT 使用才能返回数组，直接在JSON_INSERT中插入会变成字符串)

9. 使用场景总结

JSON_INSERT() 在以下场景中特别有用：

• 动态配置管理: 存储应用程序的配置信息，并根据需要动态添加新的配置项。
• 用户资料管理: 存储用户资料，并根据需要添加新的属性，例如电话号码、地址、兴趣爱好等。
• 日志记录: 存储日志信息，并根据需要添加新的字段，例如请求ID、响应时间等。
• 数据集成: 将来自不同来源的数据集成到单个 JSON 文档中。
• 灵活的数据模型: 在无需修改数据库schema的情况下，增加新的数据属性。

理解和熟练运用 JSON路径表达式

通过学习本讲座，我们了解了 JSON_INSERT() 函数的语法、工作原理、使用场景和注意事项。 掌握了 JSON_INSERT() 函数，可以更加灵活和高效地处理 JSON 数据，从而构建更加强大的应用程序。