PHP 與 SQLite 實戰指南：輕量級嵌入式資料庫的完整應用

為什麼選擇 SQLite？

• 零配置：單一檔案，無伺服器程序，嵌入應用程式即用。
• 跨平台：檔案格式相容 32/64 位元、大小端序，可直接複製部署。
• ACID 事務：完整支援原子性、一致性、隔離性、持久性。
• SQL 標準相容：支援大部分 SQL-92、JSON1、FTS5 全文搜尋、R*Tree 空間索引。
• 適用場景：桌面/行動應用本地儲存、中小型網站、測試環境、CI/CD 暫存資料、邊緣運算節點。

PHP 環境準備

PHP 5.1+ 內建 PDO_SQLITE 與 SQLite3 兩套擴展，預設啟用。確認：

php -m | grep -i sqlite
# pdo_sqlite
# sqlite3

核心操作：PDO 介面（推薦）

<?php
$dbFile = __DIR__ . '/data/app.sqlite';
$pdo = new PDO("sqlite:$dbFile");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// 建立資料表
$pdo->exec("
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL,
        country CHAR(2),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
");

// 插入資料（預處理防注入）
$stmt = $pdo->prepare("INSERT INTO users (username, email, country) VALUES (?, ?, ?)");
$stmt->execute(['alice', 'alice@example.com', 'TW']);
$stmt->execute(['bob', 'bob@example.com', 'US']);
echo "Last ID: " . $pdo->lastInsertId();

// 查詢
$stmt = $pdo->query("SELECT username, country FROM users");
foreach ($stmt as $row) {
    echo "{$row['username']} ({$row['country']})\n";
}

// 事務範例
try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO users (username, email) VALUES ('charlie', 'charlie@example.com')");
    $pdo->exec("UPDATE users SET country = 'JP' WHERE username = 'alice'");
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

SQLite3 物件導向介面

<?php
$db = new SQLite3(__DIR__ . '/data/app.sqlite');
$db->exec('PRAGMA journal_mode = WAL;'); // 開啟 WAL 模式提升併發讀取
$db->exec('PRAGMA busy_timeout = 5000;'); // 鎖定等待 5 秒

$stmt = $db->prepare('SELECT * FROM users WHERE country = :c');
$stmt->bindValue(':c', 'TW', SQLITE3_TEXT);
$result = $stmt->execute();
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    print_r($row);
}

效能調優關鍵

1. WAL 模式：PRAGMA journal_mode = WAL; 允許讀寫並行，大幅提升併發吞吐。
2. 索引策略：常用 WHERE/ORDER BY/JOIN 欄位建立索引（`CREATE INDEX idx_users_country ON users(country);`）。
3. 連線複用：長運行程式（CLI/Swoole）保持單一連線；PHP-FPM 每請求新連線，開銷極小。
4. 批次寫入：事務包裹大量 INSERT，避免每筆 fsync。
5. 避免 `SELECT *`：只取需要欄位，減少 I/O。
6. 定期 VACUUM：重組檔案碎片，回收空間（`$pdo->exec('VACUUM;')`）。

常見陷阱與解決

進階功能

• JSON1 擴展：`json_extract(col, '$.path')`、`json_set()`、`json_each()` 直接操作 JSON 欄位。
• FTS5 全文搜尋：`CREATE VIRTUAL TABLE docs USING fts5(title, content);` 支援分詞、排序、片段。
• 備份 API：`$db->backup($backupDb)` 線上熱備份不鎖定主庫。
• 加密：使用 SQLCipher 擴展（需編譯支援）實現透明加密。

部署建議

• 資料庫檔案置於 非公開目錄（`/var/www/data/`），防止直接下載。
• 生產環境啟用 open_basedir 限制存取路徑。
• 備份策略：定時 `VACUUM INTO '/backup/app_$(date +%F).sqlite'` 或檔案層面快照。
• 監控：檔案大小、查詢延遲、鎖定等待時間。

總結

SQLite 是 PHP 開發者工具箱中的「瑞士刀」：零運維、標準 SQL、攜帶方便、效能驚人。善用 PDO、WAL、索引、JSON1/FTS5，即可在中小型專案、測試、CLI 工具、邊緣裝置中發揮最大價值。