如何让MySQL的LIKE查询直接返回匹配子串的所有位置？

嘿，我完全懂你的困扰！一边用MySQL的LIKE做模糊匹配，转头还要在JS里重新扫描字符串找所有匹配位置，不仅多做了一遍活，还怕两边的匹配逻辑对不上——毕竟MySQL的LIKE和JS的字符串匹配细节可能有差异，确实让人不安。

好消息是，MySQL虽然没有直接返回所有匹配位置的内置函数，但我们可以用递归CTE或者自定义存储过程来实现，不用一次次循环查询，效率和一致性都能搞定。

方法一：用递归CTE一次性获取所有匹配位置 #

递归CTE是MySQL 8.0+支持的功能，可以在一次查询里递归查找所有匹配的位置，不用多次往返数据库。举个具体的例子：

假设你的表是articles，要搜索的字段是content，要匹配的子串存在变量@search_str里（注意如果你的搜索串有%或_，已经转义过的话，这里直接用转义后的字符串就行）。

-- 先定义要搜索的字符串
SET @search_str = 'your_target_substring';

WITH RECURSIVE match_positions AS (
    -- 初始查询：找到每个匹配行的第一个子串位置
    SELECT 
        id,
        content,
        LOCATE(@search_str, content) AS pos,
        1 AS match_count
    FROM articles
    WHERE content LIKE CONCAT('%', @search_str, '%')
    
    UNION ALL
    
    -- 递归查询：从上一个匹配位置的下一个字符开始，找下一个匹配
    SELECT 
        id,
        content,
        LOCATE(@search_str, content, pos + LENGTH(@search_str)) AS pos,
        match_count + 1
    FROM match_positions
    WHERE pos > 0 -- 直到找不到匹配（pos=0）就停止递归
)
-- 最后过滤掉没有匹配的记录，输出结果
SELECT id, pos
FROM match_positions
WHERE pos > 0
ORDER BY id, pos;

这个查询的逻辑是：

1. 初始部分先筛选出所有包含目标子串的行，同时拿到第一个匹配的位置；
2. 递归部分每次从上一个位置的下一个字符开始继续查找（避免重复匹配同一个子串）；
3. 最后把所有有效的匹配位置输出，按行ID和位置排序。

关键是：LOCATE函数和LIKE的匹配逻辑完全一致，都是基于字面字符串的匹配，不会有JS和MySQL匹配结果不一致的问题。

方法二：自定义存储过程，方便复用 #

如果这个需求你经常用到，可以把上面的逻辑封装成存储过程，调用起来更方便：

DELIMITER //
CREATE PROCEDURE GetAllMatchPositions(
    IN table_name VARCHAR(255),
    IN column_name VARCHAR(255),
    IN search_str VARCHAR(255)
)
BEGIN
    -- 注意处理SQL注入风险，用QUOTE()函数转义搜索字符串
    SET @safe_search_str = QUOTE(search_str);
    SET @sql = CONCAT(
        'WITH RECURSIVE match_positions AS (
            SELECT 
                id,
                ', column_name, ' AS content,
                LOCATE(', @safe_search_str, ', ', column_name, ') AS pos,
                1 AS match_count
            FROM ', table_name, '
            WHERE ', column_name, ' LIKE CONCAT("%", ', @safe_search_str, ', "%")
            
            UNION ALL
            
            SELECT 
                id,
                content,
                LOCATE(', @safe_search_str, ', content, pos + LENGTH(', @safe_search_str, ')) AS pos,
                match_count + 1
            FROM match_positions
            WHERE pos > 0
        )
        SELECT id, pos
        FROM match_positions
        WHERE pos > 0
        ORDER BY id, pos;'
    );
    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用的时候只需要执行：

CALL GetAllMatchPositions('articles', 'content', 'your_target_substring');

这里用了QUOTE()函数来转义搜索字符串，避免SQL注入的风险，尤其是当搜索串来自用户输入的时候一定要注意。

关于性能的小提醒 #

• 如果你的表数据量很大，而且是前后都带%的模糊匹配（比如%xxx%），MySQL无法用到普通索引，会全表扫描。但递归CTE只需要扫一次表，之后的位置查找都是在内存里递归处理，比你循环多次查询数据库要高效得多。
• 如果是前缀匹配（比如xxx%），可以给字段加普通索引，能大幅提升初始查询的速度。

这样一来，你就不用再在JS里二次处理字符串了，直接从MySQL拿到所有匹配位置，逻辑和LIKE完全一致，还省了不少麻烦！

备注：内容来源于stack exchange，提问作者Axel Carré