4）SQLite

🌱 第 1 题（基础认知）

Q1：SQLite 是什么？和 MySQL、PostgreSQL 相比，有哪些主要特点和使用场景？

📝 标准答案：

SQLite 是一个轻量级的、嵌入式关系型数据库，它不需要独立的服务器进程或系统，数据库就是一个单一文件。

主要特点：

• 零配置（无需安装、配置服务）；
• 数据库存储为一个单一 .db 文件；
• 体积小，嵌入式部署（不到 1MB）；
• 支持绝大多数 SQL92 语法；
• 跨平台支持极好；
• 支持事务（ACID）、索引、触发器、视图等基本功能。

与 MySQL/PostgreSQL 区别：

特点	SQLite	MySQL / PostgreSQL
部署模式	嵌入式	C/S 架构，需独立服务
并发支持	单写多读（适合低并发）	高并发读写
数据存储	单文件	多文件或服务端管理
用途	移动端、IoT、小应用	Web 后端、企业级应用
性能	高速，但不适合高并发	面向多用户并发场景

典型使用场景：

• 移动应用（如微信小程序、Android/iOS）；
• 桌面软件（浏览器如 Chrome、Edge 的本地缓存）；
• 嵌入式系统（如智能硬件）；
• 快速原型或测试数据库。

🌿 第 2 题（基本操作）

Q2：请说出 SQLite 中常见的基本 SQL 操作语句？

📝 标准答案：

SQLite 支持标准 SQL 操作，以下是常见操作：

1. 创建表

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2. 插入数据

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

3. 查询数据

SELECT * FROM users WHERE name LIKE 'A%';

4. 更新数据

UPDATE users SET email = 'alice@new.com' WHERE id = 1;

5. 删除数据

DELETE FROM users WHERE id = 1;

6. 创建索引

CREATE INDEX idx_user_email ON users(email);

7. 使用事务

BEGIN TRANSACTION;
-- 操作若干语句
COMMIT;

SQLite 还支持 PRAGMA 命令控制行为，例如：

PRAGMA journal_mode = WAL;

🌳 第 3 题（性能优化）

Q3：SQLite 在数据量大或并发写入时性能变差的原因是什么？如何优化？

📝 标准答案：

原因：

• SQLite 默认采用文件级锁，因此只能同时有一个写操作，多写会造成阻塞；
• 数据量大时，单个 .db 文件读取慢；
• 默认使用 DELETE 日志模式，性能较差；
• 没有连接池机制，频繁打开关闭连接影响效率；
• 未使用索引或查询语句不合理。

优化方式：

问题	优化策略
写入冲突	开启 WAL 模式（Write-Ahead Logging）提升并发读写性能
文件写入慢	合理使用事务，批量插入包在一个事务中
查询慢	添加合适的索引；避免 SELECT *；用 LIMIT 分页
数据膨胀	使用 VACUUM 清理空间
提升缓存	调整 PRAGMA cache_size 增大缓存页数量
日志模式	设置 PRAGMA journal_mode = WAL 替代默认 DELETE 模式
防止死锁	使用 busy_timeout 设置自动等待写锁释放时间

🌲 第 4 题（高级特性）

Q4：你是否使用过 SQLite 的触发器、视图或虚拟表？请举例说明。

📝 标准答案：

1. 触发器（Trigger）： 用于在表的插入、更新或删除时自动执行动作。

📌 示例：在用户删除时，把数据记录到日志表

CREATE TRIGGER log_user_delete
AFTER DELETE ON users
BEGIN
  INSERT INTO user_log (user_id, action, time)
  VALUES (OLD.id, 'DELETE', datetime('now'));
END;

2. 视图（View）： 用于创建只读的逻辑表，简化查询。

📌 示例：创建一个展示用户最近注册的视图

CREATE VIEW recent_users AS
SELECT * FROM users
WHERE created_at >= date('now', '-7 days');

3. 虚拟表（Virtual Table）+ FTS： 使用 Full-Text Search 实现全文搜索。

📌 示例：创建 FTS5 搜索表

CREATE VIRTUAL TABLE docs USING fts5(title, content);

然后可以进行全文搜索：

SELECT * FROM docs WHERE docs MATCH 'AI OR 技术';

🪵 第 5 题（应用设计）

Q5：假如你要开发一个跨平台笔记应用，使用 SQLite 存储数据，你会如何设计数据结构与同步机制？

📝 标准答案：

✅ 本地数据库设计：

CREATE TABLE notes (
  id TEXT PRIMARY KEY,           -- UUID，跨端同步不冲突
  title TEXT NOT NULL,
  content TEXT,
  updated_at INTEGER,            -- 使用时间戳做版本控制
  sync_status TEXT DEFAULT 'new' -- 'new', 'updated', 'synced'
);

✅ 同步机制设计：

• 使用 updated_at 判断数据是否需要同步；
• 增量同步策略：
  • 本地改动：上传到服务器；
  • 远程更新：覆盖本地数据；
• 解决冲突：
  • 基于最后更新时间；
  • 或使用版本号、操作日志解决冲突；
• 离线支持：
  • 所有操作先落地 SQLite；
  • 定时或联网后同步；

✅ 附加建议：

• 使用 WAL 模式防止读写阻塞；
• 通过 UUID 保证多端主键唯一；
• created_at 和 updated_at 做数据历史追踪；
• 可设计本地 operation_log 表，记录用户行为，实现撤销/恢复功能。