MySQL中JSON数据类型怎么用_有哪些常用操作技巧？

MySQL从5.7版本开始支持JSON数据类型，适用于存储配置、标签等半结构化数据，其使用灵活且功能丰富。以下是具体用法和常用操作技巧：

1. 创建表时定义JSON列在创建表时，可直接将某列定义为JSON类型，用于存储结构不固定的数据。例如：

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

2. 插入合法JSON字符串插入数据时需传入符合JSON格式的字符串，包括键值对、数组等结构。例如：

INSERT INTO users (info) VALUES ('{"name": "张三", "age": 28, "tags": ["开发", "阅读"]}');

3. 提取JSON字段内容

4. 条件筛选JSON中的特定值

根据字段值筛选：使用JSON_EXTRACT或->操作符提取字段后比较。SELECT * FROM users WHERE JSON_EXTRACT(info, '$.age') > 30;-- 或简写为：SELECT * FROM users WHERE info->'$.age' > 30;
查询数组字段：使用JSON_CONTAINS检查数组是否包含特定值（注意字符串需用双引号包裹）。SELECT * FROM users WHERE JSON_CONTAINS(info->'$.tags', '"开发"');

5. 修改JSON字段内容

更新整个JSON：直接赋值新JSON字符串。UPDATE users SET info = '{"name": "李四", "age": 32}' WHERE id = 1;
部分更新字段：使用专用函数避免覆盖整个JSON。
JSON_SET：添加或更新字段（若字段不存在则添加）。UPDATE users SET info = JSON_SET(info, '$.address', '北京') WHERE id = 1;
JSON_REPLACE：仅替换已有字段（若字段不存在则不修改）。UPDATE users SET info = JSON_REPLACE(info, '$.name', '王五') WHERE id = 1;
JSON_REMOVE：删除指定字段。UPDATE users SET info = JSON_REMOVE(info, '$.age') WHERE id = 1;

6. 索引优化JSON查询性能JSON字段默认无法直接使用索引，需通过以下方式优化：

虚拟列（Generated Column）加索引：
创建虚拟列提取JSON中的字段值。ALTER TABLE users ADD COLUMN name VARCHAR(100) GENERATED ALWAYS AS (info->>'$.name') STORED;
为虚拟列创建索引。CREATE INDEX idx_name ON users(name);
MySQL 8.0+的JSON索引：直接对JSON字段中的路径值创建索引（语法较复杂，推荐优先使用虚拟列）。CREATE INDEX idx_age ON users((CAST(info->'$.age' AS UNSIGNED)));

7. 使用建议

通过以上技巧，可高效利用MySQL的JSON类型处理复杂数据场景，同时保证查询性能与数据一致性。

热门标签