SQLite 完全指南

目錄

• 什麼是 SQLite？
• 核心特性
• 架構與運作原理
• 連線機制
• 資料型別系統（重要陷阱）
• 交易與並發控制
• 什麼情境適合用 SQLite？
• SQLite vs 其他資料庫
• 快速開始
• 實戰範例
• 最佳實踐
• 常見問題
• 總結

什麼是 SQLite？

SQLite 是一個 嵌入式（embedded）關聯式資料庫引擎。它最特別的地方在於：它不是一個「伺服器」，而是一個 C 語言函式庫，直接被編譯進你的應用程式裡，整個資料庫就是磁碟上的 一個檔案。

一句話理解

MySQL / PostgreSQL = 一台「資料庫伺服器」，你的程式透過網路連線去問它
SQLite             = 一個「函式庫」，你的程式直接讀寫一個 .db 檔案

它沒有 daemon、沒有 port、沒有帳號密碼、不需要安裝服務。fopen() 開檔案多簡單，SQLite 用起來就多簡單。

為什麼它無所不在？

SQLite 是世界上 部署量最大 的資料庫引擎（沒有之一），因為它藏在：

• 每一支 Android / iOS 手機（App 的本地儲存）
• 每一個瀏覽器（Chrome、Firefox 的歷史紀錄、Cookie）
• macOS、Windows 的系統元件
• Python（內建 sqlite3 模組）、各種桌面軟體的設定檔
• 飛機的黑盒子、IoT 裝置、車載系統

核心定位

SQLite = 嵌入式 + 零設定 + 單檔案 + 完整 SQL + 公領域授權

• 不是 用來取代 MySQL/PostgreSQL 的「大型應用後端資料庫」
• 而是 取代 fopen()：當你需要結構化儲存、查詢、交易時的「應用程式內建資料層」

官方自己的定位：「SQLite 不跟 client/server 資料庫競爭，它跟 fopen() 競爭。」

核心特性

1. Serverless（無伺服器）

沒有獨立的伺服器程序。資料庫的讀寫邏輯全部在 應用程式的行程內 執行，直接對檔案做系統呼叫。

傳統資料庫：App 程序 ──網路/Socket──► DB 伺服器程序 ──► 磁碟
SQLite：    App 程序 ──函式呼叫──► SQLite 函式庫 ──► 磁碟檔案

2. Zero-Configuration（零設定）

不用安裝、不用啟動服務、不用設定檔、不用建立使用者帳號。下載一個檔案就能用。

3. Single-File（單檔案）

整個資料庫——所有資料表、索引、觸發器、view——都在 一個檔案 裡（通常是 .db / .sqlite）。要備份就複製這個檔案，要搬移就 scp 這個檔案。

4. Self-Contained（自包含）

不依賴任何外部函式庫，整個引擎約幾百 KB，可以靜態編譯進任何程式。

5. Cross-Platform（跨平台檔案格式）

.db 檔案格式跨平台相容：在 macOS 寫的檔案，搬到 Windows、Linux、ARM、x86 都能直接讀。大端序/小端序、32/64 位元都無痛。

6. Public Domain（公領域）

授權是 public domain，商用、修改、散布完全免費，無任何限制。

7. 完整的 SQL 支援

支援交易（ACID）、索引、view、trigger、外鍵、CTE、視窗函數（window functions）、全文檢索（FTS5）、JSON 函數等現代 SQL 功能。

架構與運作原理

嵌入式 vs Client-Server 架構

這是理解 SQLite 一切特性的根源。

┌─────────────────── Client-Server（MySQL/PostgreSQL）──────────────────┐
│                                                                      │
│   App 1 ─┐                                                           │
│   App 2 ─┼──TCP──► [DB Server 程序] ──► 資料檔案                       │
│   App 3 ─┘            ↑ 獨立程序、管理連線池、權限、並發                  │
│                                                                      │
└──────────────────────────────────────────────────────────────────────┘

