【MySQL】MySQL中的函数之JSON_REPLACE

在 MySQL 中，JSON_REPLACE() 函数用于在 JSON 文档中替换现有的值。如果指定的路径不存在，则 JSON_REPLACE() 不会修改 JSON 文档。如果需要添加新的键值对，可以使用 JSON_SET() 函数。

基本语法

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

• json_doc: 要修改的 JSON 文档。
• path: 要替换的值的路径。
• val: 要设置的新值。

示例

假设有一个表 users，其中有一个字段 data 存储了用户的 JSON 数据：

CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO users (id, data) VALUES
(1, '{"name": "Alice", "age": 30, "city": "New York"}'),
(2, '{"name": "Bob", "age": 25, "city": "Los Angeles"}');

示例 1: 替换单个值

假设我们要将 id 为 1 的用户的年龄从 30 改为 35：

UPDATE users
SET data = JSON_REPLACE(data, '$.age', 35)
WHERE id = 1;

查询结果：

SELECT * FROM users;

输出结果：

+----+--------------------------------------------------+
| id | data                                             |
+----+--------------------------------------------------+
| 1  | {"name": "Alice", "age": 35, "city": "New York"} |
| 2  | {"name": "Bob", "age": 25, "city": "Los Angeles"}|
+----+--------------------------------------------------+

示例 2: 替换多个值

假设我们要将 id 为 2 的用户的年龄从 25 改为 30，并将城市从 “Los Angeles” 改为 “San Francisco”：

UPDATE users
SET data = JSON_REPLACE(data, '$.age', 30, '$.city', 'San Francisco')
WHERE id = 2;

查询结果：

SELECT * FROM users;

输出结果：

+----+-----------------------------------------------------------+
| id | data                                                      |
+----+-----------------------------------------------------------+
| 1  | {"name": "Alice", "age": 35, "city": "New York"}          |
| 2  | {"name": "Bob", "age": 30, "city": "San Francisco"}       |
+----+-----------------------------------------------------------+

示例 3: 替换嵌套值

假设 JSON 数据中包含嵌套的对象：

INSERT INTO users (id, data) VALUES
(3, '{"name": "Charlie", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}');

我们可以替换嵌套对象 address 中的 street：

UPDATE users
SET data = JSON_REPLACE(data, '$.address.street', '456 Elm St')
WHERE id = 3;

查询结果：

SELECT * FROM users;

输出结果：

+----+-----------------------------------------------------------------+
| id | data                                                            |
+----+-----------------------------------------------------------------+
| 1  | {"name": "Alice", "age": 35, "city": "New York"}                |
| 2  | {"name": "Bob", "age": 30, "city": "San Francisco"}             |
| 3  | {"name": "Charlie", "age": 35, "address": {"street": "456 Elm St", "city": "Chicago"}} |
+----+-----------------------------------------------------------------+

示例 4: 替换数组中的值

假设 JSON 数据中包含一个数组：

INSERT INTO users (id, data) VALUES
(4, '{"name": "David", "age": 40, "hobbies": ["reading", "traveling", "cooking"]}');

我们可以替换数组 hobbies 中的第一个值：

UPDATE users
SET data = JSON_REPLACE(data, '$.hobbies[0]', 'writing')
WHERE id = 4;

查询结果：

SELECT * FROM users;

输出结果：

+----+-----------------------------------------------------------------------+
| id | data                                                                  |
+----+-----------------------------------------------------------------------+
| 1  | {"name": "Alice", "age": 35, "city": "New York"}                      |
| 2  | {"name": "Bob", "age": 30, "city": "San Francisco"}                   |
| 3  | {"name": "Charlie", "age": 35, "address": {"street": "456 Elm St", "city": "Chicago"}} |
| 4  | {"name": "David", "age": 40, "hobbies": ["writing", "traveling", "cooking"]} |
+----+-----------------------------------------------------------------------+

注意事项

• 如果指定的路径不存在，JSON_REPLACE() 不会修改 JSON 文档。
• 如果需要添加新的键值对，应使用 JSON_SET() 函数。
• JSON_REPLACE() 只能替换现有路径的值，不能创建新路径。

组合使用

JSON_REPLACE() 通常与其他 JSON 函数结合使用，以便更灵活地处理 JSON 数据。例如，你可以先使用 JSON_EXTRACT() 提取值，然后使用 JSON_REPLACE() 修改值。