SQLite 是最广泛使用的嵌入式数据库，它以轻量级、零配置、无服务器的架构，广泛应用于移动应用、浏览器插件、嵌入式设备和中小型桌面程序。在高级应用层面，SQLite 提供了丰富的内部命令（如 PRAGMA）、优化机制、事务控制、触发器和复杂 SQL 支持，为开发者构建高效、安全的本地数据处理系统提供了强有力的支撑。

📌 本教程由 www.52kanjuqing.com 精心整理，仅此一次引用。

📚 目录

  1. SQLite 架构与内部机制概览
  2. 高级数据结构控制（ALTER、AUTOINCREMENT 等）
  3. 事务控制与并发机制
  4. 高级 SQL 特性（子查询、JOIN、UNION）
  5. 查询性能优化（索引、EXPLAIN、VACUUM）
  6. 自动控制逻辑：触发器的使用
  7. 安全保障机制（约束、注入防护）
  8. 时间与日期函数使用技巧
  9. 常用内建函数与表达式扩展
  10. 跨语言接口（C/C++、Java、PHP、Python、Perl）

1. SQLite 架构与内部机制概览

SQLite 使用单一文件存储所有数据结构、索引、触发器及视图，采用基于 B-tree 的表与索引结构，所有操作均可在原子事务中完成，符合 ACID 原则。

关键特性：

  • 无需单独安装数据库服务器
  • 内建事务与并发控制（支持 WAL 模式）
  • 高度兼容 SQL 标准
  • 内存缓存、磁盘同步机制灵活可调

🔗 SQLite 架构详解

2. 高级数据结构控制

SQLite 在 DDL（数据定义语言）方面提供了一定程度的灵活性：

  • ALTER TABLE：允许更改表名、添加新列（不支持删除列）
  • AUTOINCREMENT：可确保主键永不复用
  • 删除所有数据：没有 TRUNCATE 命令，可使用： DELETE FROM table_name; VACUUM;

🔗 ALTER TABLE 官方文档

3. 事务控制与并发机制

SQLite 提供完整的事务支持和隔离级别：

  • BEGIN TRANSACTION、COMMIT、ROLLBACK
  • 支持 SAVEPOINT 实现嵌套事务
  • WAL（写前日志）模式提升并发性能：
PRAGMA journal_mode = WAL;

🔗 SQLite Transaction Docs

4. 高级 SQL 特性：子查询、JOIN 与 UNION

SQLite 支持多种 SQL 合成逻辑：

子查询（Subquery）

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE price > 500);

JOIN 类型：

  • INNER JOIN
  • LEFT OUTER JOIN
  • CROSS JOIN
  • 不支持 FULL OUTER JOIN（可模拟）

UNION 子句：

SELECT name FROM students
UNION
SELECT name FROM teachers;

5. 查询性能优化

优化查询性能的关键包括合理使用索引、理解查询计划、重构数据页。

索引与 INDEXED BY

CREATE INDEX idx_email ON users(email);
SELECT * FROM users INDEXED BY idx_email WHERE email = 'x@a.com';

EXPLAIN 与查询分析：

EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 5;

VACUUM 重构数据库：

VACUUM;

🔗 SQLite Query Plan Explained

6. 自动化逻辑：触发器（TRIGGER）

触发器可实现自动日志记录、权限控制、表级操作响应等。

CREATE TRIGGER log_insert
AFTER INSERT ON users
BEGIN
  INSERT INTO audit_log (user_id, time) VALUES (NEW.id, datetime('now'));
END;

7. 安全保障机制

约束类型：

  • PRIMARY KEY
  • UNIQUE
  • CHECK
  • FOREIGN KEY（默认关闭，需启用）
PRAGMA foreign_keys = ON;

防止 SQL 注入：

使用参数绑定：

cursor.execute("SELECT * FROM users WHERE email = ?", (email,))

8. 日期与时间函数

SQLite 不支持原生 DATE 类型，而是使用文本格式和函数组合实现日期操作。

SELECT date('now');
SELECT datetime('now', '+1 day');
SELECT strftime('%Y-%m', 'now');

🔗 SQLite Date and Time Functions

9. 内建函数与表达式扩展

SQLite 自带大量函数：

  • 字符串：LENGTH()、SUBSTR()、REPLACE()
  • 聚合：COUNT()、SUM()、AVG()、GROUP_CONCAT()
  • 数值：ROUND()、ABS()、RANDOM()

可通过 sqlite3_create_function()（C 语言）扩展自定义函数。

10. 多语言接口支持

SQLite 提供 C API 核心接口，同时通过驱动或绑定模块支持各类语言：

语言接口/驱动官方支持
C/C++sqlite3.h✅
Pythonsqlite3（内置）✅
JavaSQLite JDBC✅
PHPPDO_SQLITE✅
PerlDBD::SQLite✅

🔗 出站参考链接

🧩 站内推荐（www.52kanjuqing.com）

📚 参考资料

  1. Richard Hipp. SQLite Documentation. https://www.sqlite.org/docs.html
  2. D. K. Sahoo. The Definitive Guide to SQLite. Apress.
  3. Stack Overflow
  4. https://www.sqlitetutorial.net/
  5. W3Schools SQLite