MySQL函数：`JSON_INSERT()`、`JSON_REPLACE()`和`JSON_SET()`对 JSON 文档进行精确修改。

好的，我们开始今天的讲座，主题是MySQL中用于修改JSON文档的三个核心函数：JSON_INSERT()、JSON_REPLACE()和JSON_SET()。理解这些函数对于高效地操作存储在MySQL数据库中的JSON数据至关重要。

JSON数据类型简介

在深入研究这三个函数之前，我们先简单回顾一下MySQL的JSON数据类型。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，易于人阅读和编写，也易于机器解析和生成。MySQL 5.7.22及更高版本提供了原生的JSON数据类型，允许你在数据库中存储和操作JSON文档。

准备工作：创建示例表

为了演示这些函数，我们首先需要创建一个包含JSON列的表。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_data JSON
);

现在，我们插入一些示例数据：

INSERT INTO employees (employee_data) VALUES
('{"name": "Alice", "age": 30, "city": "New York"}'),
('{"name": "Bob", "age": 25, "city": "Los Angeles", "skills": ["Java", "Python"]}'),
('{"name": "Charlie", "age": 35}');

JSON_INSERT()：插入新数据

JSON_INSERT()函数用于在JSON文档中插入新的键值对，只有当指定的路径不存在时，才会插入新的数据。如果路径已存在，则JSON_INSERT()不会进行任何修改。

语法:

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

• json_doc: 要修改的JSON文档。
• path: 要插入数据的路径，使用JSON路径表达式。
• val: 要插入的值。

示例:

假设我们想向第一个员工（Alice）的信息中添加一个title字段。

SELECT JSON_INSERT(employee_data, '$.title', 'Software Engineer')
FROM employees
WHERE id = 1;

结果：

{"name": "Alice", "age": 30, "city": "New York", "title": "Software Engineer"}

现在，尝试再次插入title字段，使用相同的路径。

SELECT JSON_INSERT(employee_data, '$.title', 'Senior Engineer')
FROM employees
WHERE id = 1;

结果（保持不变）：

{"name": "Alice", "age": 30, "city": "New York", "title": "Software Engineer"}

因为$.title路径已经存在，所以JSON_INSERT()没有进行任何修改。

插入到数组中

JSON_INSERT()也可以用于向JSON数组中插入新的元素。 为了演示这个，我们先更新Bob的数据，添加一个projects数组：

UPDATE employees
SET employee_data = JSON_SET(employee_data, '$.projects', JSON_ARRAY('ProjectA', 'ProjectB'))
WHERE id = 2;

现在，向projects数组中插入一个新的项目：

SELECT JSON_INSERT(employee_data, '$.projects[2]', 'ProjectC')
FROM employees
WHERE id = 2;

结果：

{"name": "Bob", "age": 25, "city": "Los Angeles", "skills": ["Java", "Python"], "projects": ["ProjectA", "ProjectB", "ProjectC"]}

注意，$.projects[2]表示在数组索引2的位置插入，而不是替换。 如果索引超出了数组的长度，JSON_INSERT会添加NULL值直到到达指定的索引。

JSON_REPLACE()：替换现有数据

JSON_REPLACE()函数用于替换JSON文档中已存在的键值对。如果指定的路径不存在，则JSON_REPLACE()不会进行任何修改。

语法:

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

• json_doc: 要修改的JSON文档。
• path: 要替换数据的路径，使用JSON路径表达式。
• val: 要替换的值。

示例:

将Alice的年龄从30岁改为31岁。

SELECT JSON_REPLACE(employee_data, '$.age', 31)
FROM employees
WHERE id = 1;

结果：

{"name": "Alice", "age": 31, "city": "New York", "title": "Software Engineer"}

现在，尝试替换一个不存在的字段，例如$.department。

SELECT JSON_REPLACE(employee_data, '$.department', 'Engineering')
FROM employees
WHERE id = 1;

结果（保持不变）：

{"name": "Alice", "age": 31, "city": "New York", "title": "Software Engineer"}

由于$.department路径不存在，JSON_REPLACE()没有进行任何修改。

替换数组中的元素

SELECT JSON_REPLACE(employee_data, '$.projects[0]', 'ProjectAlpha')
FROM employees
WHERE id = 2;

结果：

{"name": "Bob", "age": 25, "city": "Los Angeles", "skills": ["Java", "Python"], "projects": ["ProjectAlpha", "ProjectB", "ProjectC"]}

JSON_REPLACE()成功地将projects数组中的第一个元素替换为"ProjectAlpha"。

JSON_SET()：插入或替换数据

JSON_SET()函数是一个更通用的函数，它可以插入新的键值对，也可以替换已存在的键值对。 如果指定的路径不存在，则插入新的数据；如果路径已存在，则替换现有数据。

语法:

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

• json_doc: 要修改的JSON文档。
• path: 要设置数据的路径，使用JSON路径表达式。
• val: 要设置的值。

示例:

使用JSON_SET()向Alice的信息中添加department字段。

SELECT JSON_SET(employee_data, '$.department', 'Engineering')
FROM employees
WHERE id = 1;

结果：

{"name": "Alice", "age": 31, "city": "New York", "title": "Software Engineer", "department": "Engineering"}

现在，使用JSON_SET()替换Alice的city字段。

SELECT JSON_SET(employee_data, '$.city', 'San Francisco')
FROM employees
WHERE id = 1;

