如何高效筛选含多关键词的产品？PHP+MySQL场景咨询

核心结论 #

优先使用MySQL原生JSON函数处理关键词筛选，而非PHP的array_filter或多LIKE语句，前者在效率、准确性上更优。

为什么不推荐PHP array_filter？ #

• 需要先从数据库拉取所有符合Product_Brand_ID IN (1,2)的数据，再在PHP内存中过滤关键词，数据量较大时会显著增加网络传输成本和内存占用，效率极低。
• 无法利用数据库索引优化查询，完全依赖后端处理，扩展性差。

为什么不推荐多LIKE语句？ #

• 用LIKE '%"test2"%' AND LIKE '%"test9"%'会存在误匹配风险：比如关键词"test22"会被LIKE '%"test2"%'命中，但实际不是目标关键词。
• 同样无法利用索引，且匹配逻辑不够精确。

最优方案：MySQL JSON_CONTAINS函数 #

MySQL 5.7及以上版本支持JSON字段操作，JSON_CONTAINS可以精确匹配JSON数组中的元素，结合IN语句即可实现需求：

SELECT * 
FROM products
WHERE 
  Product_Brand_ID IN (1, 2)
  AND JSON_CONTAINS(Product_keyword, '"test2"', '$.keywords')
  AND JSON_CONTAINS(Product_keyword, '"test9"', '$.keywords');

语法说明 #

• JSON_CONTAINS(target, candidate, path)：检查target指定的JSON数据是否包含candidate值，path指定JSON路径（这里$.keywords对应字段中存储的keywords数组）。
• 注意candidate需要用双引号包裹，因为JSON字符串必须以双引号表示。

性能优化建议 #

1. 给Product_Brand_ID加索引：

   CREATE INDEX idx_product_brand_id ON products(Product_Brand_ID);
   

2. 针对JSON数组创建虚拟列并加索引（如果关键词查询频繁）：

   -- 生成虚拟列，提取keywords数组
   ALTER TABLE products 
   ADD COLUMN product_keywords_arr JSON 
   GENERATED ALWAYS AS (Product_keyword->'$.keywords') VIRTUAL;
   
   -- 给虚拟列创建索引
   CREATE INDEX idx_product_keywords_arr ON products((CAST(product_keywords_arr AS JSON)));
   

   之后查询可以直接用虚拟列，进一步提升效率：

   SELECT * 
   FROM products
   WHERE 
     Product_Brand_ID IN (1, 2)
     AND JSON_CONTAINS(product_keywords_arr, '"test2"')
     AND JSON_CONTAINS(product_keywords_arr, '"test9"');
   

方案对比总结 #

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