MySQL 5.7及以上版本开始支持JSON数据类型，为存储和操作JSON数据提供了便利。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，广泛应用于现代Web应用程序中。本文将详细介绍MySQL中JSON数据的存储结构与操作。
一、JSON数据类型的基本操作
1.1 创建包含JSON字段的表
首先，创建一个包含JSON字段的表。例如，创建一个存储用户信息的表，其中 details字段为JSON类型。
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),details JSON
);
1.2 插入JSON数据
插入数据时，可以直接插入JSON格式的数据。
INSERT INTO users (name, details) VALUES
('John Doe', '{"age": 30, "email": "john.doe@example.com", "address": {"city": "New York", "zip": "10001"}}'),
('Jane Smith', '{"age": 25, "email": "jane.smith@example.com", "address": {"city": "Los Angeles", "zip": "90001"}}');1.3 查询JSON数据
可以使用 ->>运算符提取JSON字段中的值。例如，查询所有用户的邮箱。
SELECT name, details->>'$.email' AS email FROM users;1.4 更新JSON数据
使用 JSON_SET函数可以更新JSON数据。例如，更新John Doe的城市为Boston。
UPDATE users SET details = JSON_SET(details, '$.address.city', 'Boston') WHERE name = 'John Doe';1.5 删除JSON数据
使用 JSON_REMOVE函数可以删除JSON数据中的某个键。例如，删除Jane Smith的地址信息。
UPDATE users SET details = JSON_REMOVE(details, '$.address') WHERE name = 'Jane Smith';
二、JSON函数与操作
2.1 JSON_EXTRACT
JSON_EXTRACT用于从JSON文档中提取数据。等价于 ->操作符。
SELECT JSON_EXTRACT(details, '$.email') AS email FROM users;
2.2 JSON_ARRAY
JSON_ARRAY用于创建JSON数组。
SELECT JSON_ARRAY('apple', 'banana', 'cherry') AS fruits;
2.3 JSON_OBJECT
JSON_OBJECT用于创建JSON对象。
SELECT JSON_OBJECT('name', 'John Doe', 'age', 30) AS person;
2.4 JSON_CONTAINS
JSON_CONTAINS用于检查JSON文档中是否包含某个值。
SELECT name FROM users WHERE JSON_CONTAINS(details, '{"city": "New York"}', '$.address');
2.5 JSON_MERGE
JSON_MERGE用于合并两个JSON文档。
SELECT JSON_MERGE('{"name": "John"}', '{"age": 30}') AS merged_json;
2.6 JSON_TYPE
JSON_TYPE返回JSON值的数据类型。
SELECT JSON_TYPE('{"name": "John"}') AS json_type;
三、JSON索引与优化
3.1 创建虚拟列与索引
为了提高查询性能，可以创建虚拟列并对其建立索引。例如，为用户表中的邮箱创建虚拟列和索引。
ALTER TABLE users ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (details->>'$.email') STORED;
CREATE INDEX idx_email ON users (email);
3.2 使用全文索引
对于包含大量文本的JSON字段，可以使用全文索引来提高查询性能。
ALTER TABLE users ADD FULLTEXT(details);
SELECT * FROM users WHERE MATCH(details) AGAINST('New York');