SQLite - PHP

SQLite 和 PHP：现代方法

本章将指导您在现代 PHP 应用程序中使用 SQLite。我们将重点介绍当前的最佳实践，包括使用预处理语句（prepared statements）进行安全编码和健壮的错误处理。

前置条件与设置

现代 PHP 版本（PHP 7.4+ 及更高版本）通常默认包含 SQLite3 扩展（extension）。要验证它是否已启用，您可以在终端中运行以下命令：

php -m | grep sqlite3

如果未列出，您可能需要在 php.ini 文件中通过取消注释（删除开头的分号）行 extension=sqlite3 来启用它。Windows 用户需要取消注释 extension=php_sqlite3.dll。

为了管理项目依赖，即使对于没有外部库的项目，使用 Composer 也是一种最佳实践。它提供了结构化的项目设置和可靠的自动加载。

PHP SQLite3 类的主要方法

PHP 的 SQLite3 类提供了面向对象的 SQLite 接口。尽管有许多方法，但现代开发工作流主要依赖少数几个关键方法来实现安全高效的数据库交互。我们将使用基于异常的错误模型（exception-based error model），它比每次调用后检查返回值更简洁。

方法	描述
1	public SQLite3::__construct(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = '') 打开或创建一个 SQLite 数据库。失败时抛出 Exception 异常。
2	public SQLite3::enableExceptions(bool $enable) 对现代错误处理至关重要。当设置为 true 时，SQLite3 对象将在出错时抛出异常，而不是返回 false，从而简化您的代码。
3	public SQLite3::prepare(string $query): SQLite3Stmt|false 准备一个 SQL 语句以执行。这是使用预处理语句（prepared statements）防止 SQL 注入（SQL injection）的第一步。返回一个 SQLite3Stmt 对象。
4	public SQLite3Statement::bindValue(string|int $param, mixed $value, int $type): bool 将值绑定到预处理语句中的参数。参数可以是命名参数（例如 :name）或位置参数（?）。
5	public SQLite3Statement::execute(): SQLite3Result|false 执行预处理语句。对于 SELECT 查询，它返回一个 SQLite3Result 对象。对于其他查询，成功时返回 true。
6	public SQLite3Result::fetchArray(int $mode = SQLITE3_BOTH): array|false 从结果集中获取一行。SQLITE3_ASSOC 返回关联数组，SQLITE3_NUM 返回数字数组。
7	public SQLite3::lastInsertRowID(): int 返回最近一次 INSERT 语句的行 ID。
8	public SQLite3::changes(): int 返回最近一次 UPDATE 或 DELETE 语句影响的行数。
9	public SQLite3::close(): bool 关闭数据库连接。虽然 PHP 在脚本退出时会处理此操作，但对于长时间运行的脚本来说，这是一个好习惯。

连接到数据库

这是一种现代、健壮的 SQLite 数据库连接方式。我们使用 try...catch 块来优雅地处理潜在的连接错误。

<?php
declare(strict_types=1);

$dbFile = __DIR__ . '/inventory.db';

try {
    // 打开数据库文件。如果文件不存在，它将被创建。
    $db = new SQLite3($dbFile);

    // 启用异常用于错误处理
    $db->enableExceptions(true);

    echo "Connected to database 'inventory.db' successfully.\n";

} catch (Exception $e) {
    // 在生产环境中，应记录错误而不是向用户显示。
    error_log('Database connection failed: ' . $e->getMessage());
    // 发送通用错误响应。
    http_response_code(500);
    echo "Error: Could not connect to the database.\n";
    exit; // 停止脚本执行
}

运行此脚本会在同一目录下创建一个名为 inventory.db 的文件并打印成功消息。请注意，Web 服务器（例如 Apache、Nginx）必须对数据库文件存储的目录具有写入权限。

创建表

我们将使用 exec() 方法来执行简单的一次性查询，这些查询不返回结果，也不涉及外部数据。它适用于模式（schema）设置。

<?php
// （包含上一步的连接代码）

