Mysql如何对json数据进行查询及修改

MySQL 5.7及以上版本支持JSON类型，可以对JSON数据进行查询和修改。下面将详细讲解MySQL如何对JSON数据进行查询和修改的攻略。

查询JSON数据

1. 使用->符号

使用->符号可以从JSON数据中提取指定字段的值。

语法：

SELECT json_column->'$.key' FROM table_name;

其中，json_column是JSON类型的列，key是需要提取的字段名，table_name是表名。

示例：从table1表中提取指定字段的值。

CREATE TABLE table1 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL
);

INSERT INTO table1 (data) VALUES
('{"name": "Tom", "age": 18, "gender": "male"}'),
('{"name": "Jim", "age": 20, "gender": "female"}'),
('{"name": "Lucy", "age": 21, "gender": "female"}');

SELECT data->'$.name', data->'$.age' FROM table1;

输出：

+------------+-----------+
| data->'$.name' | data->'$.age' |
+------------+-----------+
| "Tom"      | 18      |
| "Jim"      | 20      |
| "Lucy"     | 21      |
+------------+-----------+

2. 使用JSON_EXTRACT函数

JSON_EXTRACT函数可以从JSON数据中提取指定字段的值。

语法：

SELECT JSON_EXTRACT(json_column, '$.key') FROM table_name;

其中，json_column是JSON类型的列，key是需要提取的字段名，table_name是表名。

示例：从table1表中提取指定字段的值。

SELECT JSON_EXTRACT(data, '$.name'), JSON_EXTRACT(data, '$.age') FROM table1;

输出：

+--------------------------+------------------------+
| JSON_EXTRACT(data, '$.name') | JSON_EXTRACT(data, '$.age') |
+--------------------------+------------------------+
| "Tom"                    | 18                    |
| "Jim"                    | 20                    |
| "Lucy"                   | 21                    |
+--------------------------+------------------------+

修改JSON数据

1. 使用->符号

使用->符号可以修改JSON数据中指定字段的值。

语法：

UPDATE table_name SET json_column = JSON_SET(json_column, '$.key', value) WHERE condition;

其中，json_column是JSON类型的列，key是需要修改的字段名，value是要修改为的值，condition是修改的条件，table_name是表名。

示例：将table1表中年龄为18岁的人的性别修改为"female"。

UPDATE table1 SET data = JSON_SET(data, '$.gender', 'female') WHERE data->'$.age' = 18;

2. 使用JSON_SET函数

JSON_SET函数可以修改JSON数据中指定字段的值。

语法：

UPDATE table_name SET json_column = JSON_SET(json_column, '$.key', value) WHERE condition;

其中，json_column是JSON类型的列，key是需要修改的字段名，value是要修改为的值，condition是修改的条件，table_name是表名。

示例：将table1表中年龄为20岁的人的性别修改为"male"。

UPDATE table1 SET data = JSON_SET(data, '$.gender', 'male') WHERE JSON_EXTRACT(data, '$.age') = 20;

注意，使用JSON_SET函数时，需要指定新值的JSON格式，否则可能会报错。例如，我们需要修改名叫"Tom"的信息中的年龄和性别。

UPDATE table1 SET data = JSON_SET(data, '$.age', 20, '$.gender', 'male') WHERE JSON_EXTRACT(data, '$.name') = 'Tom';

这里需要为新值指定JSON格式：'$.age', 20, '$.gender', 'male'，其中逗号不可省略。

以上就是MySQL如何对JSON数据进行查询及修改的完整攻略。