Sqlite 简明教程

SQLite - PRAGMA

SQLite PRAGMA 命令是一个特殊命令，用于控制 SQLite 环境中的各种环境变量和状态标志。可以读取 PRAGMA 值，也可以根据需要设置它。

SQLite PRAGMA command is a special command to be used to control various environmental variables and state flags within the SQLite environment. A PRAGMA value can be read and it can also be set based on the requirements.

Syntax

要查询当前的 PRAGMA 值，只需提供 pragma 的名称。

To query the current PRAGMA value, just provide the name of the pragma.

PRAGMA pragma_name;

要为 PRAGMA 设置新值，请使用以下语法。

To set a new value for PRAGMA, use the following syntax.

PRAGMA pragma_name = value;

设置模式可以是名称或整数等效项，但返回的值始终是一个整数。

The set mode can be either the name or the integer equivalent but the returned value will always be an integer.

auto_vacuum Pragma

auto_vacuum pragma 会获取或设置自动真空模式。下面是简单的语法。

The auto_vacuum pragma gets or sets the auto-vacuum mode. Following is the simple syntax.

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

其中以下各项可以是 mode −

Where mode can be any of the following −

Sr.No.

Pragma Value & Description

1

0 or NONE Auto-vacuum is disabled. This is the default mode which means that a database file will never shrink in size unless it is manually vacuumed using the VACUUM command.

2

1 or FULL Auto-vacuum is enabled and fully automatic which allows a database file to shrink as data is removed from the database.

3

2 or INCREMENTAL Auto-vacuum is enabled but must be manually activated. In this mode the reference data is maintained, but free pages are simply put on the free list. These pages can be recovered using the incremental_vacuum pragma any time.

cache_size Pragma

cache_size pragma 可以获取或临时设置内存中页面缓存的最大大小。下面是简单的语法。

The cache_size pragma can get or temporarily set the maximum size of the in-memory page cache. Following is the simple syntax.

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

pages 值表示缓存中的页面数。内置页面缓存的默认大小为 2000 页，最小大小为 10 页。

The pages value represents the number of pages in the cache. The built-in page cache has a default size of 2,000 pages and a minimum size of 10 pages.

case_sensitive_like Pragma

case_sensitive_like pragma 控制内建 LIKE 表达式的区分大小写。默认情况下，此 pragma 为假，这意味着内建 LIKE 运算子忽略字母大小写。下面是简单的语法。

The case_sensitive_like pragma controls the case-sensitivity of the built-in LIKE expression. By default, this pragma is false which means that the built-in LIKE operator ignores the letter case. Following is the simple syntax.

PRAGMA case_sensitive_like = [true|false];

无法查询此 pragma 的当前状态。

There is no way to query for the current state of this pragma.

count_changes Pragma

count_changes pragma 获取或设置数据操作语句（如 INSERT、UPDATE 和 DELETE）的返回值。下面是简单的语法。

count_changes pragma gets or sets the return value of data manipulation statements such as INSERT, UPDATE and DELETE. Following is the simple syntax.

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

默认情况下，此 pragma 为假，并且这些语句不会返回任何内容。如果设置为 true，每个提到的语句将返回一个单列单行表，其中包含一个单个整数值，指示受操作影响的行。

By default, this pragma is false and these statements do not return anything. If set to true, each of the mentioned statement will return a one-column, one-row table consisting of a single integer value indicating impacted rows by the operation.

database_list Pragma

database_list pragma 将用于列出所有附加的数据库。下面是简单的语法。

The database_list pragma will be used to list down all the databases attached. Following is the simple syntax.

PRAGMA database_list;

此 pragma 将返回一个三列表，每打开或附加一个数据库都会有一行，分别给出数据库的序号、名称和关联的文件。

This pragma will return a three-column table with one row per open or attached database giving database sequence number, its name and the file associated.

encoding Pragma

encoding pragma 控制如何在数据库文件中对字符串进行编码和存储。下面是简单的语法。

The encoding pragma controls how strings are encoded and stored in a database file. Following is the simple syntax.

PRAGMA encoding;
PRAGMA encoding = format;

