SQLite 3 資料庫管理與使用

SQLite 3 是一套廣受歡迎的嵌入式關聯式資料庫管理系統。其最大特色在於輕量化、無需獨立伺服器程序，且所有資料皆儲存在單一檔案，非常適合行動應用、嵌入式系統、小型桌面程式（及本地開發環境）應用。SQLite 3 易於管理與操作。本文將為 ServBay 用戶詳細說明如何在 ServBay 環境下高效管理與運用 SQLite 3 資料庫，包括安裝驗證、連線、基本操作、資料備份/還原、效能調校及安全重點。

概述

ServBay 作為一套功能全面的本地 Web 開發環境，已預載 SQLite 3 及相關開發函式庫，您可直接經由終端指令或透過所支援的 PHP、Node.js、Python、Go 等各式語言來存取操作，無需再額外安裝。這大幅簡化了本地開發時使用 SQLite 3 的流程。

安裝與設定 SQLite 3

ServBay 預設已整合 SQLite 3，無需任何額外安裝步驟，可執行檔與相關函式庫皆已內含於 ServBay 套件內。

驗證 SQLite 3 安裝

您可在終端機執行下列命令，快速檢查系統是否能存取 ServBay 提供的 SQLite 3 執行檔並查詢其版本：

bash

sqlite3 --version

若可顯示 SQLite 3 版本號（例如 3.41.2 2023-03-26 11:52:19 ...），即代表已整合完成並可直接使用。

建立及連接 SQLite 3 資料庫

SQLite 3 一個資料庫本質上就是一個檔案。依預設，ServBay 建議將資料庫檔存放於 /Applications/ServBay/db/sqlite 目錄，但僅為建議，您可依需求另選存放路徑。建立或連接資料庫，通常可透過 sqlite3 指令列工具或各語言的 SQLite 函式庫完成。

使用命令列工具連接

善用 sqlite3 指令工具是管理 SQLite 3 資料庫最直接的方法。

建立或連接資料庫：在終端執行下列命令。若該路徑無檔案，sqlite3 會自動建立嶄新的資料庫檔；若已存在則會直接開啟。
bash
```
sqlite3 /Applications/ServBay/db/sqlite/servbay.db
```
1
（本教學將範例資料庫命名為 servbay.db ，以呼應 ServBay 品牌）
進入 sqlite> 提示符後，即可執行 SQL 指令或 .commands。
離開 SQLite 3：在 sqlite> 提示符下輸入 .exit 即可離開工具。
sql
```
.exit
```
1

以程式語言連接

ServBay 支援的多種語言均提供對應 SQLite 3 函式庫。以下以 PHP 及 Node.js 示範。

PHP 範例

ServBay 通常已內建 PHP SQLite 3 延伸模組（php-sqlite3）。可利用內建 SQLite3 類別連接資料庫。

php

<?php
// 指定資料庫檔案路徑，建議存於 ServBay 的 db/sqlite 目錄
$database_file = '/Applications/ServBay/db/sqlite/servbay.db';

// 連接至 SQLite 3 資料庫
// 若檔案不存在，SQLite3 建構子會自動建立檔案
$db = new SQLite3($database_file);

if (!$db) {
    die("無法連線至 SQLite 3 資料庫: " . $db->lastErrorMsg());
}

echo "成功連線至 SQLite 3 資料庫: " . $database_file . "\n";

// 建立資料表 (若不存在)
$create_table_sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)";
$db->exec($create_table_sql);
echo "資料表 'users' 建立或檢查完成。\n";

// 插入資料
$name = 'ServBay Demo User';
$email = 'demo@servbay.demo';
$insert_sql = $db->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$insert_sql->bindValue(':name', $name, SQLITE3_TEXT);
$insert_sql->bindValue(':email', $email, SQLITE3_TEXT);

if ($insert_sql->execute()) {
    echo "資料成功插入。\n";
} else {
    echo "資料插入失敗: " . $db->lastErrorMsg() . "\n";
}


// 查詢資料
$query_sql = "SELECT id, name, email FROM users";
$result = $db->query($query_sql);

