—含核心语法、并发控制、事务、性能优化与嵌入式实践

SQLite 是一款零配置、无服务、单文件、跨平台的嵌入式关系型数据库，广泛应用于移动设备、IoT、桌面软件及嵌入式 Linux 系统。本文全面梳理 SQLite3 的完整语法体系，并针对多线程/多进程并发访问、事务隔离、异常处理等关键场景提供深度解析与示例代码，助你从“会用”迈向“精通”。

---

一、基础语法概览

1. 数据类型（Dynamic Typing + Type Affinity）

SQLite 采用动态类型系统，但为兼容 SQL 标准，引入 Type Affinity（类型亲和性）：

声明类型	亲和性	存储类（实际存储）
INT , INTEGER	INTEGER	INTEGER / NULL
CHAR , TEXT, CLOB	TEXT	TEXT / NULL
BLOB	BLOB	BLOB / NULL
REAL , FLOAT, DOUBLE	REAL	REAL / INTEGER / NULL
其他（如 NUMERIC）	NUMERIC	按数值优先存储

✅ 示例：

CREATE TABLE sensors (

id INTEGER PRIMARY KEY,  -- 自增整数

    name TEXT NOT NULL,

value REAL,

data BLOB,               -- 二进制数据（如图像）

    created_at NUMERIC DEFAULT CURRENT_TIMESTAMP

);

2. 常用 DDL（数据定义语言）

-- 创建表（带约束）

CREATE TABLE users (

uid INTEGER PRIMARY KEY AUTOINCREMENT,

username TEXT UNIQUE NOT NULL CHECK(length(username) >= 3),

email TEXT COLLATE NOCASE,  -- 大小写不敏感比较

    is_active BOOLEAN DEFAULT 1 -- SQLite 无 BOOLEAN，用 0/1

);

-- 添加索引（加速查询）

CREATE INDEX idx_users_email ON users(email);

-- 修改表（SQLite 有限支持）

ALTER TABLE users ADD COLUMN last_login TEXT;

-- 注意：不能 DROP COLUMN 或修改列类型（需重建表）

-- 删除表

DROP TABLE IF EXISTS temp_data;

3. DML（数据操作语言）

插入（INSERT）

-- 单条插入

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');

-- 批量插入（高效！）

INSERT INTO sensors (name, value) 

VALUES ('temp1', 23.5), ('temp2', 24.1), ('humid1', 60.2);

-- 从另一表插入

INSERT INTO archive SELECT * FROM sensors WHERE created_at < '2023-01-01';

查询（SELECT）

-- 基础查询

SELECT username, email FROM users WHERE is_active = 1;

-- 聚合与分组

SELECT name, AVG(value) as avg_val 

    FROM sensors 

    GROUP BY name 

    HAVING avg_val > 20;

-- 分页（嵌入式常用）

SELECT * FROM logs ORDER BY ts DESC LIMIT 50 OFFSET 100;

-- 全文搜索（需启用 FTS5）

CREATE VIRTUAL TABLE docs USING fts5(content);

SELECT * FROM docs WHERE docs MATCH 'sqlite tutorial';

更新（UPDATE）与删除（DELETE）

-- 安全更新（建议加 WHERE）

UPDATE users SET last_login = datetime('now') WHERE uid = 123;

-- 删除旧数据

DELETE FROM logs WHERE ts < datetime('now', '-7 days');

-- 清空表（保留结构）

DELETE FROM temp_table;  -- 比 DROP+CREATE 快

二、事务与并发控制（核心重点）

1. 事务（ACID 保证）

SQLite 支持 BEGIN/COMMIT/ROLLBACK：

BEGIN IMMEDIATE;  -- 立即获取 RESERVED 锁（避免死锁）

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;           -- 若中间出错，自动 ROLLBACK

✅ 事务模式：

• BEGIN DEFERRED

  （默认）：首次读写时才加锁；

• BEGIN IMMEDIATE

  ：立即加 RESERVED 锁，防止写饥饿；

• BEGIN EXCLUSIVE

  ：直接加 EXCLUSIVE 锁（独占）。

2. 并发访问模型

（1）多线程 vs 多进程

场景	机制	限制
单进程多线程	共享 sqlite3* 句柄	需启用 SQLITE_OPEN_NOMUTEX + 应用层同步
多进程	文件锁（POSIX advisory locks）	同一数据库文件可被多进程读，但写互斥

（2）WAL 模式（Write-Ahead Logging）——强烈推荐

PRAGMA journal_mode = WAL;  -- 启用 WAL

✅ WAL 优势：

• 读写不阻塞：读者读 -shm 中的快照，写者追加到 -wal；

