SQLite 介紹
SQLite 是無伺服器、零配置、單一檔案的嵌入式關聯式資料庫。PHP 內建 `sqlite3` 擴展（PHP 5.3+），適合：中小型應用、本地儲存、快取、離線優先架構、測試環境、IoT 裝置。
完整操作類封裝
<?php
/**
* SQLite 資料庫操作類
* 封裝 SQLite3 類別，提供統一介面、預處理語句、事務支援
*/
class SqliteDatabase {
private SQLite3 $db;
private string $filePath;
private int $queryCount = 0;
public function __construct(string $filePath, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE) {
$this->filePath = $filePath;
$this->db = new SQLite3($filePath, $flags);
$this->db->busyTimeout(5000); // 5 秒忙碌等待
// 啟用外鍵約束
$this->db->exec('PRAGMA foreign_keys = ON');
// WAL 模式提升並發
$this->db->exec('PRAGMA journal_mode = WAL');
}
// ==================== 連線管理 ====================
public function getConnection(): SQLite3 {
return $this->db;
}
public function close(): bool {
if ($this->db) {
$result = $this->db->close();
$this->db = null;
return $result;
}
return false;
}
public function getFilePath(): string {
return $this->filePath;
}
// ==================== 查詢執行 ====================
/**
* 執行不回傳結果的 SQL (CREATE, INSERT, UPDATE, DELETE, PRAGMA)
* @return bool
*/
public function exec(string $sql): bool {
$this->queryCount++;
return $this->db->exec($sql);
}
/**
* 執行查詢回傳結果集
* @return SQLite3Result|false
*/
public function query(string $sql): SQLite3Result|false {
$this->queryCount++;
return $this->db->query($sql);
}
/**
* 準備預處理語句（防注入、效能更好）
* @return SQLite3Stmt
*/
public function prepare(string $sql): SQLite3Stmt {
return $this->db->prepare($sql);
}
// ==================== 便利查詢方法 ====================
/**
* 查詢所有列（關聯陣列）
* @return array>
*/
public function fetchAll(string $sql, array $params = []): array {
$stmt = $this->prepare($sql);
$this->bindParams($stmt, $params);
$result = $stmt->execute();
$rows = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$rows[] = $row;
}
$result->finalize();
return $rows;
}
/**
* 查詢單列
* @return array|false
*/
public function fetchOne(string $sql, array $params = []): array|false {
$stmt = $this->prepare($sql);
$this->bindParams($stmt, $params);
$result = $stmt->execute();
$row = $result->fetchArray(SQLITE3_ASSOC);
$result->finalize();
return $row ?: false;
}
/**
* 查詢單一欄位值
*/
public function fetchColumn(string $sql, array $params = [], int $column = 0): mixed {
$row = $this->fetchOne($sql, $params);
if ($row === false) return null;
$values = array_values($row);
return $values[$column] ?? null;
}
/**
* 查詢單一整數
*/
public function fetchInt(string $sql, array $params = []): ?int {
$val = $this->fetchColumn($sql, $params);
return $val !== null ? (int)$val : null;
}
/**
* 綁定參數到預處理語句
*/
private function bindParams(SQLite3Stmt $stmt, array $params): void {
foreach ($params as $key => $value) {
// 支援命名參數 (:name) 與位置參數 (?)
$paramName = is_int($key) ? ($key + 1) : $key;
$type = $this->getSqliteType($value);
$stmt->bindValue($paramName, $value, $type);
}
}
private function getSqliteType(mixed $value): int {
return match (true) {
is_int($value) => SQLITE3_INTEGER,
is_float($value) => SQLITE3_FLOAT,
is_bool($value) => SQLITE3_INTEGER,
is_null($value) => SQLITE3_NULL,
default => SQLITE3_TEXT,
};
}
// ==================== 寫入操作 ====================
/**
* 插入單筆資料
* @param string $table
* @param array $data 欄位=>值
* @return int|false 最後插入的 rowid
*/
public function insert(string $table, array $data): int|false {
if (empty($data)) return false;
$columns = implode(', ', array_keys($data));
$placeholders = ':' . implode(', :', array_keys($data));
$sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
$stmt = $this->prepare($sql);
$this->bindParams($stmt, $data);
$result = $stmt->execute();
$stmt->finalize();
return $result ? $this->db->lastInsertRowID() : false;
}
/**
* 批次插入（事務包裹）
* @param array> $rows
* @return int 成功筆數
*/
public function insertBatch(string $table, array $rows): int {
if (empty($rows)) return 0;
$this->db->exec('BEGIN TRANSACTION');
$count = 0;
try {
$first = $rows[0];
$columns = implode(', ', array_keys($first));
$placeholders = ':' . implode(', :', array_keys($first));
$sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
$stmt = $this->prepare($sql);
foreach ($rows as $row) {
$this->bindParams($stmt, $row);
$result = $stmt->execute();
$stmt->reset();
if ($result) $count++;
}
$stmt->finalize();
$this->db->exec('COMMIT');
} catch (Throwable $e) {
$this->db->exec('ROLLBACK');
throw $e;
}
return $count;
}
/**
* 更新資料
* @param array $data 欄位=>值
* @param string $where WHERE 條件（不含 WHERE 關鍵字）
* @param array $whereParams WHERE 參數
* @return int 影響行數
*/
public function update(string $table, array $data, string $where = '', array $whereParams = []): int {
if (empty($data)) return 0;
$setClause = implode(', ', array_map(fn($col) => "$col = :$col", array_keys($data)));
$sql = "UPDATE $table SET $setClause";
if ($where) $sql .= " WHERE $where";
$params = $data + $whereParams;
$stmt = $this->prepare($sql);
$this->bindParams($stmt, $params);
$result = $stmt->execute();
$changes = $this->db->changes();
$stmt->finalize();
return $changes;
}
/**
* 刪除資料
*/
public function delete(string $table, string $where = '', array $whereParams = []): int {
$sql = "DELETE FROM $table";
if ($where) $sql .= " WHERE $where";
$stmt = $this->prepare($sql);
$this->bindParams($stmt, $whereParams);
$result = $stmt->execute();
$changes = $this->db->changes();
$stmt->finalize();
return $changes;
}
// ==================== 事務支援 ====================
public function beginTransaction(): void {
$this->db->exec('BEGIN TRANSACTION');
}
public function commit(): void {
$this->db->exec('COMMIT');
}
public function rollBack(): void {
$this->db->exec('ROLLBACK');
}
/**
* 事務執行範本
*/
public function transactional(callable $callback): mixed {
$this->beginTransaction();
try {
$result = $callback($this);
$this->commit();
return $result;
} catch (Throwable $e) {
$this->rollBack();
throw $e;
}
}
// ==================== 實用工具 ====================
/**
* 取得資料表結構
*/
public function getTableSchema(string $table): array {
return $this->fetchAll("PRAGMA table_info($table)");
}
/**
* 取得所有資料表名稱
*/
public function getTables(): array {
$rows = $this->fetchAll("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'");
return array_column($rows, 'name');
}
/**
* 資料庫備份（線上備份 API）
*/
public function backup(string $destPath): bool {
$destDb = new SQLite3($destPath);
$success = $this->db->backup($destDb);
$destDb->close();
return $success;
}
/**
* 執行 VACUUM 整理碎片
*/
public function vacuum(): bool {
return $this->db->exec('VACUUM');
}
/**
* 取得查詢計數（除錯用）
*/
public function getQueryCount(): int {
return $this->queryCount;
}
/**
* 取得 SQLite 版本
*/
public function getVersion(): string {
return SQLite3::version()['versionString'] ?? 'Unknown';
}
}
使用範例
<?php
// 建立/開啟資料庫
$db = new SqliteDatabase(__DIR__ . '/data/app.db');
// 建立資料表
$db->exec("
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
");
$db->exec("
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
content TEXT,
status INTEGER DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
)
");
// 插入資料
$userId = $db->insert('users', [
'name' => 'Alice',
'email' => 'alice@example.com',
'age' => 28
]);
echo "新用戶 ID: $userId\n";
// 批次插入
$posts = [
['user_id' => $userId, 'title' => '第一篇文章', 'content' => '內容...'],
['user_id' => $userId, 'title' => '第二篇文章', 'content' => '更多內容'],
];
$db->insertBatch('posts', $posts);
// 查詢
$users = $db->fetchAll("SELECT * FROM users WHERE age > ?", [20]);
foreach ($users as $user) {
echo "{$user['name']} ({$user['email']})\n";
}
// 關聯查詢
$posts = $db->fetchAll("
SELECT p.title, u.name as author
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE p.status = 1
");
foreach ($posts as $post) {
echo "[{$post['author']}] {$post['title']}\n";
}
// 標量查詢
$count = $db->fetchInt("SELECT COUNT(*) FROM users");
echo "用戶總數: $count\n";
// 事務範例
$db->transactional(function($db) {
$db->update('users', ['age' => 29], 'id = ?', [1]);
$db->insert('posts', ['user_id' => 1, 'title' => '生日快樂', 'content' => '今天過生日！']);
});
// 備份
$db->backup(__DIR__ . '/backups/app_' . date('Ymd_His') . '.db');
echo "查詢次數: " . $db->getQueryCount() . "\n";
$db->close();
SQLite 專屬優化設定 (PRAGMA)
<?php
// 連線後建議執行的優化 PRAGMA
$optimizePragmas = [
'journal_mode = WAL', // Write-Ahead Logging：讀寫並發、抗崩潰
'synchronous = NORMAL', // 平衡安全與速度 (FULL 最安全、OFF 最快)
'cache_size = -32768', // 32MB 頁面快取 (負數 = KB)
'temp_store = MEMORY', // 臨時表/索引存記憶體
'mmap_size = 268435456', // 256MB 記憶體映射 (大檔案加速)
'page_size = 4096', // 4KB 頁面 (建立 DB 前設定)
'foreign_keys = ON', // 啟用外鍵約束
'auto_vacuum = INCREMENTAL', // 自動整理碎片
];
foreach ($optimizePragmas as $pragma) {
$db->exec("PRAGMA $pragma");
}
SQLite vs MySQL/PostgreSQL 選擇指南
| 場景 | 推薦 | 理由 |
|---|
| 單機應用、桌面軟體、行動 App 本地儲存 | SQLite | 零部署、單檔案、跨平台 |
| 中小型網站、CMS、部落格 | SQLite / MySQL | 流量 < 10萬 PV/天 SQLite 足夠 |
| 高並發寫入、多伺服器叢集 | PostgreSQL / MySQL | SQLite 寫入鎖定整個資料庫 |
| 複雜分析查詢、窗口函數、CTE | PostgreSQL | SQLite 功能有限 |
| 需要存儲程序、觸發器、使用者權限 | MySQL / PostgreSQL | SQLite 不支援 |
| 測試環境、CI/CD | SQLite (:memory:) | 極快、隔離、無殘留 |
記憶體資料庫：單元測試利器
<?php
// 記憶體資料庫：測試專用，極快、自動銷毀
$testDb = new SqliteDatabase(':memory:');
$testDb->exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)");
$testDb->insert('test', ['value' => 'hello']);
$row = $testDb->fetchOne("SELECT * FROM test WHERE id = 1");
// 測試結束自動釋放，無檔案殘留
常見問題與解決
| 問題 | 原因 | 解決 |
|---|
| Database is locked | 並發寫入衝突、長事務阻塞 | 啟用 WAL、縮短事務、重試機制 |
| 無法開啟檔案 | 權限不足、目錄不存在 | 確保目錄可寫、檔案擁有者正確 |
| ALTER TABLE 功能有限 | SQLite 不支援 DROP COLUMN 等 | 建立新表 → 複製資料 → 重命名 |
| 日期時間處理 | 無原生 DateTime 型別 | 存 TEXT (ISO8601) 或 INTEGER (Unix timestamp) |
SQLite 以其零配置、單檔案、跨平台特性，成為嵌入式儲存的首選。配合 PHP SQLite3 類別與適當的 PRAGMA 優化，能輕鬆支撐中小型應用的資料儲存需求。
- 上一篇文章： PHP PDO 資料庫操作類：模仿 Spring JdbcTemplate 設計的現代化封裝
- 下一篇文章： PHP + MySQL 實現無限級分類完整教學與代碼實例
Copyright ©
程式碼之家 All Rights Reserved
