大數據量下的默認值策略：避免全表掃描的實用技巧

原文僅一句：「這個方法不錯。那你大可以把默認值設為 99999」。經上下文推測，這應針對分頁查詢、排序或條件篩選中「無條件時返回所有數據」的性能隱患。

場景重構

典型分頁 API：GET /api/list?page=1&size=20&status=1

• 若 status 未傳，業務期望「返回所有狀態」
• 寫法 A：WHERE status = ? 綁定 99999 → 索引失效，全表掃描
• 寫法 B：動態構建 SQL，無條件則不加 WHERE → 命中索引，性能穩定

正確做法：動態條件構建

function buildWhere(array $filters): array {
    $where = [];
    $params = [];
    
    if (isset($filters['status']) && $filters['status'] !== '') {
        $where[] = 'status = ?';
        $params[] = $filters['status'];
    }
    if (!empty($filters['keyword'])) {
        $where[] = 'title LIKE ?';
        $params[] = "%{$filters['keyword']}%";
    }
    // ... 其他條件
    
    $sql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
    return [$sql, $params];
}

// 使用
[$whereSql, $params] = buildWhere($_GET);
$stmt = $pdo->prepare("SELECT * FROM news $whereSql ORDER BY id DESC LIMIT ?, ?");
$params[] = ($page - 1) * $size;
$params[] = $size;
$stmt->execute($params);

為什麼「大數字默認值」是反模式

進階：通用查詢構建器模式

class QueryBuilder {
    private $pdo;
    private $table;
    private $wheres = [];
    private $params = [];
    
    public function __construct(PDO $pdo, string $table) {
        $this->pdo = $pdo;
        $this->table = $table;
    }
    
    public function where(string $column, $value, string $op = '='): self {
        if ($value !== '' && $value !== null) {
            $this->wheres[] = "$column $op ?";
            $this->params[] = $value;
        }
        return $this;
    }
    
    public function whereLike(string $column, string $value): self {
        return $this->where($column, "%$value%", 'LIKE');
    }
    
    public function paginate(int $page, int $perPage): array {
        $whereSql = $this->wheres ? 'WHERE ' . implode(' AND ', $this->wheres) : '';
        $total = $this->pdo->query("SELECT COUNT(*) FROM {$this->table} $whereSql", $this->params)->fetchColumn();
        
        $stmt = $this->pdo->prepare("SELECT * FROM {$this->table} $whereSql ORDER BY id DESC LIMIT ?, ?");
        $params = array_merge($this->params, [($page-1)*$perPage, $perPage]);
        $stmt->execute($params);
        
        return ['data' => $stmt->fetchAll(), 'total' => $total];
    }
}

// 使用
$data = (new QueryBuilder($pdo, 'news'))
    ->where('status', $_GET['status'] ?? '')
    ->whereLike('title', $_GET['keyword'] ?? '')
    ->paginate(1, 20);

結論：拒絕「萬能默認值」，擁抱動態 SQL 構建。現代框架（Laravel Eloquent、ThinkPHP Db、Medoo）均內建類似機制，直接使用即可。