SQLite PRAGMA 命令详解：优化数据库性能（六）

admin
数据库
8天前
7热度
0评论

SQLite 是一款轻量级的关系型数据库管理系统，广泛应用于嵌入式设备、移动应用和桌面应用程序中。SQLite 的 PRAGMA 命令是一组强大的工具，可以帮助开发者管理和优化数据库性能。本文将详细介绍 SQLite 中常用的 PRAGMA 命令，帮助你更好地理解和使用这些命令。

什么是 PRAGMA 命令？

PRAGMA 命令是 SQLite 提供的一种特殊命令，用于控制数据库的各种环境变量和状态标志。通过 PRAGMA 命令，你可以查询和设置数据库的配置参数，从而优化数据库性能和行为。

语法

要查询当前的 PRAGMA 值，只需提供该 PRAGMA 的名称：

PRAGMA pragma_name;

要设置新的 PRAGMA 值，语法如下：

PRAGMA pragma_name = value;

常用的 PRAGMA 命令

1. auto_vacuum

auto_vacuum PRAGMA 用于获取或设置自动清理模式。自动清理模式决定了数据库文件在删除数据后的行为。语法如下：

PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;

mode 可以是以下值之一：

0 或 NONE：禁用自动清理。这是默认模式，意味着数据库文件大小不会缩小，除非手动使用 VACUUM 命令。
1 或 FULL：启用自动清理，是全自动的。在这种模式下，数据库文件会随着数据的删除而缩小。
2 或 INCREMENTAL：启用自动清理，但需要手动激活。在这种模式下，删除的数据会被标记为自由页，可以通过 incremental_vacuum PRAGMA 进行覆盖。

2. cache_size

cache_size PRAGMA 用于获取或设置内存中页面缓存的最大大小。这可以显著提高数据库的读写性能。语法如下：

PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;

pages 值表示缓存中的页面数。默认情况下，缓存大小为 2,000 页，最小尺寸为 10 页。

3. case_sensitive_like

case_sensitive_like PRAGMA 用于控制内置的 LIKE 表达式的大小写敏感性。默认情况下，LIKE 操作符不区分大小写。语法如下：

PRAGMA case_sensitive_like = [true|false];

目前无法查询该 PRAGMA 的当前状态。

4. count_changes

count_changes PRAGMA 用于获取或设置数据操作语句（如 INSERT、UPDATE 和 DELETE）的返回值。默认情况下，这些语句不返回任何内容。语法如下：

PRAGMA count_changes;
PRAGMA count_changes = [true|false];

如果设置为 true，每个语句将返回一个单行单列的表，表示受影响的行数。

5. database_list

database_list PRAGMA 用于列出所有已连接的数据库。语法如下：

PRAGMA database_list;

该 PRAGMA 返回一个包含三个列的表格，分别是数据库的序列号、名称和文件路径。

6. encoding

encoding PRAGMA 用于控制字符串在数据库文件中的编码方式。语法如下：

PRAGMA encoding;
PRAGMA encoding = format;

format 可以是 UTF-8、UTF-16le 或 UTF-16be 之一。

7. freelist_count

freelist_count PRAGMA 用于返回当前被标记为自由页的数据库页数。语法如下：

PRAGMA [database.]freelist_count;

8. index_info

index_info PRAGMA 用于返回关于数据库索引的信息。语法如下：

PRAGMA [database.]index_info(index_name);

结果集将为每个索引列显示一行，包含索引列的序号、表名和列名。

9. index_list

index_list PRAGMA 用于列出与表关联的所有索引。语法如下：

PRAGMA [database.]index_list(table_name);

结果集将为每个索引显示一行，包含索引名和是否唯一的标识。

10. journal_mode

journal_mode PRAGMA 用于获取或设置日志文件的存储和处理模式。语法如下：

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

支持的模式包括：

DELETE：默认模式。事务结束后，日志文件将被删除。
TRUNCATE：日志文件被截断为零字节长度。
PERSIST：日志文件被保留，但头部被重写，表明日志不再有效。
MEMORY：日志记录保留在内存中，而不是磁盘上。
OFF：不保留任何日志记录。

11. max_page_count

max_page_count PRAGMA 用于获取或设置数据库允许的最大页数。语法如下：

PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;

默认值为 1,073,741,823，即大约 1 GB 的页面。

12. page_count

page_count PRAGMA 用于返回当前数据库中的页数。语法如下：

PRAGMA [database.]page_count;

数据库文件的大小应该是 page_count * page_size。

13. page_size

page_size PRAGMA 用于获取或设置数据库页面的大小。语法如下：


PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;

默认情况下，允许的页面大小是 512、1024、2048、4096、8192、16384、32768 字节。更改现有数据库的页面大小需要先设置页面大小，然后立即执行 VACUUM 命令。

14. parser_trace

parser_trace PRAGMA 用于控制 SQL 解析器的调试输出。语法如下：

PRAGMA parser_trace = [true|false];

默认情况下，调试输出是关闭的。设置为 true 时，SQL 解析器会在解析 SQL 命令时打印其状态。

15. recursive_triggers

recursive_triggers PRAGMA 用于获取或设置递归触发器功能。如果未启用递归触发器，一个触发动作将不会触发另一个触发。语法如下：

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];

16. schema_version

schema_version PRAGMA 用于获取或设置存储在数据库头中的架构版本值。语法如下：

PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;

这是一个 32 位有符号整数值，用于跟踪架构的变化。每当执行架构改变命令（如 CREATE 或 DROP）时，该值会递增。

17. secure_delete

secure_delete PRAGMA 用于控制数据从数据库中删除的方式。语法如下：

PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];

默认情况下，安全删除标志是关闭的，但可以通过 SQLITE_SECURE_DELETE 构建选项来改变。

18. sql_trace

sql_trace PRAGMA 用于将 SQL 跟踪结果转储到屏幕上。语法如下：

PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];

SQLite 必须通过 SQLITE_DEBUG 指令编译才能使用该 PRAGMA。

19. synchronous

synchronous PRAGMA 用于获取或设置当前磁盘的同步模式，控制 SQLite 如何将数据写入物理存储。语法如下：

PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;

支持的同步模式包括：

0 或 OFF：不进行同步。
1 或 NORMAL：在关键的磁盘操作后同步。
2 或 FULL：在每个关键的磁盘操作后同步。

20. temp_store

temp_store PRAGMA 用于获取或设置临时数据库文件的存储模式。语法如下：

PRAGMA temp_store;
PRAGMA temp_store = mode;

支持的存储模式包括：

0 或 DEFAULT：默认使用编译时的模式，通常是 FILE。
1 或 FILE：使用基于文件的存储。
2 或 MEMORY：使用基于内存的存储。

21. temp_store_directory

temp_store_directory PRAGMA 用于获取或设置临时数据库文件的位置。语法如下：

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

22. user_version

user_version PRAGMA 用于获取或设置存储在数据库头中的用户自定义版本值。语法如下：

PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;

这是一个 32 位有符号整数值，可以由开发人员设置，用于版本跟踪的目的。

23. writable_schema

writable_schema PRAGMA 用于获取或设置是否能够修改系统表。语法如下：

PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];

如果设置为 true，则可以创建和修改以 sqlite_ 开始的表，包括 sqlite_master 表。使用该 PRAGMA 时需谨慎，因为它可能导致数据库损坏。

约束

约束是在表的数据列上强制执行的规则，用于确保数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束应用于整个表。

1. NOT NULL 约束

NOT NULL 约束确保某列不能有 NULL 值。默认情况下，列可以保存 NULL 值。如果你不希望某列有 NULL 值，可以在该列上定义此约束。

实例

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

2. DEFAULT 约束

DEFAULT 约束在 INSERT INTO 语句没有提供特定值时，为列提供一个默认值。

实例

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),

   SALARY         REAL    DEFAULT 50000.00
);

3. UNIQUE 约束

UNIQUE 约束确保某列中的所有值都是不同的。这可以防止在一个特定的列中存在重复的记录。

实例

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

4. PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每个记录。一个表只能有一个主键，但它可以由一个或多个字段组成。主键必须包含唯一值，且不能有 NULL 值。

实例

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

5. CHECK 约束

CHECK 约束用于确保输入记录的值满足特定条件。如果条件值为 false，则记录违反了约束，不能插入到表中。

实例

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

删除约束

在 SQLite 中，要删除表的约束，通常需要使用 ALTER TABLE 语句，并指定要删除的约束类型。

删除主键约束

ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;

删除唯一约束

ALTER TABLE table_name
DROP CONSTRAINT unique_constraint_name;

删除检查约束

ALTER TABLE table_name
DROP CONSTRAINT check_constraint_name;

总结

通过本文的介绍，你应该对 SQLite 的 PRAGMA 命令有了全面的了解。这些命令不仅有助于优化数据库性能，还能帮助你更好地管理和维护数据库。合理使用 PRAGMA 命令，可以显著提升你的数据库应用的稳定性和效率。希望本文对你有所帮助！