• 多进程并发写性能提升 3~5 倍；

• 崩溃恢复更快（只需截断 WAL）。

⚠️ WAL 注意事项：

• 生成 db-wal 和 db-shm 文件，必须与 .db 同目录；

• 长时间运行后需 checkpoint 以回收空间：

sqlite3_wal_checkpoint_v2(db, NULL, SQLITE_CHECKPOINT_TRUNCATE, NULL, NULL);

（3）处理 SQLITE_BUSY（锁冲突）

// C API 设置 busy handler

static int busy_handler(void *ptr, int tries) {

    if (tries > 50) return 0; // 放弃

    usleep(1000);             // 休眠 1ms

    return 1;                 // 重试

}

sqlite3_busy_handler(db, busy_handler, NULL);

// 或设置超时（毫秒）

sqlite3_busy_timeout(db, 5000); // 5秒超时

4）多线程安全模式（编译时决定）

模式	编译选项	使用方式
Serialized	SQLITE_THREADSAFE=1 （默认）	多线程共享同一 sqlite3* 安全
Multi-thread	SQLITE_THREADSAFE=2	每个线程独立 sqlite3*
Single-thread	SQLITE_THREADSAFE=0	仅单线程

✅ 嵌入式推荐：
使用 SQLITE_OPEN_NOMUTEX + 应用层 mutex，避免内部锁竞争：

pthread_mutex_lock(&db_mutex);

sqlite3_exec(db, "UPDATE ...", ...);

pthread_mutex_unlock(&db_mutex);

三、高级特性与优化技巧

1. PRAGMA 指令（运行时配置）

PRAGMA synchronous = FULL;      -- 安全（每次写入磁盘），但慢

PRAGMA synchronous = NORMAL;    -- 平衡（默认）

PRAGMA synchronous = OFF;       -- 快，但断电可能损坏

PRAGMA cache_size = -2000;      -- 2000KB page cache（负值表示 KB）

PRAGMA foreign_keys = ON;       -- 启用外键约束（默认关闭！）

PRAGMA integrity_check;         -- 检查数据库完整性

2. 性能优化建议

• 批量操作用事务

  ：1000 条 INSERT 无事务 ≈ 30s，有事务 ≈ 0.1s；

• 避免 SELECT *

  ：只取必要字段；

• 合理使用索引

  ：WHERE、JOIN、ORDER BY 字段建索引；

• 定期 ANALYZE

  ：ANALYZE; 更新统计信息，优化查询计划；

• VACUUM 谨慎使用

  ：重写整个 DB，耗 I/O（嵌入式 SD 卡磨损大）。

3. 异常与错误处理

错误码	含义	应对
SQLITE_BUSY	数据库锁定	重试或超时
SQLITE_FULL	磁盘满	清理数据、告警
SQLITE_CORRUPT	数据库损坏	从备份恢复
SQLITE_CONSTRAINT	约束违反（如 UNIQUE）	检查输入数据

✅ C API 错误检查模板：

int rc = sqlite3_exec(db, sql, callback, 0, &errmsg);

if (rc != SQLITE_OK) {

    fprintf(stderr, "SQL error: %s\n", errmsg);

    sqlite3_free(errmsg);

}

四、嵌入式 ARM Linux 专项建议

1. 文件系统选择：

   • 使用 F2FS 或 ext4 with journal=writeback 减少写放大；

   • 避免直接写 SD 卡根分区，挂载独立分区。

2. 断电保护：

   • 启用 PRAGMA synchronous=FULL；

   • 关键事务后调用 fsync() 或 sqlite3_wal_checkpoint_v2(..., TRUNCATE)。

3. 资源限制：

   • 编译时禁用 FTS5/RTREE：./configure --disable-fts5 --disable-rtree；

   • 设置 PRAGMA cache_size = -512（512KB 缓存）。

---

结语：小而美，稳而强

SQLite3 以其极简架构实现强大功能，但在并发、异常、嵌入式环境下，细节决定成败。掌握其事务机制、WAL 模式、锁行为与错误处理，方能在资源受限的 ARM Linux 设备上构建高可靠、低开销的数据存储方案。

记住：

• 永远用 Prepared Statements

  ；

• 批量操作必用事务

  ；

• 多进程首选 WAL 模式

  ；

• 断电场景宁可牺牲性能也要保数据

  。

SQLite 不仅是一个数据库，更是一种“在约束中追求极致”的工程哲学。

SQLite不止于轻量：揭秘万亿级部署背后的核心力量​

山海重光：当〈山海经〉的神兽踏进芯片，古老幻想在硅基世界涅槃重生