面试题手册

SQLite 的 JSON 扩展提供了处理 JSON 数据的能力：JSON 扩展概述SQLite 3.38.0+ 内置 JSON1 扩展提供了一组函数用于创建、查询和操作 JSON 数据JSON 数据以 TEXT 类型存储在数据库中JSON 函数分类创建函数：jsonarray()、jsonobject()提取函数：jsonextract()、jsoneach()修改函数：jsonset()、jsonremove()查询函数：jsonarraylength()、json_type()路径函数：jsonpatch()、jsontree()创建 JSON 数据 -- 创建 JSON 对象 SELECT json_object('name', 'Alice', 'age', 30); -- 输出: {"name":"Alice","age":30} -- 创建 JSON 数组 SELECT json_array(1, 2, 3, 'four'); -- 输出: [1,2,3,"four"]提取 JSON 数据 -- 提取 JSON 字段 SELECT json_extract('{"name":"Alice","age":30}', '$.name'); -- 输出: "Alice" -- 提取嵌套字段 SELECT json_extract('{"user":{"name":"Alice"}}', '$.user.name'); -- 输出: "Alice"修改 JSON 数据 -- 设置或更新 JSON 字段 SELECT json_set('{"name":"Alice"}', '$.age', 30); -- 输出: {"name":"Alice","age":30} -- 删除 JSON 字段 SELECT json_remove('{"name":"Alice","age":30}', '$.age'); -- 输出: {"name":"Alice"}查询 JSON 数据 -- 获取 JSON 数组长度 SELECT json_array_length('[1,2,3,4]'); -- 输出: 4 -- 获取 JSON 字段类型 SELECT json_type('{"name":"Alice","age":30}', '$.age'); -- 输出: integer在表中使用 JSON -- 创建包含 JSON 字段的表 CREATE TABLE users ( id INTEGER PRIMARY KEY, data TEXT ); -- 插入 JSON 数据 INSERT INTO users (data) VALUES (json_object('name', 'Alice', 'tags', json_array('admin', 'user'))); -- 查询 JSON 数据 SELECT json_extract(data, '$.name') as name FROM users; -- 基于 JSON 字段过滤 SELECT * FROM users WHERE json_extract(data, '$.name') = 'Alice';JSON 路径表达式$：根对象.key：对象属性[index]：数组索引*：通配符..：递归下降性能考虑JSON 查询比直接查询列慢可以使用生成列和索引优化 JSON 查询 CREATE TABLE users ( id INTEGER PRIMARY KEY, data TEXT, name TEXT GENERATED ALWAYS AS (json_extract(data, '$.name')) STORED ); CREATE INDEX idx_name ON users(name);使用场景存储灵活的数据结构存储配置信息存储日志和元数据实现 NoSQL 风格的数据存储JSON 扩展使 SQLite 能够处理半结构化数据，增强了数据库的灵活性。

