如何用MySQL和PHP实现多关键词按匹配词数优先级排序搜索？

针对你的需求，要严格按匹配关键词数量从多到少排序返回结果，可以用以下两种方法实现：

方法一：手动计算匹配关键词数量（无FULLTEXT索引也可用） #

如果你的商品表没有创建FULLTEXT索引，或者数据量较小，可直接通过字符串函数统计每个记录匹配的关键词数量，再按数量降序排序。

假设你的表名为products，字段为product_id和keywords，搜索关键词为laser、wedding、cake、box，SQL语句如下：

SELECT 
    product_id,
    keywords,
    (
        IF(INSTR(LOWER(keywords), 'laser') > 0, 1, 0) +
        IF(INSTR(LOWER(keywords), 'wedding') > 0, 1, 0) +
        IF(INSTR(LOWER(keywords), 'cake') > 0, 1, 0) +
        IF(INSTR(LOWER(keywords), 'box') > 0, 1, 0)
    ) AS match_count
FROM products
WHERE 
    INSTR(LOWER(keywords), 'laser') > 0 
    OR INSTR(LOWER(keywords), 'wedding') > 0 
    OR INSTR(LOWER(keywords), 'cake') > 0 
    OR INSTR(LOWER(keywords), 'box') > 0
ORDER BY match_count DESC;

• 用LOWER()统一转换为小写，避免大小写不匹配的问题；
• INSTR()函数判断关键词是否存在，存在则加1，最终match_count就是匹配的关键词总数；
• WHERE条件确保只返回至少匹配一个关键词的记录；
• 按match_count降序排序，就能实现先全匹配、再3个、2个、单个的顺序。

方法二：利用FULLTEXT索引（适合大数据量） #

如果你的商品数据量很大，建议先给keywords字段创建FULLTEXT索引，提升查询效率：

ALTER TABLE products ADD FULLTEXT INDEX idx_keywords(keywords);

然后用FULLTEXT的BOOLEAN模式结合统计匹配数量：

SELECT 
    product_id,
    keywords,
    (
        MATCH(keywords) AGAINST('+laser' IN BOOLEAN MODE) +
        MATCH(keywords) AGAINST('+wedding' IN BOOLEAN MODE) +
        MATCH(keywords) AGAINST('+cake' IN BOOLEAN MODE) +
        MATCH(keywords) AGAINST('+box' IN BOOLEAN MODE)
    ) AS match_count
FROM products
WHERE MATCH(keywords) AGAINST('laser wedding cake box' IN BOOLEAN MODE)
ORDER BY match_count DESC;

• MATCH...AGAINST('+词' IN BOOLEAN MODE)会返回1（匹配）或0（不匹配），求和后得到匹配总数；
• WHERE条件用BOOLEAN模式匹配所有包含至少一个关键词的记录；
• 同样按match_count降序排序，满足你的排序需求。

注意事项 #

• 如果搜索词是用户动态输入的，需要先拆分出单个关键词，再动态生成对应的SQL语句；
• 两种方法都能严格按匹配数量排序，方法二更适合大数据量场景，方法一无需索引，适合小数据量或临时查询。

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