PHP小码农

Appearance

SQLite3 连接与配置 ​

概述 ​

SQLite 是一个轻量级的嵌入式关系型数据库引擎，无需独立的服务器进程，整个数据库存储在单个磁盘文件中。PHP 通过 SQLite3 扩展（自 PHP 5.3 起内置）提供对 SQLite 的支持，适合中小型应用、单元测试、桌面应用和嵌入式场景。

核心优势

  • 零配置 — 无需安装数据库服务器
  • 无服务器 — 数据库就是一个文件
  • 事务支持 — 完整的 ACID 事务
  • 跨平台 — 数据库文件可在不同操作系统间复制

基础概念 ​

SQLite3 vs PDO_SQLite ​

PHP 提供两种方式操作 SQLite：

特性SQLite3 扩展PDO_SQLite
API 风格面向对象PDO 标准
命名参数:name:name 和 ?
扩展名称sqlite3pdo_sqlite
错误处理异常/返回值PDO 错误模式
适用场景简单项目已使用 PDO 的项目

数据库文件 vs 内存数据库 ​

类型连接方式数据持久性适用场景
文件数据库new SQLite3('path/to/db.sqlite')永久保存生产环境
内存数据库new SQLite3(':memory:')进程结束即消失测试、缓存、临时计算
临时文件new SQLite3('') 使用临时表会话内中间处理

语法与代码 ​

创建文件数据库连接 ​

php
<?php
declare(strict_types=1);

try {
    // 创建或打开数据库文件
    $db = new SQLite3(__DIR__ . '/app.db');

    // 开启异常模式（PHP 8.1+）
    $db->enableExceptions(true);

    echo "数据库连接成功\n";
    echo "SQLite 版本: " . $db->querySingle('SELECT sqlite_version()') . "\n";

} catch (Exception $e) {
    die("数据库连接失败: " . $e->getMessage());
}

内存数据库连接 ​

php
<?php
declare(strict_types=1);

// 内存数据库 — 适合单元测试和临时计算
$db = new SQLite3(':memory:');
$db->enableExceptions(true);

// 创建表并插入测试数据
$db->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');

$stmt = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->bindValue(':name', '测试用户');
$stmt->bindValue(':email', 'test@example.com');
$stmt->execute();

// 验证数据
$name = $db->querySingle('SELECT name FROM users WHERE id = 1');
echo "用户名: {$name}\n"; // 输出: 测试用户

// 关闭连接 — 内存数据随之消失
$db->close();

内存数据库生命周期

内存数据库仅在 SQLite3 对象存活期间存在。所有 new SQLite3(':memory:') 实例创建的是各自独立的内存数据库，彼此不共享数据。

打开选项与标志位 ​

php
<?php
declare(strict_types=1);

// SQLite3 打开标志常量
$flags = [
    'SQLITE3_OPEN_READWRITE'  => SQLite3::OPEN_READWRITE,   // 读写模式
    'SQLITE3_OPEN_CREATE'     => SQLite3::OPEN_CREATE,       // 不存在时创建
    'SQLITE3_OPEN_READONLY'    => SQLite3::OPEN_READONLY,     // 只读模式
];

// 只读模式打开（适合报表查询）
$readOnlyDb = new SQLite3('/data/reports.sqlite', SQLITE3_OPEN_READONLY);

// 读写模式 + 不自动创建
$readWriteDb = new SQLite3('/data/app.sqlite', SQLITE3_OPEN_READWRITE);

连接配置（PRAGMA） ​

php
<?php
declare(strict_types=1);

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

// 开启 WAL 模式（Write-Ahead Logging）
$db->exec('PRAGMA journal_mode = WAL;');

// 设置繁忙超时（毫秒）— 等待锁释放
$db->exec('PRAGMA busy_timeout = 5000;');

// 开启外键约束
$db->exec('PRAGMA foreign_keys = ON;');

// 设置缓存大小（单位：页，默认一页 4KB）
$db->exec('PRAGMA cache_size = -8000;'); // 32MB 负数表示 KB

// 设置同步模式
// FULL — 最安全，每次写入都 fsync
// NORMAL — 安全与性能平衡
// OFF — 最快，但断电可能丢数据
$db->exec('PRAGMA synchronous = NORMAL;');

// 设置临时存储位置
$db->exec('PRAGMA temp_store = MEMORY;');

// 查看当前配置
$jMode = $db->querySingle('PRAGMA journal_mode;');
echo "Journal 模式: {$jMode}\n";

WAL 模式优势

WAL（Write-Ahead Logging）模式允许读写并发操作，在 Web 应用中显著减少 "database is locked" 错误。建议所有生产环境开启。

实战示例 ​

SQLite 数据库管理类 ​

php
<?php
declare(strict_types=1);

class SQLiteDatabase
{
    private ?SQLite3 $connection = null;
    private string $databasePath;
    private bool $inMemory;
    private bool $walEnabled;

