PHP MySQL 關鍵字搜尋與結果輸出完整教學

本教學示範如何建立搜尋資料表、儲存關鍵字資料，並使用 PHP 連接 MySQL 根據使用者輸入的關鍵字進行資料庫查詢，將符合條件的記錄輸出顯示。採用 PDO Prepared Statements 確保安全性，適合站內搜尋、關鍵字過濾、內容檢索等基礎應用。

1. 資料庫準備

-- 建立資料庫與資料表
CREATE DATABASE IF NOT EXISTS `search_demo` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `search_demo`;

CREATE TABLE IF NOT EXISTS `search_keywords` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `keyword` VARCHAR(500) NOT NULL COMMENT '關鍵字內容',
  `category` VARCHAR(50) DEFAULT NULL COMMENT '分類',
  `weight` INT(11) DEFAULT '1' COMMENT '權重/排序',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_keyword` (`keyword`),
  KEY `idx_category` (`category`),
  FULLTEXT KEY `ft_keyword` (`keyword`) -- 全文索引 (MySQL 5.6+ InnoDB)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='搜尋關鍵字表';

-- 測試資料
INSERT INTO `search_keywords` (`keyword`, `category`, `weight`) VALUES
('內容搜尋', '教學', 10),
('PHP 站內搜尋', '教學', 8),
('MySQL 全文檢索', '資料庫', 9),
('PDO Prepared Statements', '安全', 7),
('PHP 資料庫操作', '教學', 6),
('Laravel Eloquent 搜尋', '框架', 5);

2. 完整搜尋程式 (search.php) - PDO 安全版

 PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        try {
            $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
        } catch (PDOException $e) {
            die("資料庫連線失敗: " . $e->getMessage());
        }
    }
    return $pdo;
}

// 安全輸出
function h($str) {
    return htmlspecialchars($str, ENT_QUOTES, 'UTF-8');
}

// 高亮關鍵字
function highlight($text, $keyword) {
    if (!$keyword) return h($text);
    return preg_replace('/(' . preg_quote($keyword, '/') . ')/iu', '$1', h($text));
}

// ===== 主程式 =====
$pdo = getPDO();
$keyword = trim($_GET['q'] ?? '');
$category = $_GET['cat'] ?? '';
$page = max(1, (int)($_GET['page'] ?? 1));
$perPage = 10;
$offset = ($page - 1) * $perPage;

$results = [];
$total = 0;
$sql = "";
$params = [];

if ($keyword !== '') {
    // === 方式一：LIKE 模糊搜尋 (適用小量資料、需精確匹配) ===
    $where = "WHERE `keyword` LIKE ?";
    $params[] = "%$keyword%";
    
    if ($category) {
        $where .= " AND `category` = ?";
        $params[] = $category;
    }
    
    // 總筆數
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM `search_keywords` $where");
    $stmt->execute($params);
    $total = $stmt->fetchColumn();
    
    // 分頁查詢
    $sql = "SELECT * FROM `search_keywords` $where ORDER BY `weight` DESC, `id` DESC LIMIT $offset, $perPage";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll();
    
    /* 
    === 方式二：全文檢索 (MySQL 5.6+ InnoDB，大量資料、自然語言搜尋) ===
    $where = "WHERE MATCH(`keyword`) AGAINST(? IN NATURAL LANGUAGE MODE)";
    $params = [$keyword];
    if ($category) { $where .= " AND `category` = ?"; $params[] = $category; }
    // ... 類似執行方式
    
    === 方式三：布林模式全文檢索 (支援 + - * 運算子) ===
    $where = "WHERE MATCH(`keyword`) AGAINST(? IN BOOLEAN MODE)";
    $params = [$keyword]; // 如: '+PHP -教學 搜尋*'
    */
}

$totalPages = ceil($total / $perPage);
$categories = $pdo->query("SELECT DISTINCT `category` FROM `search_keywords` WHERE `category` IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN);
?>



    
    
    


    
🔍 關鍵字搜尋系統
    
    

        
        
            全部分類
            
                >
            
        
        搜尋
    
    
    
        

            找到  筆結果 (第  /  頁)，搜尋關鍵字：
        
        
        

            
                
沒有找到符合「」的資料
            
                
                

                    

                    

                        ID: 
                        
                            
                        
                        權重: 
                        建立: 
                    
                
                
            
        
        
         1): ?>
        
        
    
        
請輸入關鍵字開始搜尋
    

3. 搜尋方式比較與選擇指南

中文全文檢索注意事項

• MySQL 內建全文檢索對中文支援有限 (按字元而非詞切分)
• 建議：使用 ngram 解析器 (MySQL 5.7.6+)：FULLTEXT KEY ft_kw (keyword) WITH PARSER ngram
• 或整合 jieba PHP、SCWS 斷詞後儲存分詞欄位
• 生產環境強烈建議使用 Elasticsearch + IK Analyzer 或 MeiliSearch (輕量、易部署)

效能優化建議

1. 索引策略：LIKE 搜尋建立前綴索引 INDEX idx_kw (keyword(50))；全文檢索建立 FULLTEXT 索引
2. 快取熱門查詢：Redis 快取搜尋結果 (TTL 5-30 分鐘)
3. 分頁優化：深分頁 (page > 1000) 改用 WHERE id > last_id LIMIT 10 游標式分頁
4. 非同步索引：寫入時送進佇列，背景 Worker 更新搜尋索引
5. 只讀副本：搜尋查詢導向 Read Replica，減輕主庫壓力

安全性檢查清單

• ✅ 使用 Prepared Statements 防止 SQL 注入
• ✅ 輸出時 htmlspecialchars() 防止 XSS
• ✅ 關鍵字長度限制 (建議 1-100 字元)
• ✅ 限流：同 IP 每分鐘搜尋次數限制
• ✅ 敏感詞過濾 (可選)
• ⚠️ 避免直接在 SQL 中拼接使用者輸入