SQLite - PRAGMA

使用 PRAGMA 配置 SQLite

PRAGMA 命令是 SQLite 特有的特殊命令，用于查询和修改数据库引擎的内部设置。这些设置可以控制从性能、持久性到数据完整性规则的所有内容。可以将它们视为数据库连接的运行时配置选项。

语法

查询某个设置的当前值：

PRAGMA setting_name;

更改当前连接的某个设置：

PRAGMA setting_name = value;

注意：PRAGMA 设置通常是连接（connection）特有的。每次打开新的数据库连接时，您都应该重新应用关键设置。

现代应用程序必备的 PRAGMA

尽管有数十种 PRAGMA，但有少数对于构建健壮（robust）且高性能的应用程序至关重要。

数据完整性

PRAGMA foreign_keys

控制外键约束（foreign key constraints）的强制执行。由于历史原因，此设置默认为 OFF。强烈建议为任何依赖关系完整性（relational integrity）的应用程序启用它。

-- 建议：为每个连接启用此设置，以确保数据完整性。
PRAGMA foreign_keys = ON;

性能与并发性

PRAGMA journal_mode

这是最重要的性能设置之一。它控制 SQLite 如何处理事务回滚和崩溃恢复。

DELETE (默认)：回滚日志（rollback journal）在每个事务结束时被删除。安全但可能较慢。
WAL (预写日志)：强烈建议用于大多数应用程序。 WAL 模式显著提高了并发性（读者不会阻塞写入者，反之亦然），并且通常快得多。更改首先写入单独的 -wal 文件。
MEMORY：将日志保存在内存中。速度快，但如果应用程序崩溃，事务将丢失。
OFF：完全禁用回滚日志。速度非常快，但崩溃或断电可能导致数据库损坏。请极其谨慎使用。

-- 将日志模式设置为预写日志（Write-Ahead Log），以获得更好的性能和并发性。
PRAGMA journal_mode = WAL;

PRAGMA synchronous

控制 SQLite 将数据写入磁盘的谨慎程度，以确保数据永久保存。安全性与速度之间存在权衡。

FULL (2)：最安全。保证数据在继续执行之前写入磁盘。可能较慢。
NORMAL (1)：在使用 WAL 模式时的默认值。在关键时刻进行同步，提供了安全性与性能的良好平衡。
OFF (0)：SQLite 将数据传递给操作系统，但不等待其写入磁盘。速度快得多，但崩溃时可能丢失数据。

-- 在使用 WAL 模式时，NORMAL 是一个安全且性能良好的默认设置。
PRAGMA synchronous = NORMAL;

PRAGMA cache_size

设置内存中页缓存的大小。默认值为 2000 页。更大的缓存可以提高大型数据库上读密集型工作负载的性能，但会增加内存使用。负值 -N 表示目标缓存大小为 N kibibytes（千字节）。

-- 将缓存大小设置为 64 MiB (65536 KiB)。
PRAGMA cache_size = -65536;

数据库检查与调试

PRAGMA database_list：列出所有附加的数据库，包括主数据库和任何临时数据库。

PRAGMA foreign_key_check：检查数据库是否存在任何外键违规。每找到一个违规，返回一行。非常适合验证数据完整性。

PRAGMA integrity_check：对整个数据库进行全面的损坏检查。在大型数据库上可能较慢。

PRAGMA table_info(table_name)：为指定表中的每个列返回一行，详细说明其名称、类型和约束。

PRAGMA index_list(table_name)：列出与表相关联的所有索引。

实用示例：推荐的设置

对于新应用程序，一个好的起点是在打开数据库连接后立即执行以下 PRAGMA 命令：

-- 1. 设置预写日志（Write-Ahead Logging），以获得更好的并发性和性能。
PRAGMA journal_mode = WAL;

-- 2. 将同步模式设置为 NORMAL，以在安全性和速度之间取得良好平衡。
PRAGMA synchronous = NORMAL;

-- 3. 强制执行外键约束，以确保数据完整性。
PRAGMA foreign_keys = ON;

-- 4. 设置忙碌超时，以优雅地处理并发写入尝试。
PRAGMA busy_timeout = 5000; -- 等待 5000 毫秒（5 秒）