PHP如何高效操作SQLite数据库？-建站教程-锦华智联科技

PHP SQLite 完整教程

SQLite 是一个轻量级的、无服务器的、自包含的 SQL 数据库引擎，它被直接嵌入到 PHP 中，这意味着你无需安装或配置任何额外的服务器或扩展（只需确保 PHP SQLite 扩展已启用），这使得 SQLite 成为小型项目、原型开发、测试和需要便携式数据存储应用的理想选择。

第一步：准备工作 (检查环境)
第二步：创建和连接数据库
第三步：执行 SQL 语句 (创建表)
第四步：数据操作 (CRUD)
- 创建数据
- 读取数据
- 更新数据
- 删除数据
第五步：预处理语句 (防 SQL 注入)
第六步：事务处理
第七步：错误处理
第八步：高级主题
- 获取最后插入的 ID
- 获取受影响的行数
- 使用 fetchAll() 获取所有结果
完整示例代码
最佳实践和总结

第一步：准备工作 (检查环境)

在开始之前，请确保你的 PHP 环境已经启用了 SQLite 扩展。

如何检查？

创建一个名为 info.php 的文件,内容如下：

<?php
phpinfo();
?>

在你的浏览器中访问这个文件（http://localhost/info.php），然后按 Ctrl+F 搜索 sqlite。

你应该会看到类似以下的输出，这表示 SQLite 扩展已启用：

如果未启用怎么办？

你需要修改你的 php.ini 文件。

Windows: 取消注释这行（删除前面的分号）：
```
extension=php_sqlite3.dll
```
Linux/macOS (使用源码编译或包管理器安装):
```
extension=sqlite3
```

修改后，重启你的 Web 服务器（如 Apache 或 Nginx）。

第二步：创建和连接数据库

在 SQLite 中，数据库就是一个普通的文件，如果文件不存在，SQLite 会自动创建它。

使用 new SQLite3() 来创建或打开一个数据库连接。

<?php
// 数据库文件名
$dbFile = 'my_database.db';
try {
    // 创建或打开一个 SQLite3 数据库连接
    // my_database.db 文件不存在，它会自动创建
    $db = new SQLite3($dbFile);
    echo "成功连接到数据库: " . $dbFile;
} catch (Exception $e) {
    // 如果连接失败，抛出异常
    die("无法连接到数据库: " . $e->getMessage());
}
?>

运行此脚本后，你会发现在同一目录下生成了一个 my_database.db 文件，这就是你的 SQLite 数据库。

第三步：执行 SQL 语句 (创建表)

连接数据库后，我们就可以执行 SQL 语句来创建表了，使用 exec() 方法来执行不返回结果集的 SQL 语句（如 CREATE, INSERT, UPDATE, DELETE）。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 创建一个 'users' 表
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";
// 执行 SQL 语句
if ($db->exec($sql)) {
    echo "表 'users' 创建成功或已存在。";
} else {
    echo "创建表失败: " . $db->lastErrorMsg();
}
// 关闭数据库连接
$db->close();
?>

代码解释:

CREATE TABLE IF NOT EXISTS: 如果表不存在则创建,避免重复创建时报错。
id INTEGER PRIMARY KEY AUTOINCREMENT: 创建一个自增的主键。
name TEXT NOT NULL: name 列是文本类型,且不能为空。
email TEXT NOT NULL UNIQUE: email 列是文本类型，不能为空,且值必须唯一。
$db->exec($sql): 执行 SQL。
$db->lastErrorMsg(): 如果执行失败,获取错误信息。
$db->close(): 关闭数据库连接，这是一个好习惯，虽然脚本结束时 PHP 会自动关闭。

第四步：数据操作

创建数据

使用 exec() 或 query() 都可以执行 INSERT 语句。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 方法一: 使用 exec()
$db->exec("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')");
echo "Alice 插入成功。<br>";
// 方法二: 使用 query (更灵活，可以获取最后插入的ID)
$sql = "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')";
$result = $db->query($sql);
if ($result) {
    echo "Bob 插入成功。<br>";
}
$db->close();
?>

读取数据

读取数据需要使用 query() 方法，它会返回一个 SQLite3Result 对象,然后你可以遍历这个对象来获取每一行数据。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
$sql = "SELECT id, name, email FROM users";
$result = $db->query($sql);
echo "<h2>用户列表:</h2>";
echo "<ul>";
// 遍历结果集
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    // fetchArray(SQLITE3_ASSOC) 以关联数组形式返回行
    // 键名是列名
    echo "<li>ID: " . $row['id'] . ", 姓名: " . $row['name'] . ", 邮箱: " . $row['email'] . "</li>";
}
echo "</ul>";
// 释放结果集和关闭连接
$result->finalize();
$db->close();
?>

