PHP MySQL WHERE 子句完整教學

語法

SELECT column_list FROM table_name WHERE condition

WHERE 子句用於過濾記錄，僅返回滿足條件的行。

常用運算子

MySQLi 範例

<?php
$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if ($mysqli->connect_errno) die('連線失敗: ' . $mysqli->connect_error);

$sql = "SELECT * FROM Persons WHERE FirstName = 'Peter'";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
    echo $row['FirstName'] . ' ' . $row['LastName'] . '<br>';
}
/* 輸出: Peter Griffin */
$mysqli->close();
?>

PDO 範例 (參數化)

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $name = 'Peter';
    $stmt = $pdo->prepare("SELECT * FROM Persons WHERE FirstName = :name");
    $stmt->bindValue(':name', $name);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['FirstName'] . ' ' . $row['LastName'] . '<br>';
    }
} catch (PDOException $e) {
    echo "錯誤: " . $e->getMessage();
}
?>

動態條件建構 (安全)

<?php
$conditions = [];
$params = [];

if (!empty($_GET['name'])) {
    $conditions[] = 'FirstName LIKE :name';
    $params[':name'] = '%' . $_GET['name'] . '%';
}
if (!empty($_GET['minAge'])) {
    $conditions[] = 'Age >= :minAge';
    $params[':minAge'] = (int)$_GET['minAge'];
}
if (!empty($_GET['city'])) {
    $conditions[] = 'City = :city';
    $params[':city'] = $_GET['city'];
}

$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
$sql = "SELECT * FROM Persons $where";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
// ...
?>

效能建議

• WHERE 條件欄位建立索引 (單欄或複合索引)。
• 避免在索引欄位上使用函數 (WHERE YEAR(CreatedAt) = 2024)，改用範圍 WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'。
• LIKE '%keyword%' 開頭萬用字元無法使用索引，全文搜尋請用 Fulltext Index 或 Elasticsearch。
• 大量資料分頁請用游標式 WHERE id > :lastId ORDER BY id LIMIT 20。