MySQL中JSON操作详解 JSON字段在查询中的处理方法-mysql教程-PHP中文网

MySQL中JSON操作详解 JSON字段在查询中的处理方法

冰火之心

发布： 2025-06-10 12:03:01

原创

960人浏览过

mysql中查询json类型字段的关键在于掌握json函数和优化技巧。1. json字段以二进制格式存储，插入时使用json字符串；2. 使用json_extract()提取键值，路径表达式如'$.age'，结合->和->>操作符简化写法；3. 在where条件中查询json字段时性能较低，建议通过生成列配合索引优化查询效率；4. 查询包含数组的json字段可用json_contains()函数判断是否存在指定值，注意参数需为带引号的字符串。合理运用这些方法可实现高效查询。

MySQL中JSON操作详解 JSON字段在查询中的处理方法

在MySQL中，JSON类型字段的引入让结构化数据和非结构化数据可以在同一个表里共存。尤其在处理一些配置项、扩展信息或动态数据时，JSON字段非常实用。但很多人用的时候会卡在“怎么查”这个问题上，其实只要掌握几个关键函数和使用技巧，就能轻松应对大部分查询场景。

1. JSON字段的基本结构与存储方式

MySQL中的JSON类型字段并不是简单的字符串，它内部是经过解析的二进制格式，这意味着你在插入或更新时写的是文本形式的JSON字符串，但数据库内部会自动转换成可高效访问的结构。

举个例子：

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

登录后复制

插入一条记录：

INSERT INTO users (id, info) VALUES (1, '{"name": "Tom", "age": 25, "hobbies": ["reading", "gaming"]}');

登录后复制

这时候info字段就保存了一个完整的JSON对象，你可以通过内置函数来提取其中的内容。

2. 提取JSON字段中的值：常用函数介绍

要在查询中使用JSON字段里的内容，最常用的函数是 JSON_EXTRACT()，它的作用是从JSON对象中提取某个键的值。

比如想查出所有年龄大于20岁的用户：

SELECT * FROM users WHERE JSON_EXTRACT(info, '$.age') > 20;

登录后复制

还可以结合别名使用：

SELECT id, JSON_EXTRACT(info, '$.name') AS name FROM users;

登录后复制

注意：$.age 是JSON路径表达式，表示根层级下的age字段。如果是嵌套结构，比如"address":{"city":"Beijing"}，路径就是'$.address.city'。

除了提取，还有一些辅助函数也很有用：

JSON_UNQUOTE()：去掉引号，让结果更干净
-> 操作符（等价于 JSON_EXTRACT）
->> 操作符（等价于 JSON_UNQUOTE(JSON_EXTRACT())）

比如：

SELECT id, info->'$.name' AS name, info->>'$.name' AS clean_name FROM users;

登录后复制

3. 在WHERE条件中使用JSON字段的注意事项

虽然可以用JSON字段做查询，但在性能上有一些限制。因为JSON字段本身不是索引友好的，直接对它进行查询可能效率不高。

如果你经常需要根据某个JSON字段的值做筛选，建议你配合生成列（Generated Column） 和索引来优化。

比如你想根据用户的name频繁查询：

ALTER TABLE users ADD COLUMN name VARCHAR(100) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.name'))) STORED;
CREATE INDEX idx_name ON users(name);

登录后复制

这样之后就可以像普通字段一样查询了：

SELECT * FROM users WHERE name = 'Tom';

登录后复制

这种方式在大数据量下性能提升非常明显。

4. 查询包含数组类型的JSON字段

有时候JSON字段中会包含数组，比如前面例子中的hobbies字段是一个字符串数组。

如果你想查找“爱好里有gaming”的用户，可以这样做：

SELECT * FROM users WHERE JSON_CONTAINS(info->'$.hobbies', '"gaming"');

登录后复制

注意几点：

JSON_CONTAINS 的第二个参数要写成带引号的字符串（即'"gaming"'），否则会匹配失败。
如果是数字，也是一样，例如'"25"'。

如果你要判断是否包含多个值中的任意一个，可以配合子查询或者IN语句使用。

基本上就这些。JSON字段在MySQL中使用起来灵活方便，但也容易因为不了解其特性而写出低效的查询。掌握几个核心函数、理解路径语法，并合理利用生成列和索引，就能让它既好用又高效。

以上就是MySQL中JSON操作详解 JSON字段在查询中的处理方法的详细内容，更多请关注php中文网其它相关文章！