SQLite3 查询操作

概述

SQLite3 扩展提供了多种查询方法，支持直接查询、预处理语句和单步查询。理解不同查询方式的特性和适用场景，能够帮助你编写更安全、更高效的数据库操作代码。

查询方法选择

• exec() — 执行无返回结果的 SQL（DDL、INSERT、UPDATE、DELETE）
• query() — 执行返回结果集的 SQL（SELECT）
• prepare() + execute() — 预处理语句，推荐用于参数化查询
• querySingle() — 返回单行单列或单行数据

基础概念

查询执行流程

SQL 语句 → prepare() → bindValue() → execute() → fetchArray()/fetch()
              ↓
         直接执行
         exec() / query() / querySingle()

结果获取方式

方法	返回类型	适用场景
fetchArray(SQLITE3_ASSOC)	关联数组	按列名访问
fetchArray(SQLITE3_NUM)	索引数组	按位置访问
fetchArray(SQLITE3_BOTH)	两者都有	灵活访问（默认）
fetchArray(SQLITE3_OBJ)	匿名对象	对象风格访问

语法与代码

exec() — 执行无结果语句

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 创建表
$db->exec('
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL NOT NULL DEFAULT 0,
        stock INTEGER NOT NULL DEFAULT 0,
        category TEXT DEFAULT "uncategorized",
        created_at TEXT DEFAULT (datetime("now"))
    )
');

// 插入数据
$db->exec("
    INSERT INTO products (name, price, stock, category)
    VALUES ('iPhone 15', 7999.00, 100, '手机'),
           ('MacBook Pro', 14999.00, 50, '笔记本'),
           ('AirPods Pro', 1899.00, 200, '配件')
");

// 更新数据
$db->exec("UPDATE products SET stock = stock + 10 WHERE category = '配件'");

echo "影响行数: " . $db->changes() . "\n";
echo "最后插入ID: " . $db->lastInsertRowID() . "\n";

query() — 执行查询语句

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 查询所有产品
$result = $db->query('SELECT * FROM products ORDER BY price DESC');

// 遍历结果集 — 使用 SQLITE3_ASSOC 关联数组
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    printf(
        "ID: %d | 名称: %s | 价格: %.2f | 库存: %d\n",
        $row['id'],
        $row['name'],
        $row['price'],
        $row['stock']
    );
}