┌─────────────────────────── SQLite（嵌入式）────────────────────────────┐
│                                                                      │
│   ┌─ App 程序 ──────────────────┐                                     │
│   │  你的程式碼                  │                                     │
│   │    ↓ 函式呼叫               │                                      │
│   │  SQLite 函式庫（同一程序）    │ ──► database.db（磁碟檔案）           │
│   └────────────────────────────┘                                     │
│                                                                      │
└──────────────────────────────────────────────────────────────────────┘

關鍵差異：SQLite 沒有「伺服器程序」這個角色。SQL 的解析、最佳化、執行，全部發生在你呼叫 SQLite 函式的那個行程裡。

SQLite 內部分層

SQL 字串
   ↓
① Tokenizer（分詞）
   ↓
② Parser（語法解析 → 語法樹）
   ↓
③ Code Generator（產生 bytecode）
   ↓
④ VDBE 虛擬機（執行 bytecode，這是 SQLite 的核心）
   ↓
⑤ B-Tree 模組（資料/索引以 B-tree 組織）
   ↓
⑥ Pager（分頁快取、交易、鎖定、journal）
   ↓
⑦ OS Interface（VFS，把分頁讀寫對應到實際檔案系統呼叫）

• VDBE（Virtual Database Engine）：SQLite 把 SQL 編譯成自訂 bytecode，再用虛擬機執行。這讓查詢計畫可預測、可用 EXPLAIN 檢視。
• Pager：負責把資料庫檔案切成固定大小的 page（預設 4096 bytes），做快取、處理交易與 rollback、實作鎖定。這是並發與耐久性的核心。
• VFS（Virtual File System）：抽象層，讓 SQLite 可以跑在不同 OS、甚至記憶體、加密檔案系統上。

連線機制

這是 SQLite 和傳統資料庫差最多、也最容易誤解的地方。

「連線」其實是「開檔案」

在 MySQL 你會寫 mysql://user:pass@host:3306/dbname——有主機、port、帳號。SQLite 的「連線字串」就是 一個檔案路徑：

import sqlite3
conn = sqlite3.connect("app.db")        # 開啟（或建立）這個檔案
conn = sqlite3.connect("/data/app.db")  # 絕對路徑
conn = sqlite3.connect(":memory:")      # 純記憶體資料庫，程式結束就消失

「建立連線」實際上是 SQLite 開啟那個檔案、讀取檔頭、準備好 page cache。沒有網路、沒有握手、沒有認證——權限完全由 作業系統的檔案權限 決定（誰能讀寫這個檔案，誰就能讀寫這個資料庫）。

沒有網路存取（這是設計，不是缺陷）

SQLite 不提供 透過網路遠端連線的機制。要讓多台機器共用，唯一的方式是共享底層檔案系統（如 NFS），但官方 強烈不建議——網路檔案系統的鎖定（locking）實作常有 bug，容易造成資料庫損毀。

結論：SQLite 是給 「資料和程式在同一台機器」 的場景用的。需要跨機器存取 → 用 client-server 資料庫。

鎖定模型（Locking）：理解並發的關鍵

因為沒有伺服器來協調，SQLite 用 檔案層級的鎖 來保證一致性。經典模式（rollback journal）的鎖狀態：

UNLOCKED → SHARED（多個讀取者可同時持有）
              ↓
         RESERVED（我準備要寫，但讀取者還能讀）
              ↓
         PENDING（不准新的讀取者進來，等現有讀取者離開）
              ↓
         EXCLUSIVE（獨佔，開始寫入；此時無人能讀寫）

核心規則：「多個讀取者可以並存，但寫入是整個資料庫獨佔的。」 同一時間只能有 一個寫入者，而且寫入當下會擋住所有讀取。這就是為什麼 SQLite 在「高並發寫入」場景會撞牆，也是著名錯誤 database is locked 的來源。

兩種日誌模式：Rollback Journal vs WAL

SQLite 怎麼保證「交易到一半當機，資料不會壞」？靠日誌。有兩種機制：

Rollback Journal（預設、傳統）

寫入前：先把「原始 page」備份到 -journal 檔
   ↓
修改主資料庫檔案
   ↓