格式值可以是 UTF-8, UTF-16le 或 UTF-16be 之一。

The format value can be one of UTF-8, UTF-16le, or UTF-16be.

freelist_count Pragma

freelist_count pragma 返回单个整数，指示当前标记为已释放且可用的数据库页面数。下面是简单的语法。

The freelist_count pragma returns a single integer indicating how many database pages are currently marked as free and available. Following is the simple syntax.

PRAGMA [database.]freelist_count;

格式值可以是 UTF-8, UTF-16le 或 UTF-16be 之一。

The format value can be one of UTF-8, UTF-16le, or UTF-16be.

index_info Pragma

index_info pragma 返回有关数据库索引的信息。下面是简单的语法。

The index_info pragma returns information about a database index. Following is the simple syntax.

PRAGMA [database.]index_info( index_name );

结果集将为索引中包含的每一列包含一列行，提供列顺序、表中列索引和列名称。

The result set will contain one row for each column contained in the index giving column sequence, column index with-in table and column name.

index_list Pragma

index_list pragma 列出与表相关的所有索引。以下是最简单的语法。

index_list pragma lists all of the indexes associated with a table. Following is the simple syntax.

PRAGMA [database.]index_list( table_name );

结果集将为每个索引包含一列行，提供索引顺序、索引名称以及指示该索引是否唯一的标志。

The result set will contain one row for each index giving index sequence, index name and flag indicating whether the index is unique or not.

journal_mode Pragma

journal_mode pragma 获取或设置日志模式，该模式控制日志文件如何被存储和处理。以下是最简单的语法。

The journal_mode pragma gets or sets the journal mode which controls how the journal file is stored and processed. Following is the simple syntax.

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

下表列出了五种受支持的日志模式。

There are five supported journal modes as listed in the following table.

Sr.No.

Pragma Value & Description

1

DELETE This is the default mode. Here at the conclusion of a transaction, the journal file is deleted.

2

TRUNCATE The journal file is truncated to a length of zero bytes.

3

PERSIST The journal file is left in place, but the header is overwritten to indicate the journal is no longer valid.

4

MEMORY The journal record is held in memory, rather than on disk.

5

OFF No journal record is kept.

max_page_count Pragma

max_page_count pragma 获取或设置数据库允许的最大页面数。以下是最简单的语法。

The max_page_count pragma gets or sets the maximum allowed page count for a database. Following is the simple syntax.

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

默认值为 1,073,741,823，即一兆页面，这意味着如果默认页面大小为 1 KB，这允许数据库增长至一太字节。

The default value is 1,073,741,823 which is one giga-page, which means if the default 1 KB page size, this allows databases to grow up to one terabyte.

page_count Pragma

page_count pragma 返回数据库中的当前页面数。以下是最简单的语法 −

The page_count pragma returns in the current number of pages in the database. Following is the simple syntax −

PRAGMA [database.]page_count;

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

The size of the database file should be page_count * page_size.

page_size Pragma

page_size pragma 获取或设置数据库页面的大小。以下是最简单的语法。

The page_size pragma gets or sets the size of the database pages. Following is the simple syntax.

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

默认情况下，允许的大小为 512、1024、2048、4096、8192、16384 和 32768 字节。更改现有数据库的页面大小的唯一方法是设置页面大小，然后立即对数据库执行 VACUUM 操作。

By default, the allowed sizes are 512, 1024, 2048, 4096, 8192, 16384, and 32768 bytes. The only way to alter the page size on an existing database is to set the page size and then immediately VACUUM the database.

parser_trace Pragma

parser_trace pragma 控制在解析 SQL 命令时打印调试状态。以下是最简单的语法。

The parser_trace pragma controls printing the debugging state as it parses SQL commands. Following is the simple syntax.

PRAGMA parser_trace = [true|false];

默认情况下，设置为 false，但如果启用并将其设置为 true，则 SQL 解析器将在解析 SQL 命令时打印其状态。

By default, it is set to false but when enabled by setting it to true, the SQL parser will print its state as it parses SQL commands.

recursive_triggers Pragma

