如何查询MySQL中JSON字段ids数组包含指定字符串的记录

你之前的查询未生效是因为没有明确指定JSON结构中ids数组的路径，根路径下是包含ids属性的对象，不是字符串数组，直接全字段匹配无法命中目标值。

两种可用的正确查询方案 #

方案1：使用JSON_CONTAINS实现 #

-- 写法1：先提取ids数组再匹配
SELECT * FROM test 
WHERE JSON_CONTAINS(fragments->'$.ids', '"c9cfdecc-dfce-11eb-8c94-0ad691f61ea1"');

-- 写法2：指定路径+自动转义目标值（更稳妥，避免手动加引号出错）
SELECT * FROM test 
WHERE JSON_CONTAINS(fragments, JSON_QUOTE('c9cfdecc-dfce-11eb-8c94-0ad691f61ea1'), '$.ids');

说明：

• fragments->'$.ids'是MySQL提供的JSON字段提取简写，等价于JSON_EXTRACT(fragments, '$.ids')，用于直接取出ids数组做匹配
• JSON_QUOTE会自动将普通字符串转为合法的JSON字符串格式，不需要手动包裹双引号

方案2：使用JSON_SEARCH实现 #

SELECT * FROM test 
WHERE JSON_SEARCH(fragments, 'one', 'c9cfdecc-dfce-11eb-8c94-0ad691f61ea1', NULL, '$.ids') IS NOT NULL;

说明：

• JSON_SEARCH匹配成功会返回对应元素的路径，匹配失败返回NULL，因此需要加IS NOT NULL判断才能作为筛选条件
• 最后一个参数'$.ids'指定仅在ids数组路径下搜索，相比全JSON字段搜索性能更高

性能建议 #

如果表数据量较大，建议为fragments->'$.ids'创建JSON专用索引，或者将ids数组的内容拆分到单独的关联表存储，查询效率会有明显提升。

内容的提问来源于stack exchange，提问作者Gowri