MySQL查询JSON对象数组列时JSON_CONTAINS语法错误排查

1. JSON字符串转义错误：在MySQL中，用单引号包裹字符串时，字符串内部的单引号需要用两个连续单引号''转义，而非\'。你写的\'会导致MySQL解析字符串时出现异常，生成不符合格式的JSON，触发3141错误。
2. JSON_CONTAINS用法不符合实际需求：即使转义正确，JSON_CONTAINS(contract_details, '{"name":"xxx"}')会检查数组中是否存在完全匹配该对象的元素（即元素仅包含name字段且值为指定内容），但你的需求是只要数组中存在元素的name字段等于指定值即可，原写法无法满足这种场景。

方式1：使用JSON_SEARCH（简洁推荐） #

JSON_SEARCH可查找JSON中指定值的路径，只要返回结果非NULL，就说明存在匹配元素：

SELECT *
FROM contract
WHERE application_id = 'abcdef'
  AND JSON_SEARCH(contract_details, 'one', '~!@#$$%%^&*())_+?><,./:";\'{}||}{"') IS NOT NULL;

说明：'one'表示仅返回第一个匹配路径，若需返回所有匹配路径可改为'all'，此处只需判断是否存在，用'one'足够。

方式2：修复转义后使用JSON_CONTAINS（仅适用于完全匹配对象场景） #

如果确实需要匹配仅包含name字段且值为指定内容的对象，可修复转义后使用：

SELECT *
FROM contract
WHERE application_id = 'abcdef'
  AND JSON_CONTAINS(contract_details, '{"name":"~!@#$$%%^&*())_+?><,./:\";\'\'{}||}{"}');

这里将原语句中的\'替换为''，确保MySQL能正确解析字符串，生成合法JSON。

方式3：使用JSON_TABLE展开数组（适用于复杂查询） #

将JSON数组展开为行数据后再筛选，适合需要同时匹配多个字段等复杂场景：

SELECT DISTINCT c.*
FROM contract c
JOIN JSON_TABLE(
  c.contract_details,
  '$[*]' COLUMNS(name VARCHAR(255) PATH '$.name')
) jt ON jt.name = '~!@#$$%%^&*())_+?><,./:";\'{}||}{"'
WHERE c.application_id = 'abcdef';

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