MySQL如何统计advertisement表行匹配多搜索关键词的总次数

核心思路是先合并caption和description两个字段的内容，再逐个统计每个搜索关键词的出现次数，最后累加得到总匹配数，以下是两种常用实现方案：

• 方案1适合固定最多搜索关键词数量的场景，兼容所有MySQL版本
• 方案2支持任意数量的动态关键词搜索，需要MySQL 8.0及以上版本支持

方案1：固定关键词数量实现 #

以支持最多2个关键词搜索为例，你举的delicious pizza搜索场景SQL写法如下：

SELECT 
    id,
    (
        -- 统计第一个关键词delicious的出现次数
        (CHAR_LENGTH(CONCAT(caption, ' ', description)) - CHAR_LENGTH(REPLACE(LOWER(CONCAT(caption, ' ', description)), LOWER('delicious'), ''))) / CHAR_LENGTH('delicious')
        +
        -- 统计第二个关键词pizza的出现次数
        (CHAR_LENGTH(CONCAT(caption, ' ', description)) - CHAR_LENGTH(REPLACE(LOWER(CONCAT(caption, ' ', description)), LOWER('pizza'), ''))) / CHAR_LENGTH('pizza')
    ) AS matchingCount
FROM advertisement
HAVING matchingCount > 0
ORDER BY matchingCount DESC;

注：

1. 合并两个字段时中间加空格，是为了避免两个字段首尾内容拼接后误匹配到关键词的问题
2. 加了LOWER()函数是为了实现大小写不敏感匹配，如果你需要大小写敏感可以直接去掉
3. 如果需要支持更多关键词，只要按照相同格式累加对应关键词的统计逻辑即可

方案2：动态关键词数量实现 #

如果需要支持用户输入任意数量的关键词，不需要每次修改SQL结构，可以用递归CTE自动拆分搜索关键词后统计：

-- 替换为用户实际输入的搜索内容
SET @searchKeywords = 'delicious pizza';

WITH RECURSIVE keywords AS (
    -- 拆分第一个关键词
    SELECT 
        1 AS idx,
        SUBSTRING_INDEX(@searchKeywords, ' ', 1) AS keyword,
        SUBSTRING(@searchKeywords, LENGTH(SUBSTRING_INDEX(@searchKeywords, ' ', 1)) + 2) AS remaining
    UNION ALL
    -- 递归拆分剩余所有关键词
    SELECT 
        idx + 1,
        SUBSTRING_INDEX(remaining, ' ', 1),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 2)
    FROM keywords
    WHERE remaining != ''
)
SELECT 
    a.id,
    SUM(
        (CHAR_LENGTH(CONCAT(a.caption, ' ', a.description)) - CHAR_LENGTH(REPLACE(LOWER(CONCAT(a.caption, ' ', a.description)), LOWER(k.keyword), ''))) / CHAR_LENGTH(k.keyword)
    ) AS matchingCount
FROM advertisement a
CROSS JOIN keywords k
GROUP BY a.id
HAVING matchingCount > 0
ORDER BY matchingCount DESC;

效果验证 #

用你提供的示例数据测试，两种方案的返回结果完全符合预期：

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