    public function __construct(
        string $path,
        bool $inMemory = false,
        bool $walEnabled = true,
        int $busyTimeout = 5000
    ) {
        $this->inMemory = $inMemory;
        $this->walEnabled = $walEnabled;

        if ($inMemory) {
            $this->databasePath = ':memory:';
        } else {
            $dir = dirname($path);
            if (!is_dir($dir)) {
                mkdir($dir, 0755, true);
            }
            $this->databasePath = $path;
        }

        $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE;

        $this->connection = new SQLite3($this->databasePath, $flags);
        $this->connection->enableExceptions(true);

        // 应用基础配置
        $this->connection->busyTimeout($busyTimeout);
        $this->connection->exec('PRAGMA foreign_keys = ON;');
        $this->connection->exec('PRAGMA synchronous = NORMAL;');

        if ($walEnabled && !$inMemory) {
            $this->connection->exec('PRAGMA journal_mode = WAL;');
        }
    }

    public function exec(string $sql): void
    {
        $this->connection->exec($sql);
    }

    public function prepare(string $sql): SQLite3Stmt
    {
        return $this->connection->prepare($sql);
    }

    public function query(string $sql): SQLite3Result|false
    {
        return $this->connection->query($sql);
    }

    public function querySingle(string $sql, bool $entireRow = false): mixed
    {
        return $this->connection->querySingle($sql, $entireRow);
    }

    public function lastInsertRowID(): int
    {
        return $this->connection->lastInsertRowID();
    }

    public function changes(): int
    {
        return $this->connection->changes();
    }

    public function beginTransaction(): void
    {
        $this->connection->exec('BEGIN IMMEDIATE;');
    }

    public function commit(): void
    {
        $this->connection->exec('COMMIT;');
    }

    public function rollback(): void
    {
        $this->connection->exec('ROLLBACK;');
    }

    public function backup(string $destinationPath): void
    {
        $dest = new SQLite3($destinationPath, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
        $this->connection->backup($dest);
        $dest->close();
    }

    public function close(): void
    {
        if ($this->connection !== null) {
            $this->connection->close();
            $this->connection = null;
        }
    }

    public function __destruct()
    {
        $this->close();
    }

    public function getDatabasePath(): string
    {
        return $this->databasePath;
    }

    public function getConnection(): SQLite3
    {
        return $this->connection;
    }
}

数据库初始化与迁移 ​

php
<?php
declare(strict_types=1);

class DatabaseMigrator
{
    private SQLiteDatabase $db;

    public function __construct(SQLiteDatabase $db)
    {
        $this->db = $db;
    }

