MySQL JSON_SEARCH 怎样按通配符找到 JSON 字符串路径

MySQL 里想从 JSON 文档中找出“值在哪个路径”，可以用 JSON_SEARCH()。它搜索的是 JSON 文档里的字符串值，返回匹配值所在的路径；把 one 换成 all，就能从单一路径切换为全部不重复路径。搜索字符串里的 % 匹配任意长度字符，_ 匹配一个字符；如果要查字面量的百分号或下划线，再使用固定的转义字符。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

排查 JSON_SEARCH 返回 NULL 时，先确认文档和搜索值不是 NULL，再检查 path 范围、字符串类型以及 %、_ 是否被当成了通配符。

先分清 one 和 all：返回值不是同一种形态

我处理这类字段时，会先决定调用方只需要一个位置，还是要把所有位置都拿回来。函数的基本形式是 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])。one 找到一个匹配后就结束，返回一个路径字符串；all 返回所有不重复路径，多个结果会自动包装成数组。官方文档没有保证“第一个”匹配的选择，也没有保证数组中路径的顺序，所以不要把它当成稳定排序接口。

-- 构造一个包含重复关键词的文档，观察 one 与 all 的返回契约
SET @catalog = JSON_OBJECT(
    'items', JSON_ARRAY(
        JSON_OBJECT('name', '蓝色笔记本', 'tag', '办公'),
        JSON_OBJECT('name', '蓝色文件夹', 'tag', '办公')
    ),
    'featured', JSON_OBJECT('name', '蓝色笔记本')
);

SELECT
    JSON_SEARCH(@catalog, 'one', '蓝色笔记本') AS first_path,
    JSON_SEARCH(@catalog, 'all', '蓝色笔记本') AS all_paths;

这段查询的重点不是记住某个具体路径顺序，而是识别返回类型：first_path 是一个 JSON 字符串路径，all_paths 可能是包含多个路径的 JSON 数组。若业务只要判断“有没有”，可以在应用层按 NULL 与非 NULL 处理；若要继续读取每个位置，则应把数组当 JSON 结果处理，不要直接按逗号切字符串。

通配符、转义字符和搜索范围

search_str 的匹配规则和 LIKE 类似：% 可以匹配零个或多个字符，_ 只匹配一个字符。它们只作用于待搜索的字符串模式，不是 JSON path 里的层级通配符。要缩小搜索范围，可以把一个或多个 JSON path 放到参数末尾，例如只搜索数组元素中的 name 字段。

-- 只搜索 items 数组中的 name 字段，并分别演示 % 与 _
SET @catalog = JSON_OBJECT(
    'items', JSON_ARRAY(
        JSON_OBJECT('name', 'blue-notebook'),
        JSON_OBJECT('name', 'blue-folder'),
        JSON_OBJECT('name', 'red-notebook')
    ),
    'note', 'blue-notebook'
);

SELECT
    JSON_SEARCH(@catalog, 'all', 'blue%', NULL, '$.items[*].name') AS blue_names,
    JSON_SEARCH(@catalog, 'all', 'red-_______', NULL, '$.items[*].name') AS red_name;

第一条模式中的 blue% 能覆盖以 blue 开头的多个字符串，第二条里的下划线数量决定了后续字符数。末尾的 $.items[*].name 让搜索不再扫描根部的 note 字段；如果 path 本身写错，函数会报路径表达式错误，而不是简单返回“没有匹配”。

如果要查一个真正的百分号或下划线，必须先约定转义字符。例如数据值是 release_2026%，可以使用反斜杠把两个符号按字面匹配。默认转义字符是反斜杠；显式传入的 escape_char 必须是编译期常量，并且只能是空字符串或单个字符。把预处理语句的 ? 当作动态转义字符传入，可能在执行时有值，但不满足这个编译期常量要求。

-- 用反斜杠保护字面量的 _ 和 %；SQL 字符串中的反斜杠按当前 SQL mode 处理
SET @release_doc = JSON_OBJECT(
    'version', 'release_2026%',
    'channel', 'release_2026a'
);

SELECT JSON_SEARCH(
    @release_doc,
    'all',
    'release\\_2026\\%',
    '\\',
    '$'
) AS literal_path;

这里的意图是把 _ 和 % 当作普通字符，而不是让它们继续展开成模式。SQL 字符串转义和 JSON_SEARCH 的模式转义是两层规则；在客户端或预处理语句中拼接反斜杠时，要先确认最终送到 MySQL 的字符串内容。

返回 NULL 时按四层边界排查

JSON_SEARCH() 返回 NULL 不等于“SQL 语法一定错了”。官方语义包括：json_doc、search_str 或 path 参数为 NULL；path 在文档中不存在；搜索字符串没有找到。相反，JSON 文档无效、path 表达式无效、one_or_all 不是 one 或 all，通常会直接报错。

还要记住它匹配的是字符串值。数字、布尔值或对象不能因为“看起来像文本”就按同一规则命中；如果业务要按类型和值筛选，需要另选 JSON 提取或表结构设计。本文讨论的是定位路径，不是把 JSON_SEARCH 当作索引方案，也不代表给 JSON_SEARCH 加上通配符后就会自动获得索引性能。

我会怎样把 JSON_SEARCH 放进应用代码

在接口层，我会先固定搜索范围，再决定是否需要全部路径：只展示一个提示位置时使用 one，需要批量修正或审计时使用 all。如果搜索值来自用户输入，则应把输入当作数据参数处理，并明确是否允许用户使用 %、_ 这样的模式字符；如果业务只允许精确匹配，就要做字面量转义，而不是直接拼到搜索模式中。

最后再强调一次结果契约：one 的路径选择不保证顺序，all 的数组顺序也不保证稳定。要把路径写回 JSON、记录审计或生成可重复的接口响应时，先在应用层解析数组并自行排序；不要用一次返回的排列顺序推断文档内部的业务优先级。

JSON_SEARCH 能直接查数字吗？

它用于搜索字符串值。数字、布尔值或对象应使用更匹配其类型的 JSON 查询方式，不要把数字先强行拼成字符串再依赖模糊匹配。

为什么 JSON_SEARCH 返回 NULL 而 JSON 文档里明明有值？

常见原因是 path 把搜索范围限定错了、值不是字符串，或者 %、_ 的含义与预期不同。先移除可选 path，再用精确字符串逐层缩小范围。

all 返回的路径数组可以按下标直接使用吗？

可以读取数组，但不要把数组下标当作稳定业务顺序。官方不保证 all 结果数组的元素顺序，需要稳定输出时由应用层重新排序。

参考：MySQL 8.4 JSON 搜索函数文档。