PHP MySQL 關鍵字查詢實戰：單/多條件搜尋、結果高亮與防注入

關鍵字搜尋是 Web 應用最高頻功能之一。本文從 SQL 拼接風險、Mysqli 預處理、全文檢索遷移路徑三個維度，構建安全可擴展的查詢方案。

現狀痛點分析

原始碼存在三大風險：

1. SQL 注入：直接拼接 $_POST['name'] 進 SQL
2. XSS 風險：關鍵字直接輸出到 HTML 未轉義
3. 效能瓶頸：LIKE '%keyword%' 導致全表掃描，無法使用索引

進階方案：預處理 + 參數綁定

// Database.php - 單例連接管理
class Database {
    private static ?mysqli $instance = null;
    private mysqli $conn;

    private function __construct() {
        $this->conn = new mysqli('localhost', 'root', '', 'mydb');
        $this->conn->set_charset('utf8mb4');
        if ($this->conn->connect_errno) {
            throw new Exception('DB連接失敗: ' . $this->conn->connect_error);
        }
    }

    public static function getInstance(): self {
        return self::$instance ??= new self();
    }

    public function getConnection(): mysqli {
        return $this->conn;
    }
}

// SearchService.php - 查詢邏輯封裝
class SearchService {
    private mysqli $db;

    public function __construct() {
        $this->db = Database::getInstance()->getConnection();
    }

    /**
     * 單關鍵字搜尋（車名模糊匹配）
     */
    public function searchByName(string $keyword, int $page = 1, int $size = 20): array {
        $offset = ($page - 1) * $size;
        $sql = "SELECT id, name, fuel, power, price FROM Car WHERE name LIKE ? LIMIT ?, ?";
        
        $stmt = $this->db->prepare($sql);
        $likeKeyword = "%{$keyword}%";
        $stmt->bind_param('sii', $likeKeyword, $offset, $size);
        $stmt->execute();
        
        return $this->fetchAll($stmt);
    }

    /**
     * 多條件組合搜尋
     */
    public function searchMulti(array $filters, int $page = 1, int $size = 20): array {
        $conditions = ['1=1'];
        $params = [];
        $types = '';

        if (!empty($filters['name'])) {
            $conditions[] = 'name LIKE ?';
            $params[] = "%{$filters['name']}%";
            $types .= 's';
        }
        if (!empty($filters['brand'])) {
            $conditions[] = 'brand = ?';
            $params[] = $filters['brand'];
            $types .= 's';
        }
        // 可擴展：價格區間、年份範圍等
        if (!empty($filters['price_min'])) {
            $conditions[] = 'price >= ?';
            $params[] = $filters['price_min'];
            $types .= 'd';
        }

        $sql = "SELECT id, name, brand, price, fuel, power FROM Car WHERE " . implode(' AND ', $conditions) 
             . " LIMIT ?, ?";
        $params[] = ($page - 1) * $size;
        $params[] = $size;
        $types .= 'ii';

        $stmt = $this->db->prepare($sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();

        return $this->fetchAll($stmt);
    }

    private function fetchAll(mysqli_stmt $stmt): array {
        $result = $stmt->get_result();
        return $result->fetch_all(MYSQLI_ASSOC);
    }
}

前端整合：關鍵字高亮（防 XSS）

function highlightKeyword(string $text, string $keyword): string {
    if ($keyword === '') return htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
    
    // 1. 先整體轉義
    $safeText = htmlspecialchars($text, ENT_QUOTES, 'UTF-8');
    $safeKeyword = htmlspecialchars($keyword, ENT_QUOTES, 'UTF-8');
    
    // 2. 再替換高亮標記（不再被轉義）
    return str_ireplace(
        $safeKeyword,
        "{$safeKeyword}",
        $safeText
    );
}

// 使用範例
$cars = $searchService->searchByName($_GET['q'] ?? '');
foreach ($cars as $car) {
    echo "";
    echo "{$car['id']}";
    echo "" . highlightKeyword($car['name'], $_GET['q'] ?? '') . "";
    echo "{$car['fuel']}";
    echo "{$car['power']}";
    echo "{$car['price']}";
    echo "";
}

效能進階：MySQL 全文檢索遷移

當資料量超過 10 萬行，LIKE 查詢明顯變慢，建議遷移至全文索引：

-- 1. 建立全文索引（MySQL 5.6+ InnoDB 支援）
ALTER TABLE Car ADD FULLTEXT INDEX ft_name_brand (name, brand);

-- 2. 自然語言模式搜尋
SELECT id, name, brand, price,
       MATCH(name, brand) AGAINST('寶馬 X5' IN NATURAL LANGUAGE MODE) AS relevance
FROM Car
WHERE MATCH(name, brand) AGAINST('寶馬 X5' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC
LIMIT 20;

-- 3. 布爾模式（精確控制）
SELECT * FROM Car
WHERE MATCH(name, brand) AGAINST('+寶馬 -二手' IN BOOLEAN MODE);

架構演進路線圖

中文分詞特殊處理

MySQL 內建 NGRAM 分詞器（5.7.6+）適合中文：

-- 建表指定解析器
CREATE TABLE Car (
    name VARCHAR(255),
    FULLTEXT INDEX ft_name (name) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

搜尋系統建設核心原則：先正確（防注入）、再安全（防 XSS）、後快（索引/引擎）。不要為了「快」犧牲前兩者，技術債最終會在最痛的時候爆雷。