fetchArray() 的模式:

SQLITE3_ASSOC: 返回关联数组（推荐，可读性好）。
SQLITE3_NUM: 返回数字索引数组。
SQLITE3_BOTH: 同时返回关联和数字索引数组（默认）。

更新数据

更新数据使用 UPDATE 语句，通常配合 WHERE 子句来指定要更新的行。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 将 Alice 的邮箱更新为 'alice.new@example.com'
$sql = "UPDATE users SET email = 'alice.new@example.com' WHERE name = 'Alice'";
if ($db->exec($sql)) {
    echo "Alice 的邮箱已更新。";
} else {
    echo "更新失败: " . $db->lastErrorMsg();
}
$db->close();
?>

删除数据

删除数据使用 DELETE 语句，同样需要 WHERE 子句来指定要删除的行。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 删除名为 Bob 的用户
$sql = "DELETE FROM users WHERE name = 'Bob'";
if ($db->exec($sql)) {
    echo "Bob 已被删除。";
} else {
    echo "删除失败: " . $db->lastErrorMsg();
}
$db->close();
?>

第五步：预处理语句 (防 SQL 注入)

直接拼接 SQL 字符串是极其危险的，会导致 SQL 注入攻击。永远不要这样做：

// 危险！不要使用！
$name = "Alice'; DROP TABLE users; --";
$sql = "SELECT * FROM users WHERE name = '$name'";

正确的做法是使用预处理语句，它将 SQL 命令和数据分开处理,从而有效防止注入。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 要查询的用户名
$searchName = "Alice";
// 1. 准备 SQL 语句，使用 ? 作为占位符
$stmt = $db->prepare("SELECT * FROM users WHERE name = ?");
// 2. 绑定参数到占位符
// 's' 表示字符串 (string)
// 如果是整数，使用 'i'
$stmt->bindValue(1, $searchName, SQLITE3_TEXT);
// 3. 执行预处理语句
$result = $stmt->execute();
// 4. 获取结果
echo "<h2>查询结果:</h2>";
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    print_r($row);
}
// 5. 释放资源
$result->finalize();
$stmt->close();
$db->close();
?>

第六步：事务处理

事务是一组 SQL 语句，它们要么全部成功执行，要么全部失败回滚，这对于保证数据一致性至关重要（银行转账）。

使用 beginTransaction(), commit(), 和 rollback()。

<?php
$dbFile = 'my_database.db';
$db = new SQLite3($dbFile);
// 开始一个事务
$db->beginTransaction();
try {
    // 执行多个操作
    $db->exec("INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com')");
    $db->exec("UPDATE users SET email = 'charlie.updated@example.com' WHERE name = 'Charlie'");
    // 如果所有操作都成功，提交事务
    $db->commit();
    echo "事务成功提交！";
} catch (Exception $e) {
    // 如果任何一个操作失败，回滚事务
    $db->rollback();
    echo "事务失败，已回滚: " . $e->getMessage();
}
$db->close();
?>

第七步：错误处理

SQLite3 提供了几个方法来处理错误：

$db->lastErrorMsg(): 获取最后一次操作的错误信息。
$db->lastErrorCode(): 获取最后一次操作的错误代码。
$db->busyTimeout(): 设置一个超时时间，当数据库被锁定时（另一个进程正在写入）,等待多久再尝试。

一个健壮的脚本应该总是检查操作是否成功。

if (!$db->exec($sql)) {
    die("SQL 错误: " . $db->lastErrorMsg());
}

高级主题

获取最后插入的 ID

在执行 INSERT 操作后，可以使用 lastInsertRowID() 获取新行的 ID。

$db->exec("INSERT INTO users (name, email) VALUES ('David', 'david@example.com')");
$lastId = $db->lastInsertRowID();
echo "最后插入的 ID 是: " . $lastId;

获取受影响的行数

在执行 UPDATE 或 DELETE 后，可以使用 changes() 获取受影响的行数。

$db->exec("UPDATE users SET name = 'Dave' WHERE name = 'David'");
$affectedRows = $db->changes();
echo "有 " . $affectedRows . " 行被更新。";

使用 `fetchAll()`

