RyB 知识库

SQLite 学习笔记

从零开始的 SQLite 学习笔记，覆盖核心概念、SQL 用法、应用场景与最佳实践。

图表在 /SQLite学习笔记/diagrams/ 目录。

1 · SQLite 概述

1.1 什么是 SQLite

  • SQLite 是世界上最轻量级的关系型数据库
  • 无需服务器：整个数据库就是一个文件（或内存）
  • 零配置：不需要安装、不需要启动服务、不需要管理员
  • 嵌入式数据库：直接嵌入应用程序进程中，不是独立的数据库服务
  • 2000 年由 D. Richard Hipp 发布，公共领域（无版权限制）
TIP

📌 打个比方：MySQL/PostgreSQL 像银行金库——需要管理员、需要保安、需要排队窗口。SQLite 像保险箱——放在你办公室里，钥匙在你手里，随开随关，不需要任何人帮忙。

📌 SQLite 无处不在：你的手机（Android/iOS app 数据存储）、浏览器（Chrome 的 cookie/history）、Python（标准库自带）、飞机娱乐系统、IoT 设备……世界上部署量最大的数据库，没有之一。

1.2 SQLite vs MySQL/PostgreSQL

图 1 \xb7 SQLite vs MySQL/PostgreSQL 架构对比

特性 SQLite MySQL / PostgreSQL
架构 嵌入式（进程内） 客户端-服务器（C/S）
安装 零安装 需安装配置
服务 无独立进程 需启动数据库服务
端口 无 3306 / 5432
存储 单个文件 多文件 + 内存
并发 单写多读（WAL 模式） 高并发读写
网络访问 不支持（本地） 支持
适用规模 GB 级 TB 级
用户管理 无 完善的权限系统
成本 零 需运维
TIP

📌 什么时候用 SQLite：① 移动端 app 本地存储 ② 桌面应用数据 ③ 小型网站（日 PV < 10万）④ 配置/缓存存储 ⑤ 测试/原型 ⑥ 数据分析（替代 CSV）⑦ 嵌入式设备

📌 什么时候不该用 SQLite：① 高并发写入（多线程频繁写）② 需要网络远程访问 ③ 数据量 TB 级 ④ 需要复杂用户权限管理 ⑤ 需要存储过程/触发器等高级特性

2 · 安装与使用

2.1 安装

# SQLite 通常已预装
sqlite3 --version

# Ubuntu/Debian（如果没有）
sudo apt install sqlite3

# Windows：下载 sqlite-tools zip，解压即可用
# Python 标准库自带
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

2.2 命令行使用

# 创建/打开数据库
sqlite3 mydb.sqlite

# 常用命令
.tables                          -- 列出所有表
.schema users                    -- 查看建表语句
.headers on                      -- 显示列名
.mode column                      -- 列对齐显示
SELECT * FROM users;             -- 执行 SQL
.quit                            -- 退出

# 直接执行 SQL
sqlite3 mydb.sqlite "SELECT COUNT(*) FROM users;"

# 导入 CSV
sqlite3 mydb.sqlite
.mode csv
.import data.csv mytable
TIP

📌 SQLite 数据库就是一个文件：mydb.sqlite 就是整个数据库。复制这个文件 = 备份。删除这个文件 = 销毁。不需要 mysqldump，不需要 pg_dump。

3 · SQL 基础

3.1 建库建表

-- SQLite 不需要 CREATE DATABASE，打开文件即创建
-- 建表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自增主键
    username TEXT NOT NULL UNIQUE,
    email TEXT,
    age INTEGER DEFAULT 0,
    metadata TEXT,                           -- JSON 存为 TEXT
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 内存数据库（连接关闭即消失）
sqlite3 :memory:

3.2 CRUD

-- 插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@test.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@test.com'), ('carol', 'carol@test.com');

-- 查询
SELECT * FROM users WHERE age > 18 ORDER BY id DESC LIMIT 10;
SELECT COUNT(*) FROM users;

-- 更新
UPDATE users SET email = 'new@test.com' WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

3.3 SQLite 特色语法

-- UPSERT（不存在则插入，存在则更新）
INSERT INTO users (username, email) VALUES ('alice', 'new@test.com')
ON CONFLICT(username) DO UPDATE SET email = excluded.email;

-- WITHOUT ROWID（无隐式行 ID，节省空间）
CREATE TABLE config (
    key TEXT PRIMARY KEY,
    value TEXT
) WITHOUT ROWID;

-- CREATE TABLE AS（从查询建表）
CREATE TABLE active_users AS
SELECT * FROM users WHERE age > 18;

-- ATTACH DATABASE（附加另一个数据库文件）
ATTACH DATABASE 'archive.sqlite' AS archive;
SELECT * FROM archive.old_users;
TIP

📌 UPSERT 是 SQLite 的实用功能：ON CONFLICT DO UPDATE 一步完成"不存在则插入，存在则更新"。MySQL 用 INSERT ... ON DUPLICATE KEY UPDATE，PostgreSQL 用 INSERT ... ON CONFLICT DO UPDATE，语法略有不同。