成功 → 刪掉 journal（commit）
當機 → 下次開檔時用 journal 把舊資料復原（rollback）

缺點：寫入時會獨佔，讀取者完全被擋住。

WAL（Write-Ahead Logging，強烈推薦開啟）

寫入時：新資料先寫到 -wal 檔（不動主檔）
   ↓
讀取者：繼續讀主檔的舊版本（不被擋）
   ↓
之後再把 -wal 的內容「checkpoint」回主檔

conn.execute("PRAGMA journal_mode=WAL")

WAL 最大的好處：讀取和寫入可以同時進行（讀不擋寫、寫不擋讀）。對 Web 應用、桌面 App 的並發體驗是質變等級的改善。代價是多了 -wal 和 -shm 兩個附屬檔案，且仍然 只能有一個寫入者。

連線的附屬檔案

開啟一個 WAL 模式的 app.db，你會在磁碟上看到：

app.db        ← 主資料庫
app.db-wal    ← Write-Ahead Log
app.db-shm    ← 共享記憶體索引（協調多個連線）

備份時要注意：不能只複製主檔，否則可能漏掉 WAL 裡還沒 checkpoint 的資料。正確做法是用 VACUUM INTO 或 .backup 指令。

程式一直開關連線，SQLite 真的一直開開關關嗎？（連線池）

常見現象：應用程式碼每次請求都 connect() / close()，但底層的 SQLite 連線其實沒有真的一直開關。 這要分成「兩個層次」才講得清楚，否則會混淆：

層次一：SQLite 原生的 open / close —— 其實會開關，只是「便宜」

呼叫 sqlite3_open() / sqlite3_close()，SQLite 確實 做了實體的開啟與關閉。但因為它沒有伺服器、沒有網路握手、沒有認證流程，「開一個連線」本質上只是：

開檔案（open syscall）→ 讀檔頭 → 準備 page cache

所以成本很低——大約比「重用既有連線直接執行」慢 一個數量級，但最壞也只是 毫秒級。這跟 MySQL 開連線要走 TCP 三次握手 + 認證完全不是同個量級。

所以：SQLite 原生層「開關連線很便宜」是真的，但「不會一直開關」不是 SQLite 自己做的——是下面這層做的。

層次二：連線池（Connection Pool）—— 真正讓連線不重開的機制

「程式一直開關、但 SQLite 不會一直開關」的現象，幾乎都是驅動 / 框架層的連線池在做的，不是 SQLite 引擎本身：

你的程式碼              連線池                  SQLite 實體連線
─────────              ─────                  ──────────────
conn = connect()  ──►  借出一個既有連線    ──►  （早就開好，重用，不重開）
...執行 SQL...
conn.close()      ──►  歸還到池子          ──►  （沒有真的 close，留著下次用）
                       （reset：rollback 清掉交易狀態）

關鍵：應用程式的 open / close 只是「向池子借出 / 歸還」，對應到的實體 SQLite 連線一直活著、被重複使用。常見實作：

連線歸還時，池子通常會做 「reset on return」——對連線執行一次 rollback()，清掉殘留的交易狀態與鎖，確保下一個借用者拿到乾淨的連線。

層次三（加碼）：作業系統的檔案快取

就算實體連線真的被關掉，作業系統的 page cache 仍會把 .db 檔的分頁留在記憶體裡。所以下次重開時，讀取常常直接命中 OS 快取，不必真的碰磁碟。這是「重複開關感覺不慢」的另一層原因（但這是 OS 的功勞，不是 SQLite 或連線池）。

實務建議

• SQLite 因為連線便宜，最佳模式通常是「每執行緒保留一條長壽命連線」，而不是每個查詢開關一次——這樣 page cache 保持溫熱，也能持續享受 WAL 的好處。
• 用連線池時要記得 SQLite 是單寫入者：寫入用的池子開很大沒有意義，反而可能加劇鎖競爭。讀取池可以大，寫入實際上會被序列化。

資料型別系統（重要陷阱）

SQLite 一個一定要知道的「個性」。

