SQLite PRAGMA

SQLite 的 PRAGMA 命令用于查询或设置数据库的运行时配置，不属于标准 SQL，但 SQLite 独有且非常实用。无需 COMMIT，立即生效。

---

基本语法

-- 查询
PRAGMA pragma_name;

-- 设置
PRAGMA pragma_name = value;
-- 或
PRAGMA pragma_name(value);

---

常用 PRAGMA 分类与示例

---

1. 数据库信息查询

PRAGMA	说明	示例
database_list	列出附加数据库	PRAGMA database_list;
table_info(table)	查看表结构	PRAGMA table_info(users);
index_list(table)	列出索引	PRAGMA index_list(products);
foreign_key_list(table)	外键信息	PRAGMA foreign_key_list(orders);

-- 查看 employees 表结构
PRAGMA table_info(employees);

输出示例：

cid | name       | type    | notnull | dflt_value | pk
----|------------|---------|---------|------------|----
0   | id         | INTEGER | 0       | NULL       | 1
1   | name       | TEXT    | 1       | NULL       | 0
2   | department | TEXT    | 0       | NULL       | 0

---

2. 性能优化

PRAGMA	说明	推荐值
cache_size	页面缓存大小（KB 或 -KB 为页数）	PRAGMA cache_size = -2000; → 2MB
journal_mode	日志模式	WAL（推荐写多读少）
synchronous	同步级别	NORMAL（平衡性能与安全）
temp_store	临时表存储	MEMORY（加速）

-- 启用 WAL 模式（提升并发写性能）
PRAGMA journal_mode = WAL;

-- 设置缓存为 2000 页（约 2MB）
PRAGMA cache_size = -2000;

---

3. 事务与安全

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

-- 检查当前状态
PRAGMA foreign_keys;
-- 返回 1（开）或 0（关）

重要：新建连接默认 foreign_keys = OFF，必须显式开启

---

4. 编码与兼容性

-- 查看数据库编码
PRAGMA encoding;
-- 通常返回 "UTF-8"

-- 设置编码（仅在空库建表前有效）
PRAGMA encoding = "UTF-8";

---

5. 自动真空（Auto Vacuum）

-- 启用自动清理删除空间
PRAGMA auto_vacuum = FULL;  -- 或 INCREMENTAL

-- 手动触发清理
VACUUM;

---

6. 读写权限与加密

-- 设置密码（需 SQLite 加密扩展 SEE）
PRAGMA key = 'your_password';

-- 更改密码
PRAGMA rekey = 'new_password';

---

7. 编译选项与版本

-- 查看 SQLite 版本
PRAGMA sqlite_version;

-- 查看编译选项
PRAGMA compile_options;

---

8. 临时配置（仅当前连接）

-- 仅当前会话生效
PRAGMA temp_store = MEMORY;
PRAGMA case_sensitive_like = ON;

---

实用组合脚本（生产推荐）

-- 性能优化配置（写入前执行）
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -2000;        -- 2MB 缓存
PRAGMA foreign_keys = ON;         -- 强制外键
PRAGMA temp_store = MEMORY;       -- 临时表用内存

---

动态查看当前配置

-- 自定义查询所有 PRAGMA
SELECT name, value FROM (
  SELECT 'journal_mode' AS name, '' AS value
  UNION ALL SELECT 'journal_mode', journal_mode FROM pragma_journal_mode()
  UNION ALL SELECT 'cache_size', cache_size FROM pragma_cache_size()
  UNION ALL SELECT 'foreign_keys', foreign_keys FROM pragma_foreign_keys()
) ORDER BY name;

---

注意事项

注意点	说明
PRAGMA 不跨连接共享	每个连接需重新设置
部分 PRAGMA 只读	如 sqlite_version
某些设置仅建库时有效	如 encoding, page_size
page_size 只能在空库设置	PRAGMA page_size = 4096;

---

快速参考表

用途	PRAGMA
查表结构	table_info(table)
启用外键	foreign_keys = ON
高性能写	journal_mode = WAL
查版本	sqlite_version
查索引	index_list(table)
加密	key = 'pass'

---

需要我帮你生成一个针对你数据库的 PRAGMA 配置脚本吗？
请提供使用场景（如：高并发写、只读查询、加密等）！