if ($result) {
    echo "查詢結果:\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "\n";
    }
} else {
     echo "查詢失敗: " . $db->lastErrorMsg() . "\n";
}


// 關閉資料庫連線
$db->close();
unset($db); // 釋放資源
echo "資料庫連線已關閉。\n";

?>

Node.js 範例

於 Node.js 使用 SQLite 3 需先安裝 sqlite3 npm 套件。於專案目錄下執行：

bash

npm install sqlite3

然後可於 Node.js 程式使用該套件連接操作資料庫：

javascript

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 指定資料庫檔案路徑，利用 path.join 確保跨平台相容性
const dbPath = path.join('/Applications/ServBay/db/sqlite', 'servbay.db');

// 連接 SQLite 3 資料庫
// 若檔案不存在，sqlite3.Database 會自動建立
let db = new sqlite3.Database(dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
  if (err) {
    console.error('無法連線至 SQLite 3 資料庫:', err.message);
  } else {
    console.log('成功連線至 SQLite 資料庫.');
  }
});

// 逐步序列執行命令，確保順序正確
db.serialize(() => {
  // 建立資料表 (如不存在)
  db.run(`CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )`, (err) => {
    if (err) {
      console.error('建立資料表失敗:', err.message);
    } else {
      console.log('資料表 "users" 建立或檢查完成.');
    }
  });

  // 插入資料
  const name = 'ServBay Demo User';
  const email = 'demo@servbay.demo';
  db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, [name, email], function(err) {
    if (err) {
      // SQLITE_CONSTRAINT 為唯一約束錯誤代碼
      if (err.errno === 19) { // SQLITE_CONSTRAINT
         console.warn(`使用者 '${name}' (${email}) 已存在，略過插入。`);
      } else {
         console.error('資料插入失敗:', err.message);
      }
    } else {
      console.log(`成功插入一筆資料，ID: ${this.lastID}`);
    }
  });

  // 查詢資料
  db.all(`SELECT id, name, email FROM users`, [], (err, rows) => {
    if (err) {
      throw err;
    }
    console.log('查詢結果:');
    rows.forEach((row) => {
      console.log(`ID: ${row.id}, Name: ${row.name}, Email: ${row.email}`);
    });
  });
});

// 關閉資料庫連線
db.close((err) => {
  if (err) {
    console.error('關閉資料庫連線失敗:', err.message);
  } else {
    console.log('資料庫連線已關閉.');
  }
});

注意： 在實際專案中請確保資料庫檔案路徑安全，並妥善處理錯誤與多工存取狀況。

資料庫管理

基本 SQL 操作

成功連接 SQLite 3 資料庫後，您即可透過標準 SQL 指令進行資料管理。以下舉幾個常見操作說明：

建立資料表：定義結構。

sql

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL,
    price REAL DEFAULT 0.00
);

插入資料：新增記錄。

sql

INSERT INTO products (product_name, price) VALUES ('ServBay T-Shirt', 19.99);
INSERT INTO products (product_name, price) VALUES ('ServBay Sticker Pack', 4.99);

查詢資料：檢索記錄。

sql

SELECT * FROM products;
SELECT product_name, price FROM products WHERE price > 10.00;

更新資料：修改既有記錄。

sql

UPDATE products SET price = 24.99 WHERE product_name = 'ServBay T-Shirt';

刪除資料：移除記錄。

sql

DELETE FROM products WHERE product_id = 1;
DELETE FROM products; -- 刪除表內所有資料

刪除資料表：移除整個資料表與其資料。
sql
```
DROP TABLE products;
```
1

上述指令可直接於 sqlite3 命令工具執行，亦可由程式語言的 SQLite 函式庫方式下達。

備份與還原

SQLite 3 的備份及還原極為方便，因整個資料庫即是一個獨立檔案。

資料庫備份

最單純的備份方法是直接複製資料庫檔。建議將備份檔儲存於 /Applications/ServBay/backup/sqlite 目錄集中管理。

於終端執行指令備份資料庫檔：

bash