recursive_triggers pragma 获取或设置递归触发器功能。如果未启用递归触发器，触发器操作将不会触发另一个触发器。以下是最简单的语法。

The recursive_triggers pragma gets or sets the recursive trigger functionality. If recursive triggers are not enabled, a trigger action will not fire another trigger. Following is the simple syntax.

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

schema_version Pragma

schema_version 编译指令得到或设定存储在数据库头文件中的模式版本值。以下为简易句法。

The schema_version pragma gets or sets the schema version value that is stored in the database header. Following is the simple syntax.

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

这是一个 32 位带符号整数，它负责追踪模式更改。每当执行模式更改指令（例如，CREATE…​ 或 DROP…​）时，此值会被递增。

This is a 32-bit signed integer value that keeps track of schema changes. Whenever a schema-altering command is executed (like, CREATE…​ or DROP…​), this value is incremented.

secure_delete Pragma

secure_delete 编译指令用于控制如何删除数据库的内容。以下为简易句法。

The secure_delete pragma is used to control how the content is deleted from the database. Following is the simple syntax.

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

安全删除标志的默认值为关闭，但可以使用 SQLITE_SECURE_DELETE 构建选项进行更改。

The default value for the secure delete flag is normally off, but this can be changed with the SQLITE_SECURE_DELETE build option.

sql_trace Pragma

sql_trace 编译指令用于将 SQL 跟踪结果转储到屏幕。以下为简易句法。

The sql_trace pragma is used to dump SQL trace results to the screen. Following is the simple syntax.

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

SQLite 必须采用 SQLITE_DEBUG 指令进行编译才能包含此编译指令。

SQLite must be compiled with the SQLITE_DEBUG directive for this pragma to be included.

synchronous Pragma

synchronous 编译指令得到或设定当前磁盘同步模式，它控制了 SQLite 将数据写出到物理存储的积极程度。以下为简易句法。

The synchronous pragma gets or sets the current disk synchronization mode, which controls how aggressively SQLite will write data all the way out to physical storage. Following is the simple syntax.

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

SQLite 支持表中列出的以下同步模式。

SQLite supports the following synchronization modes as listed in the table.

Sr.No.

Pragma Value & Description

1

0 or OFF No syncs at all

2

1 or NORMAL Sync after each sequence of critical disk operations

3

2 or FULL Sync after each critical disk operation

temp_store Pragma

temp_store 编译指令得到或设定临时数据库文件所使用的存储模式。以下为简易句法。

The temp_store pragma gets or sets the storage mode used by temporary database files. Following is the simple syntax.

PRAGMA temp_store;
PRAGMA temp_store = mode;

SQLite 支持以下存储模式。

SQLite supports the following storage modes.

Sr.No.

Pragma Value & Description

1

0 or DEFAULT Use compile-time default. Normally FILE.

2

1 or FILE Use file-based storage.

3

2 or MEMORY Use memory-based storage.

temp_store_directory Pragma

temp_store_directory 编译指令得到或设定临时数据库文件使用的位置。以下为简易句法。

The temp_store_directory pragma gets or sets the location used for temporary database files. Following is the simple syntax.

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';

user_version Pragma

user_version 编译指令得到或设定存储在数据库头文件中的用户自定义版本值。以下为简易句法。

The user_version pragma gets or sets the user-defined version value that is stored in the database header. Following is the simple syntax.

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

这是一个 32 位带符号整数，可由开发者设定以用于版本追踪。

This is a 32-bit signed integer value, which can be set by the developer for version tracking purpose.

writable_schema Pragma

writable_schema 编译指示可以获取或设置修改系统表的能力。以下就是简单的语法。

The writable_schema pragma gets or sets the ability to modify system tables. Following is the simple syntax.

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

如果设置了此编译指示，那么以 sqlite_ 开头的表就可以被创建和修改，包括 sqlite_master 表。使用编译指示时请务必小心，因为它可能导致数据库完全损坏。

If this pragma is set, tables that start with sqlite_ can be created and modified, including the sqlite_master table. Be careful while using pragma because it can lead to complete database corruption.