MySQL JSON type字段的使用

在 MySQL 里，JSON 字段类型用于存储 JSON（JavaScript Object Notation）数据。JSON 数据属于半结构化数据，其形式为键值对，并且支持嵌套结构。下面为你详细介绍 MySQL 的 JSON 字段类型。

基本特性

• 存储格式：JSON 字段类型能够存储合法的 JSON 数据，像对象、数组、字符串、数字、布尔值以及 null 等。
• 自动验证：在插入或者更新数据时，MySQL 会自动验证 JSON 数据的格式，若格式不合法则会报错。
• 高效访问：MySQL 提供了一系列函数和操作符，可用于高效地访问和操作 JSON 数据。

示例操作

1. 创建包含 JSON 字段的表

sql

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    contact_info JSON
);

在这个示例中，contact_info 字段就是 JSON 类型，可用于存储员工的联系信息，如邮箱、电话等。

2. 插入 JSON 数据

INSERT INTO employees (name, contact_info)
VALUES (
    'John Doe',
    '{"email": "john.doe@example.com", "phone": "123-456-7890"}'
);

3. 查询 JSON 数据

• 访问顶层键：

SELECT name, contact_info -> '$.email' AS email
FROM employees;

• 访问嵌套键：假设 contact_info 是嵌套的 JSON 对象，你可以这样访问嵌套键：

-- 假设 contact_info 包含一个嵌套的 address 对象
INSERT INTO employees (name, contact_info)
VALUES (
    'Jane Smith',
    '{"email": "jane.smith@example.com", "address": {"city": "New York", "street": "123 Main St"}}'
);

SELECT name, contact_info -> '$.address.city' AS city
FROM employees;

4. 更新 JSON 数据

UPDATE employees
SET contact_info = JSON_SET(contact_info, '$.phone', '098-765-4321')
WHERE name = 'John Doe';

此操作会把 John Doe 的电话号码更新为 098-765-4321。

5. 删除 JSON 数据中的键

UPDATE employees
SET contact_info = JSON_REMOVE(contact_info, '$.email')
WHERE name = 'John Doe';

该操作会把 John Doe 的邮箱信息从 contact_info 中移除。

注意事项

• 性能：虽然 MySQL 对 JSON 数据提供了较好的支持，不过在处理大规模 JSON 数据时，查询性能可能会受到影响。建议对经常查询的 JSON 键建立虚拟列和索引。
• 兼容性：JSON 字段类型在 MySQL 5.7.8 及更高版本中可用。

通过上述介绍，你可以在 MySQL 中使用 JSON 字段类型来存储和操作半结构化数据。