SQLite 輕量級嵌入式資料庫完整入門指南

SQLite 核心特性與適用場景

SQLite 是一個零配置、無伺服器、單一檔案、跨平台的嵌入式關聯式資料庫引擎，廣泛應用於行動裝置、桌面應用、瀏覽器、IoT 設備、邊緣運算等場景。

核心優勢 (官方 13 項特性)

型別系統：動態型別與親和性

SQLite 採用顯式型別親和性 而非強制型別限制：

特殊規則：INTEGER PRIMARY KEY 欄位別名為 ROWID，自動指派 64 位元有號整數，唯一且不重複。

PHP 操作 SQLite 完整範例

1. 連線與建立資料表

lastErrorMsg());
}
echo "資料庫連線成功！\n";

// 建立資料表
$sql = <<exec($sql)) {
    die('建表失敗: ' . $db->lastErrorMsg());
}
echo "資料表建立成功\n";
?>

2. 列出所有資料表

query('SELECT name, sql FROM sqlite_master WHERE type="table" ORDER BY name;');

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    echo "Table: {$row['name']}\n";
    echo "SQL: {$row['sql']}\n";
    echo "-----\n";
}
?>

3. 查詢資料 (關聯陣列模式)

query('SELECT name, email FROM users;', SQLITE3_ASSOC);

echo "Users:\n";
while ($row = $result->fetchArray()) {
    echo "  Name: {$row['name']}, Email: {$row['email']}\n";
}
?>

4. 新增/修改/刪除 (參數化查詢防注入)

prepare('INSERT INTO users (name, email) VALUES (:name, :email);');
$stmt->bindValue(':name', 'User' . $i, SQLITE3_TEXT);
$stmt->bindValue(':email', "user{$i}@example.com", SQLITE3_TEXT);
$stmt->execute();

// 批次新增 (交易包裝提升效能)
$db->exec('BEGIN TRANSACTION;');
for ($i = 1; $i <= 100; $i++) {
    $stmt = $db->prepare('INSERT INTO users (name, email) VALUES (?, ?);');
    $stmt->bindValue(1, "Batch User $i", SQLITE3_TEXT);
    $stmt->bindValue(2, "batch{$i}@example.com", SQLITE3_TEXT);
    $stmt->execute();
}
$db->exec('COMMIT;');

// 修改
$stmt = $db->prepare('UPDATE users SET email = :email WHERE name = :name;');
$stmt->bindValue(':email', 'new@example.com', SQLITE3_TEXT);
$stmt->bindValue(':name', 'User1', SQLITE3_TEXT);
$stmt->execute();

// 刪除
$stmt = $db->prepare('DELETE FROM users WHERE name = :name;');
$stmt->bindValue(':name', 'User99', SQLITE3_TEXT);
$stmt->execute();
?>

管理工具推薦

• DB Browser for SQLite (原 SQLiteBrowser) - 跨平台 GUI，功能完整，支援 SQL 編輯、資料瀏覽、匯入匯出
• SQLiteStudio - 功能豐富、介面現代化、支援圖表視覺化
• DBeaver - 通用資料庫工具，支援 SQLite 及 80+ 種資料庫
• VS Code 擴充套件：SQLite, SQLite Viewer - 輕量級編輯器整合
• 命令列工具：sqlite3 - 內建互動式 Shell，支援 .schema、.tables、.dump、.backup 等點命令

進階特性與最佳實踐

WAL 模式 (Write-Ahead Logging) - 並發效能關鍵

-- 啟用 WAL 模式 (預設 DELETE 模式)
PRAGMA journal_mode = WAL;

-- 效果：
-- 讀取不阻塞寫入，寫入不阻塞讀取
-- 多讀單寫並發效能大幅提升
-- 檢查點自動/手動控制 (PRAGMA wal_autocheckpoint)

常用 PRAGMA 設定

PRAGMA synchronous = NORMAL;      -- 平衡安全/效能 (FULL 最安全、OFF 最快)
PRAGMA cache_size = -32768;       -- 32MB 快取 (負值 = KB，正值 = 頁數)
PRAGMA temp_store = MEMORY;       -- 暫存表/索引放記憶體
PRAGMA foreign_keys = ON;         -- 啟用外鍵約束 (預設 OFF)
PRAGMA page_size = 4096;          -- 頁面大小 (建庫前設定，1024-65536)
PRAGMA auto_vacuum = INCREMENTAL; -- 自動回收空間

效能優化關鍵

• ✅ 使用參數化預備陳述式 (Prepared Statements) 重用計畫
• ✅ 批次操作包裝在交易中 (BEGIN/COMMIT)，單次交易可達 10 萬+ 寫入/秒
• ✅ 適當索引：WHERE/JOIN/ORDER BY 欄位建立索引
• ✅ 避免 SELECT *，明確列出需要欄位
• ✅ 定期 VACUUM (非 WAL 模式) 或 PRAGMA incremental_vacuum 整理碎片
• ✅ 大量匯入時暫時關閉同步/索引：PRAGMA synchronous = OFF; PRAGMA journal_mode = OFF; 匯入後恢復並 REINDEX

適用與不適用場景判斷