📌 WITHOUT ROWID：默认 SQLite 每张表有个隐式的 rowid 列。如果主键就是你要查的列（如配置表的 key），用 WITHOUT ROWID 可以省掉 rowid 的存储和索引开销。

4 · 数据类型

4.1 SQLite 的动态类型系统

SQLite 与其他数据库最大的区别：**类型亲和（Type Affinity）**而非严格类型。

声明类型 亲和类型 说明
INT, INTEGER INTEGER 整数
TEXT, VARCHAR, CHAR TEXT 文本
BLOB BLOB 二进制
REAL, FLOAT, DOUBLE REAL 浮点数
（无声明）/ NUMERIC NUMERIC 数字（整数或浮点） 
-- SQLite 允许混存（但建议不要这样做）
CREATE TABLE test (val INTEGER);
INSERT INTO test VALUES (42);        -- 整数
INSERT INTO test VALUES ('hello');   -- 也行！SQLite 不报错
INSERT INTO test VALUES (3.14);      -- 也行
TIP

📌 动态类型的利与弊：利——灵活，迁移方便。弊——可能存入意外类型导致 bug。建议：虽然 SQLite 允许混存，但应用层应严格保证类型一致。

4.2 存储类

SQLite 实际有 5 种存储类：NULL、INTEGER、REAL、TEXT、BLOB。没有 Boolean，用 0（false）和 1（true）。没有 Date 类型，用 TEXT（ISO 8601）、INTEGER（Unix 时间戳）或 REAL（Julian Day）存储。

5 · 索引

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email_age ON users(email, age);   -- 联合索引

-- 查看索引
.indices users

-- 查看查询计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'alice';

-- 删除索引
DROP INDEX idx_username;
TIP

📌 SQLite 索引也是 B-Tree，与 MySQL 类似。最左前缀原则同样适用。但 SQLite 没有 EXPLAIN 的详细输出（只有 QUERY PLAN），分析能力不如 MySQL/PG。

6 · 事务与并发

图 2 \xb7 SQLite 并发模型

6.1 事务

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 或 ROLLBACK;

SQLite 的事务是 ACID 合规的。默认每个 SQL 语句自动包裹在隐式事务中。

6.2 并发模式

模式 说明 并发能力
DELETE（默认） 写时删除回滚日志 读+写互斥
WAL（推荐） Write-Ahead Logging 读不阻塞写，写不阻塞读
TRUNCATE 类似 DELETE，提交后截断日志 同 DELETE
MEMORY 日志在内存 最快，但崩溃可能丢数据 
-- 开启 WAL 模式（推荐）
PRAGMA journal_mode = WAL;

-- 设置忙等待超时（毫秒）
PRAGMA busy_timeout = 5000;    -- 等待 5 秒而不是立即报错
TIP

📌 WAL 模式是 SQLite 并发的关键：默认模式下读写互斥，一个线程写时其他线程连读都不行。WAL 模式下读和写可以并行——读操作读旧快照，写操作追加到 WAL 文件。生产环境务必开启 WAL。

📌 SQLite 的并发限制：即使 WAL 模式，同时只能有一个写操作。多个读可以并发，但写必须排队。这是 SQLite 不适合高并发写入的根本原因。

6.3 锁层级

UNLOCKED → SHARED（读） → RESERVED（准备写） → PENDING（等读完） → EXCLUSIVE（写）

7 · 编程接口

7.1 Python（标准库自带）

import sqlite3

# 连接（文件不存在自动创建）
conn = sqlite3.connect('mydb.sqlite')
conn.row_factory = sqlite3.Row    # 结果像字典一样访问

# 建表
conn.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL,
        email TEXT
    )