try {
    $sql = 'CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL,
        last_updated TEXT NOT NULL
    );';

    $db->exec($sql);
    echo "Table 'products' created or already exists.\n";

} catch (Exception $e) {
    error_log('Table creation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not create table.\n";
    exit;
}

INSERT 操作（使用预处理语句）

为了安全地插入数据，我们必须使用预处理语句（prepared statements）。这种做法通过将 SQL 命令与数据分离来防止 SQL 注入（SQL injection）攻击。我们使用命名参数（例如 :name）以提高清晰度。

<?php
// （包含连接代码）

try {
    $sql = 'INSERT INTO products (name, quantity, price, last_updated)
            VALUES (:name, :quantity, :price, :last_updated)';

    $stmt = $db->prepare($sql);

    // 第一个产品的数据
    $stmt->bindValue(':name', 'Laptop Pro', SQLITE3_TEXT);
    $stmt->bindValue(':quantity', 15, SQLITE3_INTEGER);
    $stmt->bindValue(':price', 1299.99, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->execute();
    echo "Record created successfully (ID: " . $db->lastInsertRowID() . ").\n";

    // 第二个产品的数据
    $stmt->bindValue(':name', 'Wireless Mouse', SQLITE3_TEXT);
    $stmt->bindValue(':quantity', 200, SQLITE3_INTEGER);
    $stmt->bindValue(':price', 25.50, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->execute();
    echo "Record created successfully (ID: " . $db->lastInsertRowID() . ").\n";

} catch (Exception $e) {
    error_log('Insert operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not insert record.\n";
    exit;
}

SELECT 操作

为了获取数据，我们执行 SELECT 查询。结果是一个 SQLite3Result 对象，我们可以对其进行迭代以获取每一行。

<?php
// （包含连接代码）

try {
    $sql = 'SELECT id, name, quantity, price FROM products';
    $result = $db->query($sql);

    echo "--- Product Inventory ---\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: {$row['id']}\n";
        echo "Name: {$row['name']}\n";
        echo "Quantity: {$row['quantity']}\n";
        echo "Price: ${$row['price']}\n\n";
    }
    echo "Operation done successfully.\n";

} catch (Exception $e) {
    error_log('Select operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not fetch records.\n";
    exit;
}

执行上述程序后，将产生以下结果：

--- Product Inventory ---
ID: 1
Name: Laptop Pro
Quantity: 15
Price: $1299.99

ID: 2
Name: Wireless Mouse
Quantity: 200
Price: $25.5

Operation done successfully.

UPDATE 操作

更新记录同样需要预处理语句来安全地处理 WHERE 子句的值。changes() 方法会告诉我们有多少行受到了影响。

<?php
// （包含连接代码）

try {
    $sql = 'UPDATE products
            SET price = :price, last_updated = :last_updated
            WHERE name = :name';

    $stmt = $db->prepare($sql);
    $stmt->bindValue(':price', 1250.00, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->bindValue(':name', 'Laptop Pro', SQLITE3_TEXT);

    $stmt->execute();

    $changes = $db->changes();
    echo "{$changes} record(s) updated successfully.\n";

} catch (Exception $e) {
    error_log('Update operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not update record.\n";
    exit;
}

运行此操作后，后续的 SELECT 查询将显示 ‘Laptop Pro’ 的更新价格。

DELETE 操作

删除操作遵循相同的安全模式。使用预处理语句来指定要删除的记录。

<?php
// （包含连接代码）

try {
    $sql = 'DELETE FROM products WHERE id = :id';

    $stmt = $db->prepare($sql);
    $stmt->bindValue(':id', 2, SQLITE3_INTEGER); // 删除 'Wireless Mouse'
    $stmt->execute();

    $changes = $db->changes();
    echo "{$changes} record(s) deleted successfully.\n";

} catch (Exception $e) {
    error_log('Delete operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not delete record.\n";
    exit;
} finally {
    // 始终关闭连接
    if (isset($db)) {
        $db->close();
        echo "Database connection closed.\n";
    }
}

后续步骤和深入学习

您现在已经掌握了使用现代安全实践在 PHP 中进行 SQLite 数据库 CRUD（创建、读取、更新、删除）操作的基础知识。对于更大型的应用程序，可以考虑探索：

• PDO (PHP Data Objects)： PHP 内置的数据库抽象层。使用 PDO_SQLite 提供了接口，使得将来切换到其他数据库（如 MySQL 或 PostgreSQL）时，只需最少的代码更改。
• 对象关系映射器（ORMs）： 像 Doctrine 或 Eloquent（来自 Laravel 框架）这样的库允许您使用 PHP 对象与数据库交互，完全抽象掉 SQL。这可以显著加速复杂应用程序的开发。