SQLite3Result 对象没有原生的 fetchAll() 方法,但你可以轻松地自己实现一个。

function fetchAll($result) {
    $data = [];
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        $data[] = $row;
    }
    return $data;
}
// 使用示例
$sql = "SELECT * FROM users";
$result = $db->query($sql);
$allUsers = fetchAll($result);
print_r($allUsers);

完整示例代码

这是一个将以上所有知识点整合在一起的完整示例。

<?php
// 数据库配置
$dbFile = 'my_database.db';
// 创建或连接数据库
try {
    $db = new SQLite3($dbFile);
    echo "数据库连接成功。<br>";
} catch (Exception $e) {
    die("数据库连接失败: " . $e->getMessage());
}
// 1. 创建表
$sql = "CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
)";
if (!$db->exec($sql)) {
    die("创建表失败: " . $db->lastErrorMsg());
}
echo "表 'products' 准备就绪。<br><hr>";
// 2. 使用预处理语句插入多条数据 (事务)
$db->beginTransaction();
$insertStmt = $db->prepare("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)");
$products = [
    ['Laptop', 1200.50, 10],
    ['Mouse', 25.00, 50],
    ['Keyboard', 75.75, 30]
];
try {
    foreach ($products as $product) {
        $insertStmt->bindValue(1, $product[0], SQLITE3_TEXT);
        $insertStmt->bindValue(2, $product[1], SQLITE3_FLOAT);
        $insertStmt->bindValue(3, $product[2], SQLITE3_INTEGER);
        $insertStmt->execute();
    }
    $db->commit();
    echo "成功插入 " . count($products) . " 个产品。<br>";
} catch (Exception $e) {
    $db->rollback();
    echo "插入产品失败: " . $e->getMessage() . "<br>";
}
$insertStmt->finalize();
echo "<hr>";
// 3. 查询并显示所有产品
echo "<h2>所有产品列表:</h2>";
$sql = "SELECT id, name, price, stock FROM products";
$result = $db->query($sql);
echo "<table border='1' cellpadding='5'>";
echo "<tr><th>ID</th><th>Name</th><th>Price</th><th>Stock</th></tr>";
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . htmlspecialchars($row['name']) . "</td>";
    echo "<td>" . $row['price'] . "</td>";
    echo "<td>" . $row['stock'] . "</td>";
    echo "</tr>";
}
echo "</table>";
$result->finalize();
echo "<hr>";
// 4. 使用预处理语句更新一个产品
$updateStmt = $db->prepare("UPDATE products SET price = ? WHERE name = ?");
$newPrice = 22.50;
$productName = 'Mouse';
$updateStmt->bindValue(1, $newPrice, SQLITE3_FLOAT);
$updateStmt->bindValue(2, $productName, SQLITE3_TEXT);
if ($updateStmt->execute()) {
    echo "已将 '{$productName}' 的价格更新为 {$newPrice}。<br>";
    echo "受影响的行数: " . $db->changes() . "<br>";
} else {
    echo "更新失败: " . $db->lastErrorMsg() . "<br>";
}
$updateStmt->finalize();
echo "<hr>";
// 5. 获取最后插入的产品ID
$lastProductId = $db->lastInsertRowID();
echo "最后插入的产品ID是: " . $lastProductId . "<br>";
// 关闭数据库连接
$db->close();
echo "<br>数据库连接已关闭。";
?>

最佳实践和总结

始终使用预处理语句: 这是防止 SQL 注入的黄金法则,无论你的应用大小如何。
总是关闭连接和释放资源: 虽然脚本结束时会自动处理，但显式地关闭 $db 和 $stmt，以及调用 $result->finalize() 是一个好习惯,尤其是在长时间运行的应用或循环中。
使用事务: 对于需要多个步骤才能完成的操作（如批量插入、转账）,使用事务可以保证原子性。
妥善处理错误: 检查每个数据库操作的返回值,并在出错时提供有用的反馈或记录日志。
何时使用 SQLite:
- 适合: 小型网站、移动应用本地存储、桌面应用数据、原型开发、数据分析和测试。
- 不适合: 高并发的 Web 应用（如大型电商、社交网络），因为它是文件级的锁，高并发写入性能会很差，这些场景应该使用 MySQL, PostgreSQL, 或 MariaDB 等客户端/服务器数据库。

希望这份教程能帮助你掌握 PHP 和 SQLite 的使用！

PHP如何高效操作SQLite数据库？