MySQL如何高效查询包含指定独立单词的帖子标题？

1. 使用正则表达式匹配独立单词 #

MySQL支持REGEXP/RLIKE正则匹配，其中[[:<:]]和[[:>:]]代表单词边界，能精准匹配独立单词（默认忽略大小写，取决于表的collation配置，如utf8mb4_general_ci）。

单个搜索词的查询示例：

SELECT * FROM posts 
WHERE posts.post_title REGEXP '[[:<:]]hi[[:>:]]';

该查询会匹配Title1 hi、Title3 hi, more text这类包含独立"hi"的标题，不会匹配Title2 hint。

多搜索词（如"Hi everyone"）需匹配任意一个词时，可通过两种方式实现：

-- 方式1：多条件拼接
SELECT * FROM posts 
WHERE posts.post_title REGEXP '[[:<:]]hi[[:>:]]'
   OR posts.post_title REGEXP '[[:<:]]everyone[[:>:]]';

-- 方式2：合并正则表达式
SELECT * FROM posts 
WHERE posts.post_title REGEXP '[[:<:]]hi[[:>:]]|[[:<:]]everyone[[:>:]]';

2. 用全文索引实现高效搜索 #

若数据集较大，正则会因全表扫描导致性能瓶颈，推荐使用MySQL全文索引优化文本搜索。

步骤1：创建全文索引 #

先为post_title字段添加全文索引：

ALTER TABLE posts ADD FULLTEXT INDEX idx_post_title (post_title);

步骤2：执行全文搜索 #

在BOOLEAN模式下，用双引号包裹单词表示精确匹配独立单词，多词空格分隔表示匹配任意一个：

SELECT * FROM posts 
WHERE MATCH(post_title) AGAINST('"hi" "everyone"' IN BOOLEAN MODE);

该查询性能远高于正则或LIKE，且能精准命中目标结果。

注意事项 #

• 全文索引默认忽略长度小于4的单词（如"hi"），需修改MySQL配置ft_min_word_len=2，重启服务后重建索引：

  ALTER TABLE posts DROP INDEX idx_post_title;
  ALTER TABLE posts ADD FULLTEXT INDEX idx_post_title (post_title);
  

• 全文搜索默认不区分大小写，无需额外处理。

3. PHP代码安全处理 #

无论采用哪种方案，都要避免直接拼接SQL字符串，通过预处理语句防止注入：

正则方案示例（PDO）：

$searchTerms = preg_split('/\s+/', trim($data->searchString));
$conditions = [];
foreach ($searchTerms as $term) {
    $conditions[] = "posts.post_title REGEXP '[[:<:]]".preg_quote($term)."[[:>:]]'";
}
$whereClause = implode(' OR ', $conditions);

$stmt = $pdo->prepare("SELECT * FROM posts WHERE $whereClause");
$stmt->execute();
$posts = $stmt->fetchAll();

全文搜索方案示例（PDO）：

$searchTerms = preg_split('/\s+/', trim($data->searchString));
$searchString = '"' . implode('" "', array_map('preg_quote', $searchTerms)) . '"';

$stmt = $pdo->prepare("SELECT * FROM posts WHERE MATCH(post_title) AGAINST(? IN BOOLEAN MODE)");
$stmt->execute([$searchString]);
$posts = $stmt->fetchAll();

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