PHP MySQL Where
WHERE clauses, LIKE matching, IN and BETWEEN, and building conditions safely.
Key PointsCollect conditions and parameters in pairs, then join with placeholders.
Basic conditions
PHP
<?php
$stmt = $pdo->prepare("SELECT id, name, age FROM users WHERE age >= ? AND status = ?");
$stmt->execute([18, 1]);
foreach ($stmt->fetchAll() as $r) {
echo "{$r['name']} {$r['age']}\n";
}Output
Tom 30
Amy 25
Bob 28LIKE
PHP
<?php
$kw = "o";
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["%$kw%"]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
// 前缀匹配（能用索引）
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["T%"]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)
Array
(
[0] => Tom
)LIKE '%kw%' cannot use an index — full scan. Only prefix LIKE 'kw%' uses it. For large-scale fuzzy search use FULLTEXT or Elasticsearch.% and _ in user input act as wildcards. Escape them for literal matching.
PHP
<?php
function likeEscape(string $s): string {
return str_replace(["\\", "%", "_"], ["\\\\", "\\%", "\\_"], $s);
}
$kw = "50%_off";
$pattern = "%" . likeEscape($kw) . "%";
echo $pattern, "\n";
echo strlen($pattern), "\n";Output
%50\%\_off%
11IN and BETWEEN
PHP
<?php
$ids = [1, 3];
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("SELECT name FROM users WHERE id IN ($in) ORDER BY id");
$stmt->execute($ids);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
$stmt = $pdo->prepare("SELECT name FROM users WHERE age BETWEEN ? AND ?");
$stmt->execute([25, 29]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)
Array
(
[0] => Amy
[1] => Bob
)BETWEEN 25 AND 29 is inclusive. Beware date ranges: BETWEEN '2023-11-01' AND '2023-11-30' misses records with a time on the 30th.
Building conditions dynamically
When the number of filters varies, collect conditions and parameters in parallel arrays and join with AND. Never interpolate values.
PHP
<?php
$input = ["kw" => "o", "min_age" => 26, "status" => 1];
$where = [];
$params = [];
if (($input["kw"] ?? "") !== "") {
$where[] = "name LIKE ?";
$params[] = "%" . $input["kw"] . "%";
}
if (isset($input["min_age"])) {
$where[] = "age >= ?";
$params[] = (int)$input["min_age"];
}
if (isset($input["status"])) {
$where[] = "status = ?";
$params[] = (int)$input["status"];
}
$sql = "SELECT name FROM users";
if ($where) {
$sql .= " WHERE " . implode(" AND ", $where);
}
$sql .= " ORDER BY id";
echo $sql, "\n";
echo "参数: " . implode(", ", $params) . "\n";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
SELECT name FROM users WHERE name LIKE ? AND age >= ? AND status = ? ORDER BY id
参数: %o%, 26, 1
Array
(
[0] => Tom
)Conditions and parameters must be added together. Adding one without the other yields HY093.
The NULL trap
PHP
<?php
// 错：= NULL 永远不成立
$sql1 = "SELECT COUNT(*) FROM users WHERE age = NULL";
// 对：用 IS NULL
$sql2 = "SELECT COUNT(*) FROM users WHERE age IS NULL";
// 对：NULL 安全比较
$sql3 = "SELECT COUNT(*) FROM users WHERE age <=> NULL";
echo $sql1, "\n";
echo $sql2, "\n";
echo $sql3, "\n";Output
SELECT COUNT(*) FROM users WHERE age = NULL
SELECT COUNT(*) FROM users WHERE age IS NULL
SELECT COUNT(*) FROM users WHERE age <=> NULL= NULL evaluates to NULL, never true — same for != NULL. **Use IS NULL / IS NOT NULL**.基本条件
PHP
<?php
$stmt = $pdo->prepare("SELECT id, name, age FROM users WHERE age >= ? AND status = ?");
$stmt->execute([18, 1]);
foreach ($stmt->fetchAll() as $r) {
echo "{$r['name']} {$r['age']}\n";
}Output
Tom 30
Amy 25
Bob 28LIKE 模糊匹配
PHP
<?php
$kw = "o";
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["%$kw%"]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
// 前缀匹配（能用索引）
$stmt = $pdo->prepare("SELECT name FROM users WHERE name LIKE ?");
$stmt->execute(["T%"]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)
Array
(
[0] => Tom
)LIKE '%kw%' 用不上索引，全表扫描。只有 LIKE 'kw%' 这种前缀匹配才能走索引。数据量大又需要全文模糊搜索，应该上全文索引（FULLTEXT）或 Elasticsearch。用户输入里的 % 和 _ 是通配符，会被当成模式而不是字面量。要精确匹配就得转义。
PHP
<?php
function likeEscape(string $s): string {
return str_replace(["\\", "%", "_"], ["\\\\", "\\%", "\\_"], $s);
}
$kw = "50%_off";
$pattern = "%" . likeEscape($kw) . "%";
echo $pattern, "\n";
echo strlen($pattern), "\n";Output
%50\%\_off%
11IN 与 BETWEEN
PHP
<?php
$ids = [1, 3];
$in = implode(",", array_fill(0, count($ids), "?"));
$stmt = $pdo->prepare("SELECT name FROM users WHERE id IN ($in) ORDER BY id");
$stmt->execute($ids);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));
$stmt = $pdo->prepare("SELECT name FROM users WHERE age BETWEEN ? AND ?");
$stmt->execute([25, 29]);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
Array
(
[0] => Tom
[1] => Bob
)
Array
(
[0] => Amy
[1] => Bob
)BETWEEN 25 AND 29 等价于 >= 25 AND <= 29，两端都包含。日期范围要小心：BETWEEN '2023-11-01' AND '2023-11-30' 会漏掉 11 月 30 日当天有时分秒的记录。
动态拼接条件
筛选条件数量不固定时，用「条件数组 + 参数数组」成对收集，最后 implode(" AND ")。永远不要拼值。
PHP
<?php
$input = ["kw" => "o", "min_age" => 26, "status" => 1];
$where = [];
$params = [];
if (($input["kw"] ?? "") !== "") {
$where[] = "name LIKE ?";
$params[] = "%" . $input["kw"] . "%";
}
if (isset($input["min_age"])) {
$where[] = "age >= ?";
$params[] = (int)$input["min_age"];
}
if (isset($input["status"])) {
$where[] = "status = ?";
$params[] = (int)$input["status"];
}
$sql = "SELECT name FROM users";
if ($where) {
$sql .= " WHERE " . implode(" AND ", $where);
}
$sql .= " ORDER BY id";
echo $sql, "\n";
echo "参数: " . implode(", ", $params) . "\n";
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));Output
SELECT name FROM users WHERE name LIKE ? AND age >= ? AND status = ? ORDER BY id
参数: %o%, 26, 1
Array
(
[0] => Tom
)条件和参数必须同增同减。只加条件不加参数是最常见的错误，报 HY093（参数数量不匹配）。
NULL 的坑
PHP
<?php
// 错：= NULL 永远不成立
$sql1 = "SELECT COUNT(*) FROM users WHERE age = NULL";
// 对：用 IS NULL
$sql2 = "SELECT COUNT(*) FROM users WHERE age IS NULL";
// 对：NULL 安全比较
$sql3 = "SELECT COUNT(*) FROM users WHERE age <=> NULL";
echo $sql1, "\n";
echo $sql2, "\n";
echo $sql3, "\n";Output
SELECT COUNT(*) FROM users WHERE age = NULL
SELECT COUNT(*) FROM users WHERE age IS NULL
SELECT COUNT(*) FROM users WHERE age <=> NULL= NULL 的结果是 NULL 不是 true，永远匹配不到行。同理 != NULL 也匹配不到。**判断空值只能用 IS NULL / IS NOT NULL**。