MariaDB 10.4如何搜索全表JSON列中的指定属性值

核心实现思路 #

你的表中items字段的JSON结构为动态数字键作为顶层slot标识，每个键对应独立的物品对象，包含name、label等属性。MariaDB 10.4 完整支持JSON路径通配符匹配，可以直接通过内置JSON函数实现全字段、全跨行的属性检索，不需要额外解析数据。

可直接运行的查询语句 #

使用JSON_SEARCH函数，通过$.*通配符匹配所有顶层slot下的对象，定向检索name或label属性：

-- 精确匹配：检索存在name="diving_gear" 或 label="Bag of Crack" 的所有记录
SELECT * FROM stashitems 
WHERE 
  JSON_SEARCH(items, 'one', 'diving_gear', NULL, '$.*.name') IS NOT NULL
  OR JSON_SEARCH(items, 'one', 'Bag of Crack', NULL, '$.*.label') IS NOT NULL;

参数说明 #

• 第二个参数填'one'：找到第一个匹配项就终止当前行的JSON遍历，比遍历所有匹配项的'all'模式性能更好
• 第四个参数填NULL：使用默认的字符串比较规则，区分大小写、无自定义转义
• 最后一个路径参数：$.*.name表示匹配根节点下任意键对应对象的name属性，$.*.label同理

常用变体 #

1. 模糊匹配（比如检索label中包含Crack关键词的记录）：

SELECT * FROM stashitems 
WHERE JSON_SEARCH(items, 'one', '%Crack%', NULL, '$.*.label') IS NOT NULL;

2. 搜索值包含%/_等通配符时，指定转义符避免误匹配（比如搜索name为glass_bottle）：

SELECT * FROM stashitems 
WHERE JSON_SEARCH(items, 'one', 'glass\_bottle', '\\', '$.*.name') IS NOT NULL;

性能优化建议 #

当前全表仅1500行，上述语句无性能压力，可直接使用。如果后续数据量增长到10万行以上，可以通过生成列+全文索引提速：

-- 新增存储型生成列，自动聚合所有物品的name、label属性为可检索文本
ALTER TABLE stashitems 
ADD COLUMN item_search_text TEXT 
AS (CONCAT_WS(' ', JSON_UNQUOTE(JSON_EXTRACT(items, '$.*.name')), JSON_UNQUOTE(JSON_EXTRACT(items, '$.*.label')))) 
STORED;

-- 给生成列加全文索引
CREATE FULLTEXT INDEX idx_stashitems_search ON stashitems(item_search_text);

优化后用全文检索查询，性能可提升数十倍：

SELECT * FROM stashitems 
WHERE MATCH(item_search_text) AGAINST ('diving_gear' IN BOOLEAN MODE);

避坑提示 #

不要直接用LIKE '%关键词%'匹配items列的原始文本，会出现两类问题：

• 误匹配：关键词出现在image/info等非目标属性时会返回错误结果
• 漏匹配：JSON内存在转义字符、属性值带多余空格时会匹配失败
  使用JSON函数是定向匹配目标属性，不会出现上述问题。如果你的items列是TEXT/LONGTEXT类型而非原生JSON类型，只要存储内容是合法JSON格式，上述所有语句都可以正常运行。

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