从零入门到移动开发实战

1. 什么是 SQLite？

SQLite 是"无服务器"的关系型数据库引擎。整个数据库就是一个 .db 文件，你把它放在哪都可以。它被嵌入在你的应用中，不需要安装、配置或管理。全球有超过 1 万亿个 SQLite 数据库在运行——你的手机里就有几十个。

关键特性

• 零配置：无需 CREATE DATABASE，打开文件即数据库
• 单文件：一个 .db 文件包含全部内容，可随意复制
• ACID 兼容：事务支持与大型数据库相同
• 大小：库文件 ~750KB，适合嵌入任何应用

2. SQLite vs 其他数据库

场景	SQLite	MySQL/PostgreSQL
移动 App 本地存储	✅ 完美	❌ 过重
桌面应用	✅ 完美	⚠️ 可能
Web 网站（低并发）	✅ 可用	✅ 推荐
Web 网站（高并发写入）	❌ 不适合	✅ 推荐
数据分析	⚠️ 中等数据集	✅ 大数据集
IoT / 嵌入式设备	✅ 完美	❌ 资源不足
原型开发	✅ 完美	⚠️ 需配置

3. 实战：Todo App 数据库设计

CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    color TEXT DEFAULT '#3498db',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER,
    title TEXT NOT NULL,
    description TEXT,
    priority INTEGER CHECK(priority BETWEEN 1 AND 5) DEFAULT 3,
    due_date DATE,
    completed BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- 复合索引
CREATE INDEX idx_tasks_project_priority ON tasks(project_id, priority);
CREATE INDEX idx_tasks_due ON tasks(due_date);

4. SQLite 独有技巧

日期时间处理

-- SQLite 没有 DATETIME 类型，用 TEXT/INTEGER 存储
SELECT
    date('now') AS today,
    datetime('now', '+7 days') AS next_week,
    strftime('%Y-%m-%d', due_date) AS formatted_date
FROM tasks;

全文搜索 (FTS5)

CREATE VIRTUAL TABLE tasks_fts USING fts5(title, description);

-- 与主表同步
CREATE TRIGGER tasks_fts_insert AFTER INSERT ON tasks BEGIN
    INSERT INTO tasks_fts (rowid, title, description)
    VALUES (new.id, new.title, new.description);
END;

-- 搜索
SELECT * FROM tasks_fts WHERE tasks_fts MATCH 'database OR SQL';

UPSERT（插入或更新）

INSERT INTO projects (name, color) VALUES ('学习', '#e74c3c')
ON CONFLICT(name) DO UPDATE SET color = excluded.color;

5. Python 实战：完整 Todo CLI

import sqlite3
import sys

DB = "todos.db"

def init():
    with sqlite3.connect(DB) as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS todos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task TEXT NOT NULL,
                done BOOLEAN DEFAULT 0
            )
        """)

def add(task):
    with sqlite3.connect(DB) as conn:
        conn.execute("INSERT INTO todos (task) VALUES (?)", (task,))

def list_todos():
    with sqlite3.connect(DB) as conn:
        rows = conn.execute(
            "SELECT id, task, done FROM todos ORDER BY id"
        ).fetchall()
    for r in rows:
        status = "✅" if r[2] else "⬜"
        print(f"{status} [{r[0]}] {r[1]}")

def done(tid):
    with sqlite3.connect(DB) as conn:
        conn.execute("UPDATE todos SET done=1 WHERE id=?", (tid,))

if __name__ == "__main__":
    init()
    cmd = sys.argv[1] if len(sys.argv) > 1 else "list"
    if cmd == "add":
        add(" ".join(sys.argv[2:]))
    elif cmd == "done":
        done(int(sys.argv[2]))
    list_todos()

思考题

1. SQLite 的 VACUUM 什么时候需要执行？会带来什么问题？
2. 为什么 SQLite 不适合高并发 Web 应用？WAL 模式能缓解到何种程度？
3. 把 SQLite .db 文件放到 Git 里作为"数据库即代码"的方案，你是否赞成？

必知命令

sqlite3 db.sqlite .dump >; backup.sql    # 导出
sqlite3 db.sqlite <; backup.sql          # 恢复
sqlite3 db.sqlite "PRAGMA integrity_check"  # 检查损坏
sqlite3 db.sqlite VACUUM                # 压缩数据库