# 建立備份目錄 (如不存在)
mkdir -p /Applications/ServBay/backup/sqlite

# 複製資料庫檔以備份
# 建議檔名加日期或時間戳區隔版本
cp /Applications/ServBay/db/sqlite/servbay.db /Applications/ServBay/backup/sqlite/servbay_$(date +%Y%m%d_%H%M%S).db

您亦可於 sqlite3 工具內利用 .backup 指令進行熱備份（即時備份），但本地開發情境下直接檔案複製已十分便利。

還原資料庫

將備份檔案複製回原存放路徑即可完成還原。執行還原前，請務必停止一切可能正在存取該資料庫的應用程式或 ServBay 服務，以避免檔案鎖定或資料不一致！

bash

# 假設您要還原最新的備份檔
# 取出最新檔案例：servbay_20231027_103000.db
LATEST_BACKUP=$(ls -t /Applications/ServBay/backup/sqlite/servbay_*.db | head -n 1)

# 檢查是否找出備份檔
if [ -z "$LATEST_BACKUP" ]; then
  echo "錯誤：找不到任何 SQLite 備份檔。"
  exit 1
fi

echo "即將還原備份檔: $LATEST_BACKUP"

# 停止相關服務/應用...（依您的 ServBay 設定及使用方式而異）
# 如 PHP 應用，請確保其相關進程關閉

# 覆蓋原有資料庫檔
cp "$LATEST_BACKUP" /Applications/ServBay/db/sqlite/servbay.db

echo "資料庫還原完成。"

# 重新啟動所需服務/應用...

重要提醒： 直接複製檔案方式於資料庫正被寫入時可能導致損毀。若有活躍寫入，建議先停掉相關服務，或利用 SQLite 熱備份 API（例如透過程式庫方法）。

效能優化

SQLite 3 提供多種 PRAGMA 指令及索引設計協助效能最佳化。

索引優化

針對常用於查詢篩選（WHERE）、連接（JOIN）或排序（ORDER BY）的欄位建立索引，可大幅提升查詢速度。

sql

-- 為 users 資料表 email 欄建立唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 為 products 資料表 product_name 欄建立普通索引
CREATE INDEX idx_products_name ON products(product_name);

查詢優化

利用 EXPLAIN QUERY PLAN 指令檢視 SQLite 執行 SQL 查詢規劃，有助發現效能瓶頸。

sql

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'demo@servbay.demo';
EXPLAIN QUERY PLAN SELECT product_name FROM products ORDER BY price DESC;

分析輸出查詢計畫，判斷是否有用到索引、是否在進行全表掃描（SCAN TABLE）。

組態調校 (PRAGMA)

PRAGMA 指令能調整 SQLite 3 執行參數，常用加速設定包括：

PRAGMA journal_mode; / PRAGMA journal_mode = mode;：設定日誌模式。WAL（Write-Ahead Logging）模式下讀寫效能表現通常更好，適合多讀寫及併發情境。預設可能是 DELETE 或 TRUNCATE 模式。設成 WAL 可減少寫鎖衝突。
sql
```
PRAGMA journal_mode = WAL;
```
1
PRAGMA synchronous; / PRAGMA synchronous = level;：控制與檔案系統同步的嚴謹度。為 OFF（或 0）可大幅提升寫入速度，但有系統異常時最近交易有遺失風險；FULL（1）與 NORMAL（2）較為保險但慢。開發階段可設為 OFF 以求效能。
sql
```
PRAGMA synchronous = OFF;
```
1
PRAGMA cache_size; / PRAGMA cache_size = pages;：設定緩存頁數。加大緩存可降低 I/O 提升效能，但耗用記憶體。
sql
```
PRAGMA cache_size = -20000; -- 設緩存為 20MB（負數表示 KB）
```
1

注意： PRAGMA 設定僅影響目前連線。若希望長駐，需每次連接後重新設置，或於應用啟動時置頂執行。

安全管理

雖 SQLite 3 為純檔案型資料庫，於本地開發環境下仍應注意底層安全。

檔案權限