''')

# 插入
conn.execute('INSERT INTO users (username, email) VALUES (?, ?)',
             ('alice', 'alice@test.com'))
conn.commit()

# 查询
rows = conn.execute('SELECT * FROM users WHERE username = ?', ('alice',)).fetchall()
for row in rows:
    print(row['id'], row['username'], row['email'])

# 使用上下文管理器（自动提交/回滚）
with conn:
    conn.execute('UPDATE users SET email = ? WHERE id = ?', ('new@test.com', 1))

conn.close()
TIP

📌 参数化查询防注入：永远用 ? 占位符，不要用字符串拼接 SQL。f"SELECT * FROM users WHERE name = '{name}'" 有 SQL 注入风险，WHERE name = ? 则安全。

7.2 C/C++

#include <sqlite3.h>

sqlite3* db;
sqlite3_open("mydb.sqlite", &db);

char* errmsg = NULL;
sqlite3_exec(db, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)",
             NULL, NULL, &errmsg);

sqlite3_stmt* stmt;
sqlite3_prepare_v2(db, "SELECT * FROM users WHERE id = ?", -1, &stmt, NULL);
sqlite3_bind_int(stmt, 1, 42);

while (sqlite3_step(stmt) == SQLITE_ROW) {
    const unsigned char* name = sqlite3_column_text(stmt, 1);
    printf("name: %s\n", name);
}

sqlite3_finalize(stmt);
sqlite3_close(db);

7.3 Node.js

const Database = require('better-sqlite3');
const db = new Database('mydb.sqlite');

// 建表
db.exec(`CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)`);

// 插入（同步 API，更简单）
const insert = db.prepare('INSERT INTO users (name) VALUES (?)');
insert.run('alice');

// 查询
const rows = db.prepare('SELECT * FROM users').all();
console.log(rows);

db.close();

8 · 应用场景

8.1 移动端存储

# Android（Java/Kotlin）
# Room ORM 底层就是 SQLite
# iOS（Swift）
# Core Data / FMDB 底层也是 SQLite

8.2 桌面应用配置

# 用 SQLite 替代 JSON/INI 配置文件
conn = sqlite3.connect('app_config.sqlite')
conn.execute('CREATE TABLE IF NOT EXISTS config (key TEXT PRIMARY KEY, value TEXT)')
conn.execute('INSERT OR REPLACE INTO config VALUES (?, ?)', ('theme', 'dark'))

8.3 数据分析

# 把 CSV 导入 SQLite 做分析
import sqlite3, csv
conn = sqlite3.connect(':memory:')

conn.execute('''CREATE TABLE sales (
    date TEXT, product TEXT, amount REAL
)''')

with open('sales.csv') as f:
    reader = csv.reader(f)
    next(reader)  # skip header
    conn.executemany('INSERT INTO sales VALUES (?, ?, ?)', reader)

# 用 SQL 分析
for row in conn.execute('SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY 2 DESC'):
    print(row)

8.4 测试数据库

# 单元测试用内存数据库，隔离且快速
def test_user_creation():
    conn = sqlite3.connect(':memory:')
    conn.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
    conn.execute('INSERT INTO users (name) VALUES (?)', ('test',))
    assert conn.execute('SELECT COUNT(*) FROM users').fetchone()[0] == 1

9 · 性能优化

9.1 批量插入

# 慢：逐条插入，每次都提交
for item in items:
    conn.execute('INSERT INTO t VALUES (?)', (item,))
    conn.commit()

# 快：批量插入 + 单次提交
conn.executemany('INSERT INTO t VALUES (?)', items)
conn.commit()

# 更快：事务包裹
conn.execute('BEGIN')
conn.executemany('INSERT INTO t VALUES (?)', items)
conn.commit()
TIP

📌 批量插入性能差异巨大：1 万条数据，逐条提交可能要 10 秒，批量提交只需 0.1 秒。因为 SQLite 每次提交都要 fsync 刷盘，批量提交只刷一次。

9.2 PRAGMA 调优

PRAGMA journal_mode = WAL;        -- WAL 模式（并发更好）
PRAGMA synchronous = NORMAL;      -- WAL 模式下 NORMAL 足够安全且更快
PRAGMA cache_size = -64000;       -- 64MB 缓存（负数=KB）
PRAGMA temp_store = MEMORY;       -- 临时表存内存
PRAGMA mmap_size = 268435456;     -- 256MB 内存映射 I/O
PRAGMA 默认值 推荐值 说明
journal_mode DELETE WAL 读写并发
synchronous FULL NORMAL WAL 下安全且快
cache_size -2000（2MB） -64000（64MB） 更大缓存
temp_store 0 MEMORY 临时表在内存
mmap_size 0 268435456 内存映射 I/O

9.3 其他优化

  • 使用 WITHOUT ROWID：主键是文本/UUID 时省空间
  • 合理建索引：查询多的列建索引，写入多的表少建索引
  • 避免 SELECT COUNT(*)：大表 COUNT 慢，维护一个计数器表
  • 用 INSTEAD OF 触发器实现可更新的视图

10 · 工具与生态

10.1 GUI 工具

工具 说明
DB Browser for SQLite 免费开源 GUI（推荐）
DBeaver 多数据库 GUI，支持 SQLite
TablePlus 现代化数据库 GUI
VS Code SQLite 插件 编辑器内查看 SQLite

10.2 命令行工具

# sqlite3 CLI
sqlite3 mydb.sqlite

# 导出 SQL 脚本
sqlite3 mydb.sqlite .dump > backup.sql

# 导入 SQL 脚本
sqlite3 newdb.sqlite < backup.sql

# 压缩数据库（VACUUM）
sqlite3 mydb.sqlite "VACUUM;"

# 查看数据库大小
ls -lh mydb.sqlite

10.3 ORM/库

语言 库
Python sqlite3（标准库）、SQLAlchemy
Node.js better-sqlite3、sqlite3
C/C++ sqlite3.h（C API）
Rust rusqlite
Go modernc.org/sqlite（纯 Go）
Java JDBC + sqlite-jdbc

附录 · 速查表

操作 命令/语法
创建数据库 sqlite3 mydb.sqlite
内存数据库 sqlite3 :memory:
查看表 .tables
查看建表语句 .schema table_name
导出 .dump > backup.sql
开启 WAL PRAGMA journal_mode = WAL;
压缩 VACUUM;
UPSERT INSERT ... ON CONFLICT DO UPDATE
附加数据库 ATTACH DATABASE 'file' AS alias;
参数化 WHERE col = ?
本页目录