在 MySQL 中，JSON 类型是专门为存储和操作 JSON 数据设计的一种数据类型。以下是对 MySQL 中 JSON 类型及相关操作的总结：

1. 什么是 MySQL 中的 JSON 类型？

• 引入版本：MySQL 5.7 开始支持 JSON 数据类型。
• 特性：
  • 存储的 JSON 数据会被自动验证，确保符合 JSON 格式。
  • 数据存储为二进制格式，提供高效的存储和检索。
  • 支持 JSON 文档的部分更新，减少数据传输开销。
  • 提供丰富的 JSON 函数，用于查询和修改 JSON 数据。

2. 创建包含 JSON 类型的表

示例表创建

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

插入 JSON 数据

INSERT INTO example (data) VALUES
('{"name": "Alice", "age": 25}'),
('{"name": "Bob", "age": 30}');

查看数据

SELECT * FROM example;

3. JSON 数据的查询操作

查询 JSON 字段中的特定值

• 使用 ->> 提取 JSON 值为字符串：

  SELECT data->>'$.name' AS name FROM example;
  

• 使用 -> 提取 JSON 值为 JSON 格式：

  SELECT data->'$.age' AS age FROM example;
  

筛选 JSON 字段

• 按照 JSON 字段的值过滤数据：

  SELECT * FROM example WHERE JSON_EXTRACT(data, '$.age') > 25;
  

  或者使用箭头操作符：

  SELECT * FROM example WHERE data->'$.age' > 25;
  

判断 JSON 键是否存在

• 检查某个键是否存在：

  SELECT * FROM example WHERE JSON_CONTAINS_PATH(data, 'one', '$.name');
  

4. 修改 JSON 数据

更新 JSON 字段中的某个键

使用 JSON_SET 函数更新 JSON 数据中的键值：

UPDATE example
SET data = JSON_SET(data, '$.age', 35)
WHERE JSON_EXTRACT(data, '$.name') = 'Alice';

删除 JSON 字段中的某个键

使用 JSON_REMOVE 函数删除键：

UPDATE example
SET data = JSON_REMOVE(data, '$.age')
WHERE JSON_EXTRACT(data, '$.name') = 'Bob';

插入新的键值对

使用 JSON_INSERT 函数插入新键值（如果键不存在）：

UPDATE example
SET data = JSON_INSERT(data, '$.city', 'New York')
WHERE JSON_EXTRACT(data, '$.name') = 'Alice';

替换键值

使用 JSON_REPLACE 替换键值（如果键存在）：

UPDATE example
SET data = JSON_REPLACE(data, '$.age', 40)
WHERE JSON_EXTRACT(data, '$.name') = 'Alice';

5. JSON 函数总结

检索函数

• JSON_EXTRACT(json_doc, path)：提取 JSON 中的值。
• -> 和 ->>：快捷操作符，用于提取 JSON 数据。

验证函数

• JSON_VALID(json_doc)：验证是否为有效 JSON。

修改函数

• JSON_SET(json_doc, path, value)：设置键值（覆盖或新增）。
• JSON_INSERT(json_doc, path, value)：插入键值（键不存在时新增）。
• JSON_REPLACE(json_doc, path, value)：替换键值（键存在时替换）。
• JSON_REMOVE(json_doc, path)：删除 JSON 中的键。

搜索和过滤

• JSON_CONTAINS(json_doc, value, path)：检查 JSON 是否包含指定值。
• JSON_CONTAINS_PATH(json_doc, one_or_all, path)：检查 JSON 是否包含指定路径。

6. 注意事项

1. 索引支持：

   • 可以对 JSON 中的特定字段创建虚拟列，并对虚拟列建立索引，以提高查询性能。

     ALTER TABLE example
     ADD COLUMN age INT GENERATED ALWAYS AS (JSON_EXTRACT(data, '$.age')) STORED,
     ADD INDEX (age);
     

2. 存储大小：

   • JSON 数据类型存储效率高，但会占用更多的空间用于维护其二进制格式。
     

3. 使用场景：

   • JSON 数据类型适用于需要灵活存储结构化数据的场景，如配置数据、日志信息等。

通过这些方法，MySQL 中的 JSON 数据类型可以高效地存储、查询和修改 JSON 格式的数据，满足现代应用对灵活数据模型的需求。