請確保 SQLite 資料庫檔案系統權限設定妥當，僅允許 ServBay 執行身份或相關開發程序可讀寫，杜絕未授權存取。

bash

# 例：將擁有者設為現行用戶，僅所有者可讀寫
# 請依實際 ServBay 執行身份調整
chmod 600 /Applications/ServBay/db/sqlite/servbay.db

資料加密

SQLite 3 原生不支援檔案加密。若本地開發涉及敏感資料且需加密檔案，可選擇支援加密的 SQLite 擴充像 SQLCipher，需額外安裝並使用特定開啟 API。

常見問題及解答

無法連線 SQLite 3 資料庫

檢查資料庫檔案路徑：確認連線字串或命令行指定的檔案路徑正確，且路徑下確實存在資料庫檔。
檢查檔案權限：確保 ServBay 執行用戶/終端登入者有檔案讀寫權。可用 ls -l /Applications/ServBay/db/sqlite/servbay.db 查詢，必要時以 chmod 或 chown 調整。
資料庫檔案損壞？ 嘗試用 sqlite3 指令連線。若連線失敗或出現錯誤，檔案可能損毀，請從備份還原。

資料庫鎖定問題（Database is locked）

SQLite 3 進行寫入操作會鎖住全檔案。若寫入同時有其他進程寫（甚至某些情況下有讀），可能遇到鎖定錯誤。

檢查併發存取：確保沒其他應用/指令/開發伺服器同時寫入同一資料庫。常見於同時啟多組程式連線同一檔案。
啟用 WAL 模式：於連線後設定 PRAGMA journal_mode = WAL; 能大幅提升多工存取效能。建議進入資料庫就立即設此 PRAGMA。
處理鎖定錯誤：撰寫程式時遇鎖定錯誤不應直接失敗，應設重試機制，稍等再重試。多數 SQLite 函式庫支援 busy timeout，可自動重試一段時間。
例如於 Node.js sqlite3 套件可設：
javascript
```
db.configure("busyTimeout", 5000); // 忙碌逾時設 5000 毫秒（5 秒）
```
1
PHP SQLite3 類無 busyTimeout 方法，須於應用層自行設計重試結構，或查看 sqlite_busy_timeout 函數是否可用。

小結

ServBay 為開發者量身打造的本地 Web 開發環境，預載強大又易用的 SQLite 3 資料庫。透過本篇說明，您應已熟悉如何在 ServBay 下進行 SQLite 3 安裝驗證、連線、基本管理、高效備份/還原、效能調校及基本安全守則。SQLite 3 輕巧靈活，非常適合本地開發、測試及小型專案資料庫需求。再配合 ServBay 其它工具鏈（PHP、Node.js、Python、Go、多種 Web 伺服器及資料庫），您的應用專案將可快速原型與測試。

SQLite 3 資料庫管理與使用 ​

概述 ​

安裝與設定 SQLite 3 ​

驗證 SQLite 3 安裝 ​

建立及連接 SQLite 3 資料庫 ​

使用命令列工具連接 ​

以程式語言連接 ​

PHP 範例 ​

Node.js 範例 ​

資料庫管理 ​

基本 SQL 操作 ​

備份與還原 ​

資料庫備份 ​

還原資料庫 ​

效能優化 ​

索引優化 ​

查詢優化 ​

組態調校 (PRAGMA) ​

安全管理 ​

檔案權限 ​

資料加密 ​

常見問題及解答 ​

無法連線 SQLite 3 資料庫 ​

資料庫鎖定問題（Database is locked） ​

小結 ​

SQLite 3 資料庫管理與使用

概述

安裝與設定 SQLite 3

驗證 SQLite 3 安裝

建立及連接 SQLite 3 資料庫

使用命令列工具連接

以程式語言連接

PHP 範例

Node.js 範例

資料庫管理

基本 SQL 操作

備份與還原

資料庫備份

還原資料庫

效能優化

索引優化

查詢優化

組態調校 (PRAGMA)

安全管理

檔案權限

資料加密

常見問題及解答

無法連線 SQLite 3 資料庫

資料庫鎖定問題（Database is locked）

小結