PHP数据库全文索引与搜索

PHP数据库全文索引与搜索

MySQL的全文索引可以在内容中快速搜索关键词。配合PHP可以实现高效的搜索功能。今天说说PHP中全文搜索的实现。

创建全文索引。

```php
$pdo->exec("ALTER TABLE articles ADD FULLTEXT INDEX ft_search (title, content)");
?>

自然语言模式搜索。

```php
$keyword = 'PHP编程';
$stmt = $pdo->prepare("
SELECT *, MATCH(title, content) AGAINST(:keyword IN NATURAL LANGUAGE MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST(:keyword IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 20
");
$stmt->execute(['keyword' => $keyword]);
$results = $stmt->fetchAll();
echo "找到 " . count($results) . " 条结果\n";
?>

布尔模式支持更多操作符。

```php
$keyword = '+PHP +MySQL -JavaScript';

$stmt = $pdo->prepare("
SELECT *, MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 20
");
$stmt->execute(['keyword' => $keyword]);
$results = $stmt->fetchAll();

// 布尔操作符
// + 必须包含
// - 不能包含
// * 通配符
// ~ 降低相关性
?>

搜索的高亮显示。

```php
function highlight(string $text, string $keyword): string
{
$keywords = explode(' ', $keyword);
foreach ($keywords as $word) {
$text = preg_replace("/({$word})/iu", '$1', $text);
}
return $text;
}

foreach ($results as &$row) {
$row['title_highlight'] = highlight($row['title'], $keyword);
$row['content_excerpt'] = highlight(mb_substr($row['content'], 0, 200), $keyword);
}
?>

分页搜索。

```php
function searchWithPagination(PDO $pdo, string $keyword, int $page = 1, int $perPage = 20): array
{
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare("
SELECT *, MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT :limit OFFSET :offset
");
$stmt->execute(['keyword' => $keyword, 'limit' => $perPage, 'offset' => $offset]);
$results = $stmt->fetchAll();

$countStmt = $pdo->prepare("SELECT COUNT(*) FROM articles WHERE MATCH(title, content) AGAINST(:keyword IN BOOLEAN MODE)");
$countStmt->execute(['keyword' => $keyword]);
$total = (int)$countStmt->fetchColumn();

return [
'data' => $results,
'total' => $total,
'page' => $page,
'total_pages' => ceil($total / $perPage),
];
}
?>

搜索建议功能。

```php
function getSuggestions(PDO $pdo, string $prefix, int $limit = 10): array
{
$stmt = $pdo->prepare("
SELECT DISTINCT title FROM articles
WHERE title LIKE :prefix
LIMIT :limit
");
$stmt->execute(['prefix' => "{$prefix}%", 'limit' => $limit]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

$suggestions = getSuggestions($pdo, 'PHP');
print_r($suggestions);
?>

MySQL全文索引适合中小规模的搜索需求。数据量大时建议用Elasticsearch等专业搜索引擎。全文索引的性能比LIKE查询好得多，分词器的选择影响搜索准确性。