MySQL WHERE 子句完全指南：語法、操作符、BINARY 區分大小寫、PHP 實戰示例

MySQL WHERE 子句深度解析

WHERE 子句用於篩選滿足條件的行，可用於 SELECT、UPDATE、DELETE，是精準查詢的核心。

基本語法

SELECT col1, col2, ... 
FROM table1, table2 ... 
WHERE condition1 [AND|OR] condition2 ...

常用操作符對照表

字符串比較大小寫敏感度控制

默認不區分大小寫：

SELECT * FROM tutorials_tbl WHERE tutorial_author = 'Sanjay';  -- 匹配 'sanjay'/'SANJAY'

強制區分大小寫用 BINARY：

SELECT * FROM tutorials_tbl WHERE BINARY tutorial_author = 'sanjay';  -- 僅匹配小寫

命令行實戰

mysql -u root -p
USE TUTORIALS;
SELECT * FROM tutorials_tbl WHERE tutorial_author = 'Sanjay';

PHP PDO 參數化查詢示例（防注入）

<?php
$dsn = 'mysql:host=localhost;dbname=TUTORIALS;charset=utf8mb4';
$user = 'root';
$pass = 'rootpassword';

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
    
    $author = 'Sanjay';
    $stmt = $pdo->prepare('
        SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
        FROM tutorials_tbl 
        WHERE tutorial_author = :author
    ');
    $stmt->execute([':author' => $author]);
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: {$row['tutorial_id']}
";
        echo "Title: {$row['tutorial_title']}
";
        echo "Author: {$row['tutorial_author']}
";
        echo "Date: {$row['submission_date']}
";
    }
    echo "Query executed successfully\n";
} catch (PDOException $e) {
    die("Error: " . $e->getMessage());
}
?>

性能建議

• 主鍵/唯一索引列作為 WHERE 條件最快
• 多條件時，將過濾性最強（選擇率最低）的條件前置
• 避免在索引列上使用函數/運算（導致索引失效）
• LIKE 'abc%' 可用索引，LIKE '%abc%' 全掃