    public function initialize(): void
    {
        $this->db->beginTransaction();

        try {
            // 创建版本跟踪表
            $this->db->exec('
                CREATE TABLE IF NOT EXISTS migrations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    executed_at TEXT NOT NULL DEFAULT (datetime("now"))
                )
            ');

            // 创建用户表
            $this->db->exec('
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT NOT NULL UNIQUE,
                    email TEXT NOT NULL UNIQUE,
                    password_hash TEXT NOT NULL,
                    status TEXT DEFAULT "active",
                    created_at TEXT DEFAULT (datetime("now")),
                    updated_at TEXT DEFAULT (datetime("now"))
                )
            ');

            // 创建文章表
            $this->db->exec('
                CREATE TABLE IF NOT EXISTS posts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    title TEXT NOT NULL,
                    content TEXT DEFAULT "",
                    status TEXT DEFAULT "draft",
                    published_at TEXT,
                    created_at TEXT DEFAULT (datetime("now")),
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            ');

            // 创建索引
            $this->db->exec('CREATE INDEX IF NOT EXISTS idx_posts_user ON posts(user_id);');
            $this->db->exec('CREATE INDEX IF NOT EXISTS idx_posts_status ON posts(status);');

            // 记录迁移
            $stmt = $this->db->prepare(
                'INSERT OR IGNORE INTO migrations (name) VALUES ("init")'
            );
            $stmt->execute();

            $this->db->commit();
            echo "数据库初始化完成\n";

        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function migrate(string $name, string $sql): void
    {
        // 检查是否已执行
        $stmt = $this->db->prepare('SELECT COUNT(*) as cnt FROM migrations WHERE name = :name');
        $stmt->bindValue(':name', $name);
        $result = $stmt->execute();
        $row = $result->fetchArray(SQLITE3_ASSOC);

        if ($row['cnt'] > 0) {
            echo "迁移 {$name} 已执行，跳过\n";
            return;
        }

        $this->db->beginTransaction();
        try {
            $this->db->exec($sql);
            $stmt = $this->db->prepare(
                'INSERT INTO migrations (name) VALUES (:name)'
            );
            $stmt->bindValue(':name', $name);
            $stmt->execute();
            $this->db->commit();
            echo "迁移 {$name} 执行成功\n";
        } catch (Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }
}

使用示例 ​

php
<?php
declare(strict_types=1);

// 创建数据库连接
$db = new SQLiteDatabase(__DIR__ . '/data/blog.db');

// 初始化数据库
$migrator = new DatabaseMigrator($db);
$migrator->initialize();

// 添加新迁移
$migrator->migrate('add_comments', '
    CREATE TABLE IF NOT EXISTS comments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        post_id INTEGER NOT NULL,
        author TEXT NOT NULL,
        content TEXT NOT NULL,
        created_at TEXT DEFAULT (datetime("now")),
        FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
    );
');

// 备份数据库
$db->backup(__DIR__ . '/backups/blog_' . date('Y-m-d') . '.sqlite');

echo "数据库路径: " . $db->getDatabasePath() . "\n";

注意事项 ​

数据库锁定机制 ​

SQLite 使用文件级锁，在写入时会锁定整个数据库文件：

操作锁类型其他进程能否读取其他进程能否写入
SELECTSHARED可以不可以
BEGIN IMMEDIATERESERVED可以不可以
COMMIT/写入EXCLUSIVE不可以不可以

并发写入限制

SQLite 不支持高并发写入。WAL 模式下可以并发读写，但同一时刻仍只有一个写入者。对于高并发写入场景，请使用 MySQL/PostgreSQL。

常见错误处理 ​

php
<?php
// 错误1: database is locked
// 原因: 另一个进程正在写入
// 解决: 增加 busy_timeout
$db->busyTimeout(5000); // 等待5秒

// 错误2: unable to open database file
// 原因: 目录不存在或无权限
$dir = dirname($path);
if (!is_dir($dir)) {
    mkdir($dir, 0755, true);
}

// 错误3: no such table
// 原因: 未初始化数据库或迁移未执行
// 解决: 实现迁移机制（如上示例）

// 错误4: UNIQUE constraint failed
// 原因: 插入了重复的唯一键值
try {
    $stmt = $db->prepare('INSERT INTO users (username) VALUES (:name)');
    $stmt->bindValue(':name', 'existing_user');
    $stmt->execute();
} catch (Exception $e) {
    if (str_contains($e->getMessage(), 'UNIQUE constraint')) {
        echo "用户名已存在\n";
    }
}

SQLite 的数据类型亲和性 ​

SQLite 使用动态类型系统，支持类型亲和性（Type Affinity）：

亲和性映射类型说明
INTEGERINT, TINYINT, BIGINT整数
TEXTVARCHAR, TEXT, CHAR文本
REALFLOAT, DOUBLE, REAL浮点数
BLOBBLOB, 二进制二进制大对象
NUMERICNUMERIC, DECIMAL数值（优先 INTEGER 或 REAL）

类型注意事项

SQLite 不强制列类型约束。你可以在 INTEGER 列中存入文本，但在查询和排序时可能导致意外结果。建议在应用层做类型验证。

最佳实践 ​

1. 生产环境配置清单 ​

php
<?php
// 推荐的 PRAGMA 配置（按顺序）
$pragmas = [
    'journal_mode'  => 'WAL',           // 读写并发
    'busy_timeout'  => '5000',          // 等待锁5秒
    'foreign_keys'  => 'ON',            // 开启外键
    'synchronous'   => 'NORMAL',        // 安全与性能平衡
    'cache_size'    => '-8000',         // 32MB 缓存
    'temp_store'    => 'MEMORY',        // 临时表存内存
    'mmap_size'     => '268435456',     // 256MB 内存映射
];

foreach ($pragmas as $key => $value) {
    $db->exec("PRAGMA {$key} = {$value};");
}

2. 连接管理 ​

php
<?php
// 使用单例模式确保全局唯一连接
class DatabaseFactory
{
    private static ?SQLiteDatabase $instance = null;

    public static function create(string $path): SQLiteDatabase
    {
        if (self::$instance === null) {
            self::$instance = new SQLiteDatabase($path);
        }
        return self::$instance;
    }

    public static function createMemory(): SQLiteDatabase
    {
        return new SQLiteDatabase(':memory:', inMemory: true);
    }
}

3. 数据库备份策略 ​

bash
# 使用 .backup 命令在线备份（不锁定数据库）
sqlite3 /data/app.db ".backup /backup/app_$(date +%Y%m%d).sqlite"

# 导出为 SQL 文件
sqlite3 /data/app.db ".dump" > /backup/app_schema.sql

# 压缩备份
gzip -c /backup/app_$(date +%Y%m%d).sqlite > /backup/app_$(date +%Y%m%d).sqlite.gz

4. 文件权限设置 ​

bash
# SQLite 数据库文件权限
# Web 服务器需要读写权限，但不需要执行权限
chmod 0664 /data/app.db
chmod 0664 /data/app.db-wal   # WAL 文件
chmod 0664 /data/app.db-shm   # 共享内存文件

# 确保数据目录归 Web 服务器用户所有
chown www-data:www-data /data/

何时选择 SQLite

  • 单机应用、桌面工具、移动应用后端
  • 测试环境、CI/CD 流水线
  • 小型网站（日访问量 < 10万）
  • 原型开发和概念验证
  • 嵌入式设备和 IoT 场景

参考链接 ​