结果：

{"name": "Alice", "age": 31, "city": "San Francisco", "title": "Software Engineer", "department": "Engineering"}

JSON_SET()既可以插入新的字段（如department），也可以替换现有的字段（如city）。

处理数组中的元素

JSON_SET 在处理数组时，行为与 JSON_INSERT 和 JSON_REPLACE 略有不同。 如果索引超出数组大小，JSON_SET 会用 NULL 填充直到指定的索引位置，并插入新的值。

SELECT JSON_SET(employee_data, '$.projects[5]', 'ProjectF')
FROM employees
WHERE id = 2;

结果：

{"name": "Bob", "age": 25, "city": "Los Angeles", "skills": ["Java", "Python"], "projects": ["ProjectAlpha", "ProjectB", "ProjectC", null, null, "ProjectF"]}

更新表中的JSON数据

以上示例都是查询，并没有真正修改数据库中的数据。 要更新表中的JSON数据，需要结合UPDATE语句使用这些函数。

例如，更新Alice的employee_data：

UPDATE employees
SET employee_data = JSON_SET(employee_data, '$.salary', 80000)
WHERE id = 1;

SELECT employee_data FROM employees WHERE id = 1;

结果：

{"name": "Alice", "age": 31, "city": "San Francisco", "title": "Software Engineer", "department": "Engineering", "salary": 80000}

JSON_ARRAY() 和 JSON_OBJECT() 的补充说明

在上面的例子中，我们使用了JSON_ARRAY()函数来创建一个JSON数组。 同样，JSON_OBJECT()函数可以用于创建一个JSON对象。

例如，创建一个包含name和value字段的JSON对象：

SELECT JSON_OBJECT('name', 'John', 'value', 42);

结果：

{"name": "John", "value": 42}

这两个函数在构建复杂的JSON结构时非常有用。

JSON路径表达式详解

JSON路径表达式是用于定位JSON文档中特定元素的字符串。 它以$符号开头，表示根节点。 可以使用.符号访问对象中的字段，使用[]符号访问数组中的元素。

以下是一些常见的JSON路径表达式：

• $.name: 访问根对象中的name字段。
• $.address.city: 访问根对象中address对象的city字段。
• $.skills[0]: 访问根对象中skills数组的第一个元素。
• $.projects[*] : 访问projects数组中的所有元素。 (MySQL 8.0.17 及更高版本支持)
• $**.city : 递归地查找所有名为 city 的字段 (MySQL 8.0.17 及更高版本支持)

了解JSON路径表达式对于有效地使用JSON_INSERT()、JSON_REPLACE()和JSON_SET()至关重要。

性能考虑

虽然JSON数据类型提供了灵活性，但在使用时也需要考虑性能。

• 索引: 可以对JSON列的部分内容创建索引，以提高查询性能。 MySQL支持虚拟列索引和JSON列索引。
• 数据大小: 大型JSON文档可能会影响查询性能。 尽量只存储必要的数据。
• 函数调用: 频繁地调用JSON函数可能会增加服务器的负载。 在可能的情况下，尽量减少函数调用次数。

总结对比：JSON_INSERT()、JSON_REPLACE() 和 JSON_SET()

为了更好地理解这三个函数之间的区别，下面是一个表格：

函数	行为	路径已存在	路径不存在
JSON_INSERT()	插入新的键值对，仅当路径不存在时。	无操作	插入数据
JSON_REPLACE()	替换已存在的键值对，仅当路径存在时。	替换数据	无操作
JSON_SET()	插入新的键值对或替换已存在的键值对。路径不存在则插入，存在则替换。	替换数据	插入数据

实际应用场景

• 存储用户配置信息: 将用户的个性化设置存储为JSON文档，便于灵活地修改和扩展。
• 存储日志数据: 将应用程序的日志信息存储为JSON文档，方便进行分析和查询。
• 存储产品属性: 将产品的各种属性存储为JSON文档，支持动态地添加和修改属性。
• 存储API响应数据: 将外部API的响应数据存储为JSON文档，便于进行数据处理和转换。

注意事项

• 确保MySQL版本支持JSON数据类型（5.7.22及更高版本）。
• JSON路径表达式的语法要正确，否则函数可能无法正常工作。
• 在更新JSON数据时，要谨慎操作，避免意外地修改或删除数据。
• 在处理大型JSON文档时，要考虑性能问题，并采取相应的优化措施。

深入理解与灵活运用

通过今天的讲座，我们深入了解了MySQL中用于修改JSON文档的三个核心函数：JSON_INSERT()、JSON_REPLACE()和JSON_SET()。理解它们的区别和用法，可以帮助你更高效地操作存储在MySQL数据库中的JSON数据。 掌握这些函数，你就能灵活地处理各种JSON数据操作。

函数特性与应用场景的结合

理解这三个函数如何修改 JSON 文档是第一步，更重要的是知道在什么情况下使用哪个函数。JSON_INSERT 用于确保不覆盖现有数据，JSON_REPLACE 用于确保只修改已知存在的数据，而 JSON_SET 则提供了最通用的方式，可以根据需要添加或修改数据。

代码示例的实践意义

文章中提供的代码示例不仅仅是演示，更是实际开发中可能遇到的问题的简化版本。通过运行这些示例，你可以更好地理解这些函数的工作原理，并能够将其应用到更复杂的场景中。