如何用MySQL或PHP提取数据库字段中包含指定子串的完整匹配内容？

你原来的SQL写法是固定返回静态字符串WORD_THAT_CONTAINS xxx，没有从title字段中动态提取匹配的片段，所以无法得到预期结果。以下是两种可行实现方案：

MySQL 实现方案 #

要求MySQL版本 >= 8.0，内置递归CTE和正则匹配函数可以直接提取匹配片段：

单字符搜索（例如搜索re） #

WITH RECURSIVE split_matches AS (
    SELECT 
        id,
        title,
        -- 提取第一个包含re的完整单词
        REGEXP_SUBSTR(title, '\\b[[:alpha:]]*re[[:alpha:]]*\\b', 1) AS match_word,
        1 AS occurrence
    FROM myTable
    WHERE title REGEXP '\\b[[:alpha:]]*re[[:alpha:]]*\\b'
    UNION ALL
    SELECT 
        sm.id,
        sm.title,
        REGEXP_SUBSTR(sm.title, '\\b[[:alpha:]]*re[[:alpha:]]*\\b', 1, sm.occurrence + 1) AS match_word,
        sm.occurrence + 1
    FROM split_matches sm
    WHERE REGEXP_SUBSTR(sm.title, '\\b[[:alpha:]]*re[[:alpha:]]*\\b', 1, sm.occurrence + 1) IS NOT NULL
)
-- 去重后返回结果
SELECT DISTINCT match_word AS title FROM split_matches;

多词连续搜索（例如搜索Lorem Ip） #

修改正则规则匹配跨词的连续片段即可：

WITH RECURSIVE split_matches AS (
    SELECT 
        id,
        title,
        REGEXP_SUBSTR(title, '\\b[^.?!\n]*?Lorem Ip[^.?!\n]*?\\b', 1) AS match_word,
        1 AS occurrence
    FROM myTable
    WHERE title LIKE '%Lorem Ip%'
    UNION ALL
    SELECT 
        sm.id,
        sm.title,
        REGEXP_SUBSTR(sm.title, '\\b[^.?!\n]*?Lorem Ip[^.?!\n]*?\\b', 1, sm.occurrence + 1) AS match_word,
        sm.occurrence + 1
    FROM split_matches sm
    WHERE REGEXP_SUBSTR(sm.title, '\\b[^.?!\n]*?Lorem Ip[^.?!\n]*?\\b', 1, sm.occurrence + 1) IS NOT NULL
)
SELECT DISTINCT match_word AS title FROM split_matches LIMIT 10;

PHP 实现方案 #

适配所有MySQL版本，逻辑更灵活，适合业务迭代快的场景：

<?php
// 接收并过滤搜索参数
$searchKey = trim($_GET['keyword'] ?? '');
if (empty($searchKey)) {
    echo json_encode([]);
    exit;
}

// 转义正则特殊字符，避免注入风险
$escapedSearch = preg_quote($searchKey, '/');
// 构造匹配正则：提取包含搜索子串的连续完整片段
$pattern = "/\b[[:alnum:][:space:]]*?" . $escapedSearch . "[[:alnum:][:space:]]*?\b/u";

// 连接数据库，先过滤包含搜索子串的记录，减少后续处理量
$pdo = new PDO('mysql:host=你的数据库地址;dbname=你的库名;charset=utf8mb4', '账号', '密码');
$stmt = $pdo->prepare("SELECT title FROM myTable WHERE title LIKE ?");
$stmt->execute(["%{$searchKey}%"]);
$titleList = $stmt->fetchAll(PDO::FETCH_COLUMN);

$result = [];
foreach ($titleList as $title) {
    preg_match_all($pattern, $title, $matches);
    if (!empty($matches[0])) {
        foreach ($matches[0] as $match) {
            $trimMatch = trim($match);
            // 去重
            if (!in_array($trimMatch, $result)) {
                $result[] = $trimMatch;
            }
        }
    }
}

// 自动补全一般限制返回10条以内结果
$result = array_slice($result, 0, 10);
echo json_encode($result);
?>

优化建议 #

• 数据量超过10万条时，建议单独维护搜索补全字典表，提前存储所有可能的补全词条，搜索时直接查字典表，无需每次扫描全表
• 高并发场景下建议使用Elasticsearch等专业搜索引擎实现补全功能，支持模糊匹配、热度排序、拼写纠错等扩展能力
• 补全结果可以增加点击热度权重，优先展示用户高频搜索的词条

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