mysql json_set|极客笔记

mysql json_set

在MySQL中，有时候我们需要操作JSON数据类型的字段，比如添加、更新或删除JSON对象中的键值对。JSON_SET() 函数可以用来实现这些操作，它允许我们向现有JSON对象添加新的键值对，更新现有键的值，以及删除现有键值对。

语法

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

json_doc：要操作的JSON文档。
path：要更新的键的路径。可以是一个路径或者是一个路径数组。
val：要设置的值或者键值对。如果是键值对，可以是JSON对象或者其他标量值。

示例

假设我们有一个名为people的表，其中有一个名为info的JSON类型字段，存储了每个人的信息，格式如下：

CREATE TABLE people (
    id INT PRIMARY KEY,
    info JSON
);

INSERT INTO people (id, info) VALUES 
(1, '{"name": "Alice", "age": 25, "city": "New York"}'),
(2, '{"name": "Bob", "age": 30, "city": "Los Angeles"}'),
(3, '{"name": "Charlie", "age": 35, "city": "Chicago"}');

现在，假设我们想要更新Alice的信息，将age更新为26，并添加一个新的字段email，可以使用JSON_SET()函数来实现：

UPDATE people
SET info = JSON_SET(info, ' $.age', 26, '$ .email', 'alice@example.com')
WHERE id = 1;

运行上面的语句后，Alice的信息会被更新，age变为26，并且email字段被添加。查询people表可以查看更新后的结果：

SELECT * FROM people;

结果如下：

| id | info                                                                           |
|----|--------------------------------------------------------------------------------|
| 1  | {"name": "Alice", "age": 26, "city": "New York", "email": "alice@example.com"} |
| 2  | {"name": "Bob", "age": 30, "city": "Los Angeles"}                              |
| 3  | {"name": "Charlie", "age": 35, "city": "Chicago"}                             |

除了更新已有的键值对外，JSON_SET()函数还可以用来添加新的键值对，比如给所有人的信息中都添加一个phone字段：

UPDATE people
SET info = JSON_SET(info, '$.phone', '123-456-7890');

查询people表可以查看更新后的结果：

SELECT * FROM people;

结果如下：

| id | info                                                                           |
|----|--------------------------------------------------------------------------------|
| 1  | {"name": "Alice", "age": 26, "city": "New York", "email": "alice@example.com", "phone": "123-456-7890"} |
| 2  | {"name": "Bob", "age": 30, "city": "Los Angeles", "phone": "123-456-7890"}    |
| 3  | {"name": "Charlie", "age": 35, "city": "Chicago", "phone": "123-456-7890"}    |

此外，JSON_SET()函数还可以用来更新嵌套的JSON对象，比如给Alice的信息中的address字段添加street和zip：

UPDATE people
SET info = JSON_SET(info, '$.address', JSON_OBJECT('street', '123 Main St', 'zip', '10001'))
WHERE id = 1;

查询people表可以查看更新后的结果：

SELECT * FROM people;

结果如下：

| id | info                                                                                                       |
|----|------------------------------------------------------------------------------------------------------------|
| 1  | {"name": "Alice", "age": 26, "city": "New York", "email": "alice@example.com", "phone": "123-456-7890", "address": {"street": "123 Main St", "zip": "10001"}} |
| 2  | {"name": "Bob", "age": 30, "city": "Los Angeles", "phone": "123-456-7890"}                                  |
| 3  | {"name": "Charlie", "age": 35, "city": "Chicago", "phone": "123-456-7890"}                                  |