// 带条件查询
$result = $db->query('
    SELECT category, COUNT(*) as count, AVG(price) as avg_price
    FROM products
    GROUP BY category
    HAVING count > 0
    ORDER BY avg_price DESC
');

prepare() — 预处理语句

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 插入预处理
$stmt = $db->prepare('
    INSERT INTO products (name, price, stock, category)
    VALUES (:name, :price, :stock, :category)
');

// bindParam — 绑定引用（变量在 execute 时才求值）
$name = 'iPad Air';
$price = 4799.00;
$stmt->bindParam(':name', $name);
$stmt->bindParam(':price', $price);
$stmt->bindValue(':stock', 80, SQLITE3_INTEGER);
$stmt->bindValue(':category', '平板', SQLITE3_TEXT);
$stmt->execute();

// bindValue — 绑定值（立即求值）
$stmt->bindValue(':name', 'Apple Watch');
$stmt->bindValue(':price', 2999.00);
$stmt->bindValue(':stock', 150);
$stmt->bindValue(':category', '配件');
$stmt->execute();

echo "最后插入ID: " . $db->lastInsertRowID() . "\n";

querySingle() — 单值查询

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 返回单个值（第一行第一列）
$total = $db->querySingle('SELECT COUNT(*) FROM products');
echo "产品总数: {$total}\n";

// 返回完整行（关联数组）
$product = $db->querySingle('SELECT * FROM products WHERE id = 1', true);
if ($product) {
    echo "名称: {$product['name']}, 价格: {$product['price']}\n";
}

// 聚合查询
$maxPrice = $db->querySingle('SELECT MAX(price) FROM products');
$avgPrice = $db->querySingle('SELECT AVG(price) FROM products');
echo "最高价: {$maxPrice}, 平均价: {$avgPrice}\n";

busyTimeout — 处理并发等待

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 设置等待锁的超时时间（毫秒）
$db->busyTimeout(5000); // 等待最多5秒

try {
    $db->exec('BEGIN IMMEDIATE');
    $db->exec("UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0");
    $db->exec('COMMIT');
    echo "库存扣减成功\n";
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    if (str_contains($e->getMessage(), 'database is locked')) {
        echo "数据库繁忙，请稍后重试\n";
    } else {
        throw $e;
    }
}

BEGIN IMMEDIATE vs BEGIN DEFERRED

• BEGIN DEFERRED（默认）— 获取读锁，直到首次写操作时才升级为写锁
• BEGIN IMMEDIATE — 立即获取 RESERVED 锁，适合先读后写的事务
• BEGIN EXCLUSIVE — 立即获取排他锁，阻止所有其他访问

实战示例

通用查询构建器

<?php
declare(strict_types=1);

class SQLiteQueryBuilder
{
    private SQLite3 $db;
    private string $table;
    private array $wheres = [];
    private array $params = [];
    private array $orderBy = [];
    private ?int $limit = null;
    private ?int $offset = null;

    public function __construct(SQLite3 $db, string $table)
    {
        $this->db = $db;
        $this->table = $table;
    }

    public function where(string $column, string $operator, mixed $value): self
    {
        $placeholder = ':' . $column . '_' . count($this->wheres);
        $this->wheres[] = "{$column} {$operator} {$placeholder}";
        $this->params[$placeholder] = $value;
        return $this;
    }

    public function whereIn(string $column, array $values): self
    {
        $placeholders = array_map(
            fn(int $i) => ':' . $column . '_in_' . $i,
            array_keys($values)
        );
        $this->wheres[] = "{$column} IN (" . implode(', ', $placeholders) . ')';

        foreach ($values as $i => $value) {
            $this->params[':' . $column . '_in_' . $i] = $value;
        }
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orderBy[] = "{$column} {$direction}";
        return $this;
    }

    public function limit(int $limit, ?int $offset = null): self
    {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }

    public function get(): array
    {
        $sql = "SELECT * FROM {$this->table}";
        $sql .= $this->buildWhere();
        $sql .= $this->buildOrderBy();
        $sql .= $this->buildLimit();

        $stmt = $this->db->prepare($sql);
        foreach ($this->params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $result = $stmt->execute();

        $rows = [];
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $rows[] = $row;
        }
        return $rows;
    }

    public function first(): ?array
    {
        $this->limit = 1;
        $rows = $this->get();
        return $rows[0] ?? null;
    }

    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM {$this->table}";
        $sql .= $this->buildWhere();

        $stmt = $this->db->prepare($sql);
        foreach ($this->params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $result = $stmt->execute();
        $row = $result->fetchArray(SQLITE3_NUM);
        return (int) $row[0];
    }

    public function insert(array $data): int
    {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_map(
            fn(string $col) => ':' . $col,
            array_keys($data)
        ));

        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
        $stmt = $this->db->prepare($sql);

        foreach ($data as $col => $value) {
            $stmt->bindValue(':' . $col, $value);
        }
        $stmt->execute();

        return $this->db->lastInsertRowID();
    }

    public function update(array $data): int
    {
        $sets = [];
        foreach ($data as $col => $value) {
            $sets[] = "{$col} = :update_{$col}";
        }
        $sql = "UPDATE {$this->table} SET " . implode(', ', $sets);
        $sql .= $this->buildWhere();

        $stmt = $this->db->prepare($sql);
        foreach ($data as $col => $value) {
            $stmt->bindValue(':update_' . $col, $value);
        }
        foreach ($this->params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();

        return $this->db->changes();
    }

    public function delete(): int
    {
        $sql = "DELETE FROM {$this->table}";
        $sql .= $this->buildWhere();

        $stmt = $this->db->prepare($sql);
        foreach ($this->params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();

        return $this->db->changes();
    }

    private function buildWhere(): string
    {
        if (empty($this->wheres)) {
            return '';
        }
        return ' WHERE ' . implode(' AND ', $this->wheres);
    }

    private function buildOrderBy(): string
    {
        if (empty($this->orderBy)) {
            return '';
        }
        return ' ORDER BY ' . implode(', ', $this->orderBy);
    }

    private function buildLimit(): string
    {
        $sql = '';
        if ($this->limit !== null) {
            $sql .= " LIMIT {$this->limit}";
        }
        if ($this->offset !== null) {
            $sql .= " OFFSET {$this->offset}";
        }
        return $sql;
    }
}

使用查询构建器

<?php
declare(strict_types=1);

$db = new SQLite3(__DIR__ . '/app.db');
$db->enableExceptions(true);

// 查询构建器
$products = (new SQLiteQueryBuilder($db, 'products'))
    ->where('price', '>', 1000)
    ->whereIn('category', ['手机', '笔记本'])
    ->orderBy('price', 'DESC')
    ->limit(10)
    ->get();

foreach ($products as $product) {
    echo "{$product['name']} - ¥{$product['price']}\n";
}

// 计数
$count = (new SQLiteQueryBuilder($db, 'products'))
    ->where('stock', '>', 0)
    ->count();
echo "有货产品: {$count} 种\n";

// 插入
$id = (new SQLiteQueryBuilder($db, 'products'))
    ->insert([
        'name' => 'iMac',
        'price' => 10999.00,
        'stock' => 30,
        'category' => '桌面',
    ]);
echo "插入ID: {$id}\n";

批量数据导入

<?php
declare(strict_types=1);

class BatchImporter
{
    private SQLite3 $db;
    private int $batchSize;

    public function __construct(SQLite3 $db, int $batchSize = 1000)
    {
        $this->db = $db;
        $this->batchSize = $batchSize;
    }

    /**
     * 事务中逐条执行预处理语句 — 极速方式
     */
    public function fastInsert(string $table, array $rows): int
    {
        if (empty($rows)) {
            return 0;
        }

        $columns = array_keys($rows[0]);
        $placeholders = implode(', ', array_fill(0, count($columns), '?'));

        $sql = sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $table,
            implode(', ', $columns),
            $placeholders
        );

        $this->db->exec('BEGIN IMMEDIATE');
        try {
            $stmt = $this->db->prepare($sql);
            foreach ($rows as $row) {
                foreach (array_values($row) as $i => $value) {
                    $stmt->bindValue($i + 1, $value);
                }
                $stmt->execute();
                $stmt->reset();
            }
            $count = $this->db->changes();
            $this->db->exec('COMMIT');
            return $count;
        } catch (Exception $e) {
            $this->db->exec('ROLLBACK');
            throw $e;
        }
    }

    /**
     * 从 CSV 文件导入
     */
    public function importFromCsv(string $table, string $csvPath): int
    {
        $handle = fopen($csvPath, 'r');
        if ($handle === false) {
            throw new RuntimeException("无法打开 CSV 文件: {$csvPath}");
        }

        $headers = fgetcsv($handle);
        if ($headers === false) {
            fclose($handle);
            throw new RuntimeException("CSV 文件为空");
        }

        $rows = [];
        while (($row = fgetcsv($handle)) !== false) {
            $record = [];
            foreach ($headers as $i => $col) {
                $record[$col] = $row[$i] ?? '';
            }
            $rows[] = $record;
        }
        fclose($handle);

        return $this->fastInsert($table, $rows);
    }
}

注意事项

SQL 注入防护

<?php
// 错误 — 直接拼接 SQL（易受注入攻击）
$name = $_GET['name'] ?? '';
$result = $db->query("SELECT * FROM products WHERE name = '{$name}'");

// 正确 — 使用预处理语句
$stmt = $db->prepare('SELECT * FROM products WHERE name = :name');
$stmt->bindValue(':name', $_GET['name'] ?? '');
$result = $stmt->execute();

// 最低保障 — querySingle 不支持预处理，需手动转义
$safe = SQLite3::escapeString($_GET['name'] ?? '');
$count = $db->querySingle("SELECT COUNT(*) FROM products WHERE name = '{$safe}'");

永远不要拼接 SQL

即使用 SQLite3::escapeString() 转义，也应优先使用预处理语句。预处理语句更安全、更清晰、更高效（语句可复用）。

结果集内存管理

<?php
// 结果集会保持数据库文件锁定
// 务必遍历完或手动关闭
$result = $db->query('SELECT * FROM large_table');

// 方式1: 遍历完自动释放
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // 处理数据
}

// 方式2: 手动关闭（中途退出时）
$result->finalize(); // PHP 8.1+
unset($result);     // 销毁结果对象

事务中的查询注意事项

<?php
// 正确 — 事务中使用同一连接
$db->exec('BEGIN');
$db->exec("INSERT INTO products (name) VALUES ('A')");
$count = $db->querySingle('SELECT COUNT(*) FROM products'); // 可以看到未提交数据
$db->exec('COMMIT');

// 注意: 不同连接看到的是已提交数据
// SQLite 文件级事务，跨连接不可见未提交更改

最佳实践

1. 预处理语句复用

<?php
// 重复执行同一预处理语句 — 性能更优
$stmt = $db->prepare('SELECT * FROM products WHERE category = :cat');

$categories = ['手机', '笔记本', '平板', '配件'];
foreach ($categories as $cat) {
    $stmt->bindValue(':cat', $cat);
    $result = $stmt->execute();
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        // 处理数据
    }
    $stmt->reset(); // 重置以复用
}
unset($stmt);

2. SQLite 特有函数利用

<?php
$sql = '
    SELECT
        name,
        price,
        ROUND(price * 1.1, 2) as price_with_tax,
        UPPER(name) as upper_name,
        LENGTH(name) as name_length,
        datetime(created_at, "+1 day") as tomorrow,
        strftime("%Y-%m", created_at) as month,
        GROUP_CONCAT(name, ", ") as all_names,
        CAST(price AS INTEGER) as int_price
    FROM products
    GROUP BY category
';

3. UPSERT（INSERT OR REPLACE）

<?php
// SQLite 3.24+ 支持 UPSERT
$db->exec('
    INSERT INTO products (id, name, price, stock)
    VALUES (1, "iPhone 15", 7999, 100)
    ON CONFLICT(id) DO UPDATE SET
        name = excluded.name,
        price = excluded.price,
        updated_at = datetime("now")
');

参考链接

• PHP 手册: SQLite3Stmt
• PHP 手册: SQLite3Result
• SQLite 语言文档
• SQLite UPSERT 支持
• SQLite 内置函数