SQLite 的 CTE（Common Table Expressions，公用表表达式）提供了更灵活的查询方式：CTE 的概念CTE 是临时的命名结果集，在单个语句的执行范围内存在使复杂查询更易读、更易维护可以递归使用，实现层次查询SQLite 3.8.3+ 支持 CTECTE 语法 WITH cte_name AS ( cte_query ) SELECT * FROM cte_name;基本 CTE 示例 -- 简单 CTE WITH high_salary_employees AS ( SELECT * FROM employees WHERE salary > 50000 ) SELECT * FROM high_salary_employees ORDER BY salary DESC; -- 多个 CTE WITH dept_avg AS ( SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department ), high_avg_depts AS ( SELECT * FROM dept_avg WHERE avg_salary > 60000 ) SELECT * FROM high_avg_depts;递归 CTE -- 递归 CTE 语法 WITH RECURSIVE cte_name AS ( -- 初始查询（锚点成员） initial_query UNION ALL -- 递归查询（递归成员） recursive_query ) SELECT * FROM cte_name; -- 示例：生成数字序列 WITH RECURSIVE numbers(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM numbers WHERE n < 10 ) SELECT * FROM numbers; -- 示例：层次查询（组织结构） WITH RECURSIVE org_chart(id, name, manager_id, level) AS ( SELECT id, name, manager_id, 0 FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT * FROM org_chart ORDER BY level, id;CTE 的优势提高可读性：将复杂查询分解为逻辑部分代码重用：在同一查询中多次引用 CTE性能优化：某些情况下比子查询更高效递归查询：支持层次结构和图遍历CTE 与子查询的比较 -- 使用子查询 SELECT * FROM ( SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department ) WHERE avg_salary > 60000; -- 使用 CTE（更清晰） WITH dept_avg AS ( SELECT department, AVG(salary) as avg_salary FROM employees GROUP BY department ) SELECT * FROM dept_avg WHERE avg_salary > 60000;实际应用场景 -- 场景1：计算移动平均 WITH monthly_sales AS ( SELECT strftime('%Y-%m', order_date) as month, SUM(amount) as total FROM orders GROUP BY month ) SELECT month, total, AVG(total) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg FROM monthly_sales; -- 场景2：查找重复记录 WITH duplicate_emails AS ( SELECT email, COUNT(*) as cnt FROM users GROUP BY email HAVING cnt > 1 ) SELECT u.* FROM users u JOIN duplicate_emails d ON u.email = d.email ORDER BY u.email; -- 场景3：路径查找（图遍历） WITH RECURSIVE path(start_node, end_node, path, depth) AS ( SELECT id, id, CAST(id AS TEXT), 0 FROM nodes WHERE id = 1 UNION ALL SELECT p.start_node, e.to_node, p.path || '->' || e.to_node, p.depth + 1 FROM path p JOIN edges e ON p.end_node = e.from_node WHERE p.depth < 5 AND INSTR(p.path, e.to_node) = 0 ) SELECT * FROM path WHERE end_node = 10;CTE 的限制CTE 只在定义它的语句中可见不能在 CTE 中使用聚合函数和窗口函数的混合递归 CTE 需要明确的终止条件性能考虑CTE 通常被优化器视为内联视图递归 CTE 可能消耗大量资源复杂 CTE 可能需要手动优化考虑使用临时表处理大数据集CTE 是 SQLite 编写复杂查询的重要工具，特别是递归查询。

SQLite 的日期和时间函数提供了丰富的日期处理能力：日期时间存储格式SQLite 没有专门的日期时间类型通常以 TEXT（ISO8601 字符串）、REAL（Julian 日数）或 INTEGER（Unix 时间戳）存储推荐使用 TEXT 类型存储 ISO8601 格式：'YYYY-MM-DD HH:MM:SS'主要日期时间函数date()：返回日期time()：返回时间datetime()：返回日期时间julianday()：返回 Julian 日数strftime()：格式化日期时间unixepoch()：Unix 时间戳转换获取当前日期时间 -- 当前日期 SELECT date('now'); -- 输出: 2026-02-18 -- 当前时间 SELECT time('now'); -- 输出: 11:24:00 -- 当前日期时间 SELECT datetime('now'); -- 输出: 2026-02-18 11:24:00 -- 当前 Unix 时间戳 SELECT strftime('%s', 'now'); -- 输出: 1739869440日期时间计算 -- 加减天数 SELECT date('now', '+7 days'); SELECT date('now', '-1 month'); -- 加减时间 SELECT datetime('now', '+3 hours', '-30 minutes'); -- 计算日期差 SELECT julianday('now') - julianday('2026-01-01');日期时间格式化 -- 自定义格式 SELECT strftime('%Y-%m-%d %H:%M:%S', 'now'); -- 常用格式说明符 -- %Y: 4位年份 -- %m: 月份 (01-12) -- %d: 日期 (01-31) -- %H: 小时 (00-23) -- %M: 分钟 (00-59) -- %S: 秒 (00-59) -- %w: 星期 (0-6, 0=周日) -- %j: 一年中的第几天 (001-366)日期时间解析 -- 解析字符串为日期时间 SELECT datetime('2026-02-18 11:24:00'); -- 从 Unix 时间戳转换 SELECT datetime(1739869440, 'unixepoch'); -- 从 Julian 日数转换 SELECT datetime(2460585.974, 'julianday');日期时间比较 -- 比较日期 SELECT * FROM orders WHERE order_date > date('now', '-30 days'); -- 日期范围查询 SELECT * FROM events WHERE event_date BETWEEN date('now') AND date('now', '+7 days');实际应用场景 -- 创建订单表 CREATE TABLE orders ( id INTEGER PRIMARY KEY, order_date TEXT, status TEXT ); -- 插入当前时间 INSERT INTO orders (order_date, status) VALUES (datetime('now'), 'pending'); -- 查询今天的订单 SELECT * FROM orders WHERE date(order_date) = date('now'); -- 查询本周的订单 SELECT * FROM orders WHERE order_date >= date('now', 'weekday 0', '-7 days');时区处理 -- UTC 时间 SELECT datetime('now', 'utc'); -- 本地时间 SELECT datetime('now', 'localtime'); -- 时区转换 SELECT datetime('now', '+8 hours');性能优化为日期列创建索引使用函数包装列会导致索引失效考虑使用生成列存储格式化日期避免在 WHERE 子句中使用复杂日期函数SQLite 的日期时间函数功能强大，能够满足大多数日期处理需求。

SQLite 的全文搜索（FTS）功能提供高效的文本搜索能力：FTS 扩展模块SQLite 提供 FTS3、FTS4、FTS5 三个版本FTS5 是最新版本，功能最强大需要在编译时启用相应的扩展创建 FTS 表 -- 使用 FTS5 创建全文搜索表 CREATE VIRTUAL TABLE articles_fts USING fts5(title, content); -- 使用 FTS4 创建全文搜索表 CREATE VIRTUAL TABLE articles_fts USING fts4(title, content);插入数据 -- 插入数据到 FTS 表 INSERT INTO articles_fts (title, content) VALUES ('SQLite Tutorial', 'SQLite is a lightweight database...');全文搜索查询 -- 简单搜索 SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite'; -- 短语搜索 SELECT * FROM articles_fts WHERE articles_fts MATCH '"lightweight database"'; -- 布尔搜索 SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite AND database'; -- 前缀搜索 SELECT * FROM articles_fts WHERE articles_fts MATCH 'data*';FTS5 高级功能外部内容表：将 FTS 表与普通表关联 CREATE TABLE articles(id, title, content); CREATE VIRTUAL TABLE articles_fts USING fts5(title, content, content='articles', content_rowid='id');触发器自动同步：使用触发器保持 FTS 表与源表同步排名函数：使用 bm25() 函数进行结果排序性能优化为大型文档创建 FTS 索引使用批量插入提高性能定期优化 FTS 表 INSERT INTO articles_fts(articles_fts) VALUES('optimize');使用场景文档搜索系统日志分析产品搜索内容管理系统代码搜索FTS 功能使 SQLite 能够处理复杂的文本搜索需求，是构建搜索功能的重要工具。

SQLite 的内存数据库提供了一种快速的数据存储方案：创建内存数据库使用特殊的数据库名称 :memory:数据仅存储在内存中，不写入磁盘 -- 创建内存数据库连接 sqlite3 :memory:内存数据库的特点极快的读写速度：没有磁盘 I/O 开销临时性：数据库关闭后数据丢失适合缓存：用于临时存储和计算结果事务支持：支持完整的事务功能使用场景临时数据处理和转换复杂查询的中间结果存储单元测试和集成测试缓存频繁访问的数据数据导入导出的临时存储内存数据库与磁盘数据库结合可以将磁盘数据库的表附加到内存数据库 ATTACH DATABASE 'disk.db' AS disk_db;在内存数据库中进行复杂操作，然后将结果写回磁盘共享缓存模式多个连接可以共享同一个内存数据库使用 cache=shared 连接参数 sqlite3 "file::memory:?cache=shared"性能考虑内存数据库性能远超磁盘数据库适合读密集型操作大量写入时可能受限于内存带宽需要注意内存使用量，避免内存溢出限制和注意事项数据不持久，关闭后丢失受限于可用内存大小不适合长期存储重要数据需要定期将数据持久化到磁盘内存数据库是 SQLite 的强大功能，特别适合需要高性能临时数据存储的场景。

SQLite 的事务机制遵循 ACID 原则：原子性（Atomicity）事务中的所有操作要么全部成功，要么全部失败使用 BEGIN TRANSACTION 开始事务，COMMIT 提交事务，ROLLBACK 回滚事务SQLite 使用预写日志（WAL）或回滚日志来保证原子性一致性（Consistency）事务执行前后，数据库必须保持一致状态所有约束（主键、外键、唯一约束、检查约束）在事务提交时被验证违反约束的操作会导致事务回滚隔离性（Isolation）SQLite 默认使用串行化隔离级别读操作不会阻塞其他读操作写操作会获得数据库的排他锁，阻止其他写操作支持多种隔离模式：DEFERRED、IMMEDIATE、EXCLUSIVE持久性（Durability）事务提交后，对数据库的修改是永久性的数据写入磁盘后才返回提交成功使用同步模式（synchronous）控制数据持久性级别事务控制命令 BEGIN TRANSACTION; -- 开始事务 -- 执行 SQL 操作 COMMIT; -- 提交事务 -- 或 ROLLBACK; -- 回滚事务自动事务如果没有显式开始事务，SQLite 会为每个语句自动创建事务建议在复杂操作中使用显式事务以提高性能和保证数据一致性保存点（Savepoints）支持嵌套事务和部分回滚使用 SAVEPOINT 创建保存点，RELEASE SAVEPOINT 释放，ROLLBACK TO SAVEPOINT 回滚到指定点SQLite 的事务机制确保了数据的安全性和一致性，特别适合需要强一致性保证的应用场景。

SQLite 的 WAL（Write-Ahead Logging）模式是一种重要的性能优化机制：WAL 模式原理传统回滚日志模式：写操作直接修改数据库文件，使用回滚日志记录变更WAL 模式：写操作将变更写入 WAL 文件，而不是直接修改数据库文件检查点（Checkpoint）时将 WAL 文件的内容合并到主数据库文件WAL 模式的优势提高并发性：读操作不会阻塞写操作，写操作不会阻塞读操作减少磁盘 I/O：写入操作通常是顺序的，比随机写入更快更好的崩溃恢复：WAL 文件包含所有未提交的变更，恢复更可靠提高写入性能：多个写入可以批量提交，减少同步操作启用 WAL 模式 PRAGMA journal_mode = WAL;WAL 文件管理WAL 文件与数据库文件在同一目录下，后缀为 -wal 和 -shm-wal 文件存储实际的 WAL 数据-shm 文件是共享内存文件，用于协调并发访问检查点（Checkpoint）机制自动检查点：当 WAL 文件达到一定大小时自动触发手动检查点：使用 PRAGMA wal_checkpoint(TRUNCATE); 手动触发检查点将 WAL 文件中的有效变更合并到主数据库文件WAL 模式的限制不支持某些旧的 SQLite 版本在网络文件系统上可能不可靠需要额外的磁盘空间存储 WAL 文件某些情况下可能导致数据库文件增长适用场景高并发读写场景需要更好的崩溃恢复能力写入密集型应用移动应用和桌面应用WAL 模式是 SQLite 推荐的默认配置，能够显著提高大多数应用场景下的性能。

SQLite 是一个轻量级的嵌入式数据库引擎，具有以下主要特点：零配置：不需要安装或配置数据库服务器，直接集成到应用程序中无服务器架构：数据库引擎直接嵌入到应用程序中，无需独立的数据库服务器进程单一文件存储：整个数据库存储在一个单一的磁盘文件中，便于备份和迁移跨平台：支持 Windows、Linux、macOS 等多种操作系统事务支持：支持 ACID（原子性、一致性、隔离性、持久性）事务特性自包含：不需要任何外部依赖，编译后即可运行开源免费：属于公共领域，可自由使用和分发高性能：对于中小型应用，性能优于许多客户端/服务器数据库SQLite 适用于移动应用、桌面应用、嵌入式系统、小型网站等场景，但不适合高并发、多用户同时写入的大型企业级应用。

SQLite 在移动开发中有广泛的应用和特定的最佳实践：SQLite 在移动开发中的优势轻量级：占用资源少，适合移动设备零配置：无需安装和配置数据库服务器本地存储：数据存储在设备本地，离线可用跨平台：iOS、Android、React Native 等都支持移动平台集成 // iOS (Swift) - 使用 SQLite import SQLite3 let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0] + "/database.db" var db: OpaquePointer? if sqlite3_open(dbPath, &db) == SQLITE_OK { // 数据库打开成功 } // Android (Kotlin) - 使用 SQLite class DatabaseHelper(context: Context) : SQLiteOpenHelper(context, "database.db", null, 1) { override fun onCreate(db: SQLiteDatabase) { db.execSQL("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)") } override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { db.execSQL("DROP TABLE IF EXISTS users") onCreate(db) } }移动应用数据库设计最佳实践简化表结构：避免过度规范化，减少 JOIN 操作使用索引：为常用查询字段创建索引批量操作：使用事务批量插入数据分页加载：使用 LIMIT 和 OFFSET 分页查询性能优化技巧 // Swift - 使用事务批量插入 sqlite3_exec(db, "BEGIN TRANSACTION", nil, nil, nil) for user in users { let sql = "INSERT INTO users (name) VALUES ('\(user.name)')" sqlite3_exec(db, sql, nil, nil, nil) } sqlite3_exec(db, "COMMIT", nil, nil, nil) // Kotlin - 使用预编译语句 val stmt = db.compileStatement("INSERT INTO users (name) VALUES (?)") users.forEach { user -> stmt.clearBindings() stmt.bindString(1, user.name) stmt.executeInsert() }数据同步策略增量同步：只同步变更的数据时间戳标记：使用 updated_at 字段跟踪变更冲突解决：实现冲突检测和解决机制后台同步：在后台线程执行同步操作离线支持本地缓存：将服务器数据缓存到本地 SQLite离线队列：将离线操作存储在队列中，在线时同步数据合并：实现本地和服务器数据的合并逻辑数据安全 // iOS - 使用 SQLCipher 加密 let key = "encryption_key".data(using: .utf8) sqlite3_key(db, key, Int32(key!.count)) // Android - 使用 SQLCipher SQLiteDatabase.loadLibs(context) val db = SQLiteDatabase.openOrCreateDatabase(dbPath, "encryption_key", null)数据库版本管理 // Android - 数据库升级 override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { if (oldVersion < 2) { db.execSQL("ALTER TABLE users ADD COLUMN email TEXT") } if (oldVersion < 3) { db.execSQL("CREATE INDEX idx_email ON users(email)") } }常见问题解决数据库锁定：使用 WAL 模式提高并发性能问题：使用 EXPLAIN QUERY PLAN 分析查询内存泄漏：及时关闭数据库连接和游标数据丢失：定期备份数据库ORM 框架选择iOS：Core Data、Realm、GRDBAndroid：Room、Realm、GreenDAOReact Native：react-native-sqlite-storage、WatermelonDBSQLite 是移动应用本地数据存储的首选方案，掌握其最佳实践对移动开发者至关重要。

SQLite 提供多种备份和恢复方法：文件复制备份最简单的备份方式：直接复制数据库文件需要确保没有写入操作正在进行适合小型数据库或离线备份 cp database.db database_backup.db在线备份 API使用 SQLite 的备份 API 进行在线备份不需要停止数据库服务支持增量备份 -- 命令行工具 sqlite3 source.db ".backup backup.db"VACUUM INTO 备份使用 VACUUM INTO 命令创建数据库副本同时进行数据库优化和碎片整理 VACUUM INTO 'backup.db';导出 SQL 脚本使用 .dump 命令导出为 SQL 脚本可以选择性导出特定表或数据 sqlite3 database.db ".dump" > backup.sql恢复方法从文件备份恢复：直接复制备份文件从 SQL 脚本恢复：执行导出的 SQL 脚本 sqlite3 database.db < backup.sql增量备份使用 WAL 模式时，可以备份 WAL 文件定期执行检查点后进行完整备份结合 WAL 文件实现时间点恢复备份最佳实践定期执行自动备份保留多个历史备份版本验证备份文件的完整性测试恢复流程确保可用性考虑加密敏感数据跨平台备份SQLite 数据库文件是跨平台的可以在不同操作系统之间复制数据库文件注意文件路径和权限问题选择合适的备份策略取决于应用的重要性、数据变更频率和恢复时间要求。