SQLite 用的是 動態型別（dynamic typing），跟幾乎所有其他 SQL 資料庫都不一樣。

型別親和性（Type Affinity）

在 MySQL，欄位宣告 INT 就只能存整數。在 SQLite，型別是綁在「值」上，而不是綁在「欄位」上。欄位宣告的型別只是一個「建議的親和性」，你其實可以往 INTEGER 欄位塞字串：

CREATE TABLE t (id INTEGER, name TEXT);
INSERT INTO t VALUES ('hello', 123);  -- 不會報錯！id 存了字串，name 存了數字

SQLite 只有 5 種儲存類別（storage class）：

STRICT 表（新版解法）

SQLite 3.37+（2021）加入 STRICT 表，讓型別檢查變嚴格，行為更接近傳統資料庫：

CREATE TABLE t (id INTEGER, name TEXT) STRICT;
INSERT INTO t VALUES ('hello', 123);  -- 這次會報錯！

建議：新專案優先用 STRICT 表，避免動態型別帶來的隱性 bug。

沒有原生的這些型別

SQLite 沒有 專屬的 BOOLEAN、DATE、DATETIME 型別：

• 布林 → 用 0 / 1（INTEGER）
• 日期時間 → 存成 TEXT（'2026-06-08 12:00:00'）、Unix timestamp（INTEGER），或 Julian Day（REAL），搭配內建的 date() / datetime() 函數處理

交易與並發控制

ACID 完整支援

SQLite 是 完全符合 ACID 的（Atomicity、Consistency、Isolation、Durability）。即使作業系統當機或斷電，交易也保證原子性——靠的就是前面提到的 journal/WAL 機制。

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 兩個都成功，或都不發生

並發模型總結

一句話：SQLite 的並發瓶頸永遠是 「同時只能一個寫入者」。讀取可以非常高並發，寫入是序列化的。

busy_timeout：對付 database is locked

當一個連線想寫、但鎖被別人佔住時，預設會立刻回 database is locked 錯誤。設定 busy timeout 讓它「等一下再試」：

conn.execute("PRAGMA busy_timeout = 5000")  # 最多等 5 秒

什麼情境適合用 SQLite？

✅ 適合的場景

❌ 不適合的場景

官方的判斷準則

資料和應用程式在同一台機器 + 寫入並發不高 → SQLite 是絕佳選擇。 一旦需要 跨機器存取 或 高並發寫入 → 改用 PostgreSQL / MySQL。

SQLite vs 其他資料庫

SQLite vs MySQL / PostgreSQL

SQLite vs Redis

兩者常被一起問，但定位完全不同：

SQLite vs JSON / CSV 檔案

這才是 SQLite 真正的競爭對手（取代 fopen()）：

快速開始

安裝

大多數系統已內建。沒有的話：

# macOS
brew install sqlite

# Ubuntu/Debian
sudo apt install sqlite3

# 驗證
sqlite3 --version

Python、PHP、Node（better-sqlite3）、Go（mattn/go-sqlite3）等多數語言已內建或有成熟套件，通常不需另外裝。

命令列第一步

sqlite3 app.db          # 開啟（不存在就建立）app.db

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
sqlite> INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25);
sqlite> SELECT * FROM users;
1|Alice|30
2|Bob|25

sqlite> .mode column     -- 表格化輸出
sqlite> .headers on      -- 顯示欄位名
sqlite> SELECT * FROM users;
id  name   age
--  -----  ---
1   Alice  30
2   Bob    25

sqlite> .tables          -- 列出所有資料表
sqlite> .schema users    -- 看資料表定義
sqlite> .quit            -- 離開

常用「點指令」（dot commands）

.tables           # 列出資料表
.schema [表名]    # 顯示建表 SQL
.mode column      # 輸出格式：column / csv / json / table
.headers on       # 顯示欄位標題
.import data.csv t  # 匯入 CSV 到資料表 t
.output out.csv   # 把後續查詢結果導到檔案
.backup backup.db # 安全備份（含 WAL）
.quit             # 離開

實戰範例

範例 1：Python（內建 sqlite3，無需安裝）

import sqlite3

# 連線（檔案不存在會自動建立）
conn = sqlite3.connect("app.db")
conn.execute("PRAGMA journal_mode=WAL")      # 開啟 WAL，提升並發
conn.execute("PRAGMA foreign_keys=ON")        # 預設外鍵是關的，要手動開
conn.row_factory = sqlite3.Row                # 讓查詢結果能用欄位名存取

cur = conn.cursor()
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id   INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age  INTEGER
    ) STRICT
""")

# 一定要用參數化查詢，絕不要用字串拼接（防 SQL injection）
cur.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 30))
conn.commit()

# 查詢
for row in cur.execute("SELECT id, name, age FROM users WHERE age > ?", (20,)):
    print(row["id"], row["name"], row["age"])

conn.close()

範例 2：交易（轉帳，保證原子性）

import sqlite3

conn = sqlite3.connect("bank.db")
try:
    with conn:  # with 區塊：正常結束自動 commit，拋例外自動 rollback
        conn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        conn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    # 離開 with 區塊，兩筆一起 commit
except sqlite3.Error as e:
    print("交易失敗，已自動回滾：", e)
finally:
    conn.close()

範例 3：用索引加速查詢

-- 沒索引：每次查 email 都要全表掃描
SELECT * FROM users WHERE email = 'alice@example.com';

-- 建索引後變成 O(log n)
CREATE INDEX idx_users_email ON users(email);

-- 確認查詢計畫有用到索引
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
-- 看到 "USING INDEX idx_users_email" 就對了

範例 4：記憶體資料庫（測試 / 暫存超快）

import sqlite3

conn = sqlite3.connect(":memory:")  # 整個 DB 在記憶體，程式結束即消失
conn.execute("CREATE TABLE cache (k TEXT PRIMARY KEY, v TEXT)")
conn.execute("INSERT INTO cache VALUES ('key1', 'value1')")
print(conn.execute("SELECT v FROM cache WHERE k='key1'").fetchone())
# 非常適合單元測試：不留檔案、不用清理、速度極快

範例 5：查詢 JSON 欄位（SQLite 內建 JSON 函數）

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);
INSERT INTO events (payload) VALUES ('{"user":"alice","action":"login"}');

-- 直接從 JSON 字串取欄位
SELECT json_extract(payload, '$.user') AS user FROM events;
-- 或用簡寫運算子（3.38+）
SELECT payload ->> '$.action' AS action FROM events;

最佳實踐

1. 開啟 WAL 模式（除非有特殊理由）

conn.execute("PRAGMA journal_mode=WAL")

讀寫不互相阻塞，對任何有並發的應用都是必開。

2. 設定 busy_timeout 避免立即報鎖錯

conn.execute("PRAGMA busy_timeout = 5000")  # 等 5 秒再放棄

3. 新專案用 STRICT 表

CREATE TABLE t (id INTEGER, name TEXT) STRICT;

避免動態型別塞錯資料造成的隱性 bug。

4. 永遠用參數化查詢

# ❌ 危險：SQL injection
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")

# ✅ 安全
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

5. 記得手動開啟外鍵約束

conn.execute("PRAGMA foreign_keys = ON")  # 基於相容性，預設是關的！

6. 批次寫入包進一個交易

# ❌ 慢：每筆 INSERT 都自動 commit（各自一次 fsync）
for row in rows:
    conn.execute("INSERT INTO t VALUES (?)", (row,))
    conn.commit()

# ✅ 快數十~數百倍：整批包成一個交易
with conn:
    conn.executemany("INSERT INTO t VALUES (?)", [(r,) for r in rows])

7. 定期整理碎片

VACUUM;            -- 重整檔案、回收空間
ANALYZE;           -- 更新統計資訊，幫助查詢最佳化器
PRAGMA optimize;   -- 連線關閉前執行，自動做必要的優化

8. 正確備份（含 WAL 內容）

# ❌ 不要只複製主檔（可能漏掉 WAL 未 checkpoint 的資料）
cp app.db backup.db

# ✅ 用內建備份指令
sqlite3 app.db ".backup backup.db"
# 或 SQL：
# VACUUM INTO 'backup.db';

常見問題

Q1：為什麼一直出現 database is locked？

A：因為有另一個連線正持有寫入鎖。解法：

1. 開啟 WAL 模式（讀寫不互鎖）
2. 設定 PRAGMA busy_timeout
3. 縮短交易時間，別把交易開著做一堆其他事
4. 檢查是否忘了 commit() / close()，讓連線一直佔著鎖

Q2：SQLite 可以多人同時用嗎？

A：可以「多讀」，但「寫入永遠是單一序列」。對 讀多寫少 的應用（多數網站、App）綽綽有餘；對 高並發寫入（如大型交易系統）就不適合，該換 client-server DB。

Q3：SQLite 能撐多大？適合正式上線嗎？

A：單檔理論上限 281 TB，實務上 GB 等級非常舒適。完全可以正式上線——許多中小型網站、知名產品（如 Expensify）就直接用 SQLite。關鍵不是資料量，而是 並發寫入需求 和 是否需要跨機器存取。

Q4：SQLite 跟 MySQL，學哪個的 SQL 比較通用？

A：SQL 核心語法（SELECT/INSERT/JOIN/GROUP BY）幾乎共通，學會可互通。差異在：SQLite 動態型別、缺少部分 ALTER TABLE 功能、無 stored procedure、日期型別處理不同。學概念兩者皆可，正式環境再注意方言差異。

Q5：可以讓多台伺服器連同一個 SQLite 檔嗎？

A：不建議。SQLite 沒有網路層，跨機器只能靠共享檔案系統（NFS），但網路檔案系統的鎖定常出問題，容易資料庫損毀。需要跨機器 → 用 PostgreSQL/MySQL，或看 Litestream / rqlite / Turso 這類「把 SQLite 分散式化」的方案。

Q6：.db、.sqlite、.sqlite3 有差嗎？

A：沒有。副檔名只是慣例，SQLite 靠檔頭（前 16 bytes 的 magic string）辨識格式，叫什麼名字都能讀。

Q7：程式每次請求都開關連線，SQLite 會一直重開嗎？

A：分兩層看。SQLite 原生的 open/close 確實會做，但很便宜（只是開檔案，沒有網路握手），毫秒級。而「不會一直開開關關」的機制，是 驅動/框架的連線池——應用程式的 close() 只是把連線歸還池子，底層實體連線被重複使用而非真的關閉（如 Go 的 database/sql、SQLAlchemy QueuePool、Java HikariCP）。詳見 連線機制 → 程式一直開關連線，SQLite 真的一直開開關關嗎？。

總結

核心要點

SQLite = 嵌入式 + 零設定 + 單檔案 + 完整 SQL + ACID

本質：它不跟 MySQL 競爭，它跟 fopen() 競爭
      ── 是「應用程式內建的資料層」，不是「資料庫伺服器」

連線：開檔案，不是連網路；權限 = 檔案權限
並發：多讀可並行，寫入永遠單一序列（瓶頸在此）
型別：動態型別（建議用 STRICT 表變嚴格）

選用決策樹

資料和程式在同一台機器？
├─ 否 → 用 PostgreSQL / MySQL（需要網路存取）
└─ 是 → 寫入並發高嗎？
        ├─ 高 → 用 client-server DB
        └─ 不高 → ✅ SQLite（記得開 WAL）

三個關鍵問題

1. 要跨機器存取嗎？ → 要的話別用 SQLite
2. 寫入並發高嗎？ → 高的話別用 SQLite
3. 以上都不是？ → SQLite 是最省事、最可靠的選擇

快速參考

記憶口訣

SQLite 三個「單」：

1. 單檔案 — 整個資料庫就一個檔
2. 單行程 — 沒伺服器，跑在你的程式裡
3. 單寫入 — 並發瓶頸永遠是「同時只能一個寫」

建立日期：2026-06-08 最後更新：2026-06-08