Small. 小. Fast. 快速. Reliable. 可靠.
Choose any three. 选择任意三个.

PRAGMA Statements PRAGMA声明

The PRAGMA statement is an SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. PRAGMA语句是特定于SQLite的SQL扩展,用于修改SQLite库的操作或查询SQLite库的内部(非表)数据. The PRAGMA statement is issued using the same interface as other SQLite commands (eg SELECT , INSERT ) but is different in the following important respects: PRAGMA语句是使用与其他SQLite命令(例如SELECTINSERT )相同的接口发出的,但在以下重要方面有所不同:

The C-language API for SQLite provides the SQLITE_FCNTL_PRAGMA file control which gives VFS implementations the opportunity to add new PRAGMA statements or to override the meaning of built-in PRAGMA statements. 用于SQLite的C语言API提供了SQLITE_FCNTL_PRAGMA 文件控件 ,该控件使VFS实现可以添加新的PRAGMA语句或覆盖内置PRAGMA语句的含义.


PRAGMA command syntax PRAGMA命令语法

pragma-stmt: pragma-stmt:

语法图pragma-stmt

pragma-value: 语用价值:

语法图pragma-value

signed-number: 有符号数:

A pragma can take either zero or one argument. 编译指示可以采用零或一个参数. The argument is may be either in parentheses or it may be separated from the pragma name by an equal sign. 参数可以在括号中,也可以与编译指示名称用等号分隔. The two syntaxes yield identical results. 这两种语法产生相同的结果. In many pragmas, the argument is a boolean. 在许多编译指示中,参数是布尔值. The boolean can be one of: 布尔值可以是以下之一:

1 yes true on 1是对
0 no false off 0没有假关闭

Keyword arguments can optionally appear in quotes. 关键字参数可以选择用引号引起来. (Example: 'yes' [FALSE] .) Some pragmas takes a string literal as their argument. (示例: "是" [FALSE] .)某些编译指示将字符串文字作为其自变量. When pragma takes a keyword argument, it will usually also take a numeric equivalent as well. 当编译指示采用关键字参数时,通常也将采用数值等效项. For example, "0" and "no" mean the same thing, as does "1" and "yes". 例如," 0"和"否"与" 1"和"是"含义相同. When querying the value of a setting, many pragmas return the number rather than the keyword. 查询设置的值时,许多编译指示会返回数字而不是关键字.

A pragma may have an optional schema-name before the pragma name. 编译指示可以在编译指示名称之前具有可选的架构名称. The schema-name is the name of an ATTACH -ed database or "main" or "temp" for the main and the TEMP databases. schema-nameATTACH编辑的数据库的名称,或者是main和TEMP数据库的" main"或" temp". If the optional schema name is omitted, "main" is assumed. 如果省略了可选的架构名称,则假定为" main". In some pragmas, the schema name is meaningless and is simply ignored. 在某些语用上,模式名称是没有意义的,只是被忽略. In the documentation below, pragmas for which the schema name is meaningful are shown with a " schema. " prefix. 在下面的文档中,模式名称有意义的编译指示以" schema. "前缀显示.


PRAGMA functions PRAGMA功能

PRAGMAs that return results and that have no side-effects can be accessed from ordinary SELECT statements as table-valued functions . 可以从普通SELECT语句作为表值函数访问返回结果且没有副作用的PRAGMA. For each participating PRAGMA, the corresponding table-valued function has the same name as the PRAGMA with a 7-character "pragma_" prefix. 对于每个参与的PRAGMA,对应的表值函数的名称与PRAGMA的名称相同,前缀为7个字符. The PRAGMA argument and schema, if any, are passed as arguments to the table-valued function. PRAGMA参数和架构(如果有)作为参数传递给表值函数.

For example, information about the columns in an index can be read using the index_info pragma as follows: 例如,可以使用index_info杂注读取有关索引中列的信息,如下所示:

 PRAGMA index_info('idx52'); PRAGMA index_info('idx52');

Or, the same content can be read using: 或者,可以使用以下命令读取相同的内容:

 SELECT * FROM pragma_index_info('idx52'); 选择* FROM pragma_index_info('idx52');

The advantage of the table-valued function format is that the query can return just a subset of the PRAGMA columns, can include a WHERE clause, can use aggregate functions, and the table-valued function can be just one of several data sources in a join. 表值函数格式的优点是查询可以只返回PRAGMA列的子集,可以包含WHERE子句,可以使用聚合函数,而表值函数可以只是表中多个数据源之一.加入. For example, to get a list of all indexed columns in a schema, one could query: 例如,要获取模式中所有索引列的列表,可以查询:

 SELECT DISTINCT m.name || SELECT DISTINCT m.name || '.' '. || || ii.name AS 'indexed-columns' ii.AS为"索引列"
  FROM sqlite_master AS m, 从sqlite_master AS m,
       pragma_index_list(m.name) AS il, pragma_index_list(m.name)如il,
       pragma_index_info(il.name) AS ii pragma_index_info(il.name)AS ii
 WHERE m.type='table' 在哪里m.type ='table'
 ORDER BY 1; ORDER BY 1;

Additional notes: 补充笔记:


List Of PRAGMAs PRAGMA列表

Notes: 笔记:

  1. Pragmas whose names are struck through are deprecated. 编译指示他们的名字通过已被弃用的袭击 . Do not use them. 不要使用它们. They exist for historical compatibility. 存在它们是为了历史兼容性.
  2. These pragmas are only available in builds using non-standard compile-time options. 这些编译指示仅在使用非标准编译时选项的构建中可用.
  3. These pragmas are used for testing SQLite and are not recommended for use in application programs. 这些编译指示用于测试SQLite,不建议在应用程序中使用.

PRAGMA analysis_limit PRAGMA analysis_limit

PRAGMA analysis_limit; PRAGMA analysis_limit;
PRAGMA analysis_limit = N ; PRAGMA analysis_limit = N ;

Query or change a limit on the approximate ANALYZE setting. 查询或更改近似ANALYZE设置的限制. This is approximate number of rows examined in each index by the ANALYZE command. 这是ANALYZE命令在每个索引中检查的大约行数. If the argument N is omitted, then the analysis limit is unchanged. 如果省略参数N ,则分析极限不变. If the limit is zero, then the analysis limit is disabled and the ANALYZE command will examine all rows of each index. 如果限制为零,则禁用分析限制,并且ANALYZE命令将检查每个索引的所有行. If N is greater than zero, then the analysis limit is set to N and subsequent ANALYZE commands will breaks off processing each index after it has examined approximately N rows. 如果N大于零,则将分析限制设置为N,并且随后的ANALYZE命令将在检查了大约N行后中断对每个索引的处理. If N is a negative number or something other than an integer value, then the pragma behaves as if the N argument was omitted. 如果N为负数或除整数以外的其他值,则杂注的行为就像忽略了N参数一样. In all cases, the value returned is the new analysis limit used for subsequent ANALYZE commands. 在所有情况下,返回的值都是用于后续ANALYZE命令的新分析限制.

This pragma can be used to help the ANALYZE command run faster on large databases. 此实用程序可用于帮助ANALYZE命令在大型数据库上更快地运行. The results of analysis are not as good when only part of each index is examined, but the results are usually good enough. 当仅检查每个索引的一部分时,分析结果并不理想,但结果通常足够好. Setting N to 100 or 1000 allows the ANALYZE command to run very quickly, even on multi-gigabyte database files. 将N设置为100或1000可以使ANALYZE命令非常快速地运行,即使在数GB的数据库文件上也是如此. This pragma is particularly useful in combination with PRAGMA optimize . 此实用程序与PRAGMA优化组合特别有用.

This pragma was added in SQLite version 3.32.0 (2020-05-22). 此实用程序已添加到SQLite版本3.32.0(2020-05-22)中. The current implementation only uses the lower 31 bits of the N value - higher order bits are silently ignored. 当前实现仅使用N值的低31位-静默忽略高位. Future versions of SQLite might begin using higher order bits. 未来版本的SQLite可能会开始使用更高阶的位. PRAGMA application_id PRAGMA application_id


PRAGMA schema. PRAGMA 模式. application_id; application_id;
PRAGMA schema. PRAGMA 模式. application_id = integer ; application_id = 整数 ;

The application_id PRAGMA is used to query or set the 32-bit signed big-endian "Application ID" integer located at offset 68 into the database header . application_id PRAGMA用于查询或将位于偏移量68的32位带符号大端"应用程序ID"整数设置到数据库头中 . Applications that use SQLite as their application file-format should set the Application ID integer to a unique integer so that utilities such as file(1) can determine the specific file type rather than just reporting "SQLite3 Database". 使用SQLite作为其应用程序文件格式的应用程序应将Application ID整数设置为唯一的整数,以便诸如file(1)之类的实用程序可以确定特定的文件类型,而不仅仅是报告" SQLite3数据库". A list of assigned application IDs can be seen by consulting the magic.txt file in the SQLite source repository. 通过查询SQLite源存储库中的magic.txt文件,可以看到已分配的应用程序ID的列表.

See also the user_version pragma . 另请参阅user_version编译指示 . PRAGMA auto_vacuum PRAGMA auto_vacuum


PRAGMA schema. PRAGMA 模式. auto_vacuum; auto_vacuum;
PRAGMA schema. PRAGMA 模式. auto_vacuum = 0 | auto_vacuum = 0 | NONE | 无| 1 | 1 | FULL | 满| 2 | 2 | INCREMENTAL ; 增量 ;

Query or set the auto-vacuum status in the database. 查询或设置数据库中的自动真空状态.

The default setting for auto-vacuum is 0 or "none", unless the SQLITE_DEFAULT_AUTOVACUUM compile-time option is used. 除非使用SQLITE_DEFAULT_AUTOVACUUM编译时选项,否则自动真空的默认设置为0或"无". The "none" setting means that auto-vacuum is disabled. "无"设置表示禁用自动真空. When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. 禁用自动真空并且从数据库中删除数据后,数据库文件将保持相同大小. Unused database file pages are added to a " freelist " and reused for subsequent inserts. 未使用的数据库文件页面被添加到" freelist "中,并用于后续插入. So no database file space is lost. 因此,不会丢失数据库文件空间. However, the database file does not shrink. 但是,数据库文件不会收缩. In this mode the VACUUM command can be used to rebuild the entire database file and thus reclaim unused disk space. 在这种模式下, VACUUM命令可用于重建整个数据库文件,从而回收未使用的磁盘空间.

When the auto-vacuum mode is 1 or "full", the freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages at every transaction commit. 当自动真空模式为1或"完全"时,将空闲列表页面移至数据库文件的末尾,并且在每次事务提交时,数据库文件将被截断以删除空闲列表页面. Note, however, that auto-vacuum only truncates the freelist pages from the file. 但是请注意,自动清空只会截断文件中的空闲列表页面. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. 自动真空不会像VACUUM命令那样对数据库进行碎片整理或重新打包单个数据库页面. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. 实际上,由于自动抽真空会在文件内移动页面,因此实际上会使碎片变得更糟.

Auto-vacuuming is only possible if the database stores some additional information that allows each database page to be traced backwards to its referrer. 只有在数据库存储一些附加信息时,才可以进行自动抽真空,这些附加信息允许将每个数据库页面追溯到其引用程序. Therefore, auto-vacuuming must be turned on before any tables are created. 因此,在创建任何表之前必须打开自动真空. It is not possible to enable or disable auto-vacuum after a table has been created. 创建表后,无法启用或禁用自动真空.

When the value of auto-vacuum is 2 or "incremental" then the additional information needed to do auto-vacuuming is stored in the database file but auto-vacuuming does not occur automatically at each commit as it does with auto_vacuum=full. 当auto-vacuum的值为2或" incremental"时,执行自动真空处理所需的其他信息存储在数据库文件中,但是每次auto-vacuum = full时,自动提交不会在每次提交时自动发生. In incremental mode, the separate incremental_vacuum pragma must be invoked to cause the auto-vacuum to occur. 在增量模式下,必须调用单独的增量变量_pragma才能引起自动真空发生.

The database connection can be changed between full and incremental autovacuum mode at any time. 可以随时在完全自动和增量自动真空模式之间更改数据库连接. However, changing from "none" to "full" or "incremental" can only occur when the database is new (no tables have yet been created) or by running the VACUUM command. 但是,只有在数据库是新建的(尚未创建表)时,或者通过运行VACUUM命令,才可能从"无"更改为"完整"或"增量". To change auto-vacuum modes, first use the auto_vacuum pragma to set the new desired mode, then invoke the VACUUM command to reorganize the entire database file. 要更改自动真空模式,请首先使用auto_vacuum编译指示来设置新的所需模式,然后调用VACUUM命令来重组整个数据库文件. To change from "full" or "incremental" back to "none" always requires running VACUUM even on an empty database. 要从"完整"或"增量"变回"无",即使在空数据库上也始终需要运行VACUUM .

When the auto_vacuum pragma is invoked with no arguments, it returns the current auto_vacuum mode. 当不带任何参数调用auto_vacuum编译指示时,它将返回当前的auto_vacuum模式.

PRAGMA automatic_index PRAGMA automatic_index

PRAGMA automatic_index; PRAGMA automatic_index;
PRAGMA automatic_index = boolean ; PRAGMA automatic_index = 布尔值 ;

Query, set, or clear the automatic indexing capability. 查询,设置或清除自动索引功能.

Automatic indexing is enabled by default as of version 3.7.17 (2013-05-20), but this might change in future releases of SQLite. 默认情况下,自3.7.17版 (2013-05-20)起启用自动索引 ,但是在将来的SQLite版本中可能会更改. PRAGMA busy_timeout PRAGMA busy_timeout


PRAGMA busy_timeout; PRAGMA busy_timeout;
PRAGMA busy_timeout = milliseconds ; PRAGMA busy_timeout = 毫秒 ;

Query or change the setting of the busy timeout . 查询或更改繁忙超时的设置. This pragma is an alternative to the sqlite3_busy_timeout() C-language interface which is made available as a pragma for use with language bindings that do not provide direct access to sqlite3_busy_timeout() . 该实用程序是sqlite3_busy_timeout() C语言接口的替代方法,该接口可作为实用程序用于不直接访问sqlite3_busy_timeout()的语言绑定.

Each database connection can only have a single busy handler . 每个数据库连接只能有一个繁忙的处理程序 . This PRAGMA sets the busy handler for the process, possibly overwriting any previously set busy handler. 此PRAGMA设置进程的繁忙处理程序,可能会覆盖任何先前设置的繁忙处理程序. PRAGMA cache_size PRAGMA cache_size


PRAGMA schema. PRAGMA 模式. cache_size; cache_size;
PRAGMA schema. PRAGMA 模式. cache_size = pages ; cache_size = 页面 ;
PRAGMA schema. PRAGMA 模式. cache_size = - kibibytes ; cache_size =- 千字节 ;

Query or change the suggested maximum number of database disk pages that SQLite will hold in memory at once per open database file. 查询或更改每个打开的数据库文件一次SQLite将在内存中保留的数据库磁盘页的建议最大数目. Whether or not this suggestion is honored is at the discretion of the Application Defined Page Cache . 是否接受此建议由应用程序定义的页面缓存决定 . The default page cache that is built into SQLite honors the request, however alternative application-defined page cache implementations may choose to interpret the suggested cache size in different ways or to ignore it all together. 内置在SQLite中的默认页面缓存会接受该请求,但是其他应用程序定义的页面缓存实现可能选择以不同的方式解释建议的缓存大小,或者将其全部忽略. The default suggested cache size is -2000, which means the cache size is limited to 2048000 bytes of memory. 建议的默认高速缓存大小为-2000,这意味着高速缓存大小限制为2048000字节的内存. The default suggested cache size can be altered using the SQLITE_DEFAULT_CACHE_SIZE compile-time options. 可以使用SQLITE_DEFAULT_CACHE_SIZE编译时选项来更改默认的建议缓存大小. The TEMP database has a default suggested cache size of 0 pages. TEMP数据库的默认建议缓存大小为0页.

If the argument N is positive then the suggested cache size is set to N. If the argument N is negative, then the number of cache pages is adjusted to be a number of pages that would use approximately abs(N*1024) bytes of memory based on the current page size. 如果参数N为正,则建议的缓存大小设置为N.如果参数N为负,则将缓存页数调整为将使用大约abs(N * 1024)字节内存的页数.根据当前页面大小. SQLite remembers the number of pages in the page cache, not the amount of memory used. SQLite会记住页面缓存中的页面数,而不是所使用的内存量. So if you set the cache size using a negative number and subsequently change the page size (using the PRAGMA page_size command) then the maximum amount of cache memory will go up or down in proportion to the change in page size. 因此,如果使用负数设置高速缓存大小,然后更改页面大小(使用PRAGMA page_size命令),则高速缓存的最大数量将与页面大小的变化成比例地增加或减少.

Backwards compatibility note: The behavior of cache_size with a negative N was different prior to version 3.7.10 (2012-01-16). 向后兼容性说明: 版本3.7.10 (2012-01-16)之前具有负N的cache_size的行为不同. In earlier versions, the number of pages in the cache was set to the absolute value of N. 在早期版本中,缓存中的页面数设置为N的绝对值.

When you change the cache size using the cache_size pragma, the change only endures for the current session. 当您使用cache_size编译指示更改缓存大小时,该更改仅在当前会话中有效. The cache size reverts to the default value when the database is closed and reopened. 关闭并重新打开数据库后,缓存大小将恢复为默认值.

The default page cache implemention does not allocate the full amount of cache memory all at once. 默认的页面缓存实现不会一次分配全部的缓存内存. Cache memory is allocated in smaller chunks on an as-needed basis. 高速缓存在需要时按较小的块分配. The page_cache setting is a (suggested) upper bound on the amount of memory that the cache can use, not the amount of memory it will use all of the time. page_cache设置是(建议的)上限,该上限是缓存可以使用的内存量,而不是它将一直使用的内存量. This is the behavior of the default page cache implementation, but an application defined page cache is free to behave differently if it wants. 这是默认页面缓存实现的行为,但是应用程序定义的页面缓存可以随意改变行为. PRAGMA cache_spill PRAGMA cache_spill


PRAGMA cache_spill; PRAGMA cache_spill;
PRAGMA cache_spill= boolean ; PRAGMA cache_spill = 布尔值 ;
PRAGMA schema. PRAGMA 模式. cache_spill= N ; cache_spill = N ;

The cache_spill pragma enables or disables the ability of the pager to spill dirty cache pages to the database file in the middle of a transaction. cache_spill编译指示启用或禁用分页器在事务中间将脏的缓存页面溢出到数据库文件的功能. Cache_spill is enabled by default and most applications should leave it that way as cache spilling is usually advantageous. 默认情况下,Cache_spill是启用的,并且大多数应用程序都应采用这种方式,因为缓存溢出通常是有利的. However, a cache spill has the side-effect of acquiring an EXCLUSIVE lock on the database file. 但是,缓存溢出具有获取数据库文件上的EXCLUSIVE锁的副作用. Hence, some applications that have large long-running transactions may want to disable cache spilling in order to prevent the application from acquiring an exclusive lock on the database until the moment that the transaction COMMIT s. 因此,某些具有大量长时间运行的事务的应用程序可能希望禁用缓存溢出,以防止该应用程序在事务COMMIT之前获取数据库的排他锁.

The "PRAGMA cache_spill= N " form of this pragma sets a minimum cache size threshold required for spilling to occur. 此编译指示的" PRAGMA cache_spill = N "形式设置发生溢出所需的最小高速缓存大小阈值. The number of pages in cache must exceed both the cache_spill threshold and the maximum cache size set by the PRAGMA cache_size statement in order for spilling to occur. 高速缓存中的页面数必须同时超过cache_spill阈值和PRAGMA cache_size语句设置的最大高速缓存大小,才能发生溢出.

The "PRAGMA cache_spill= boolean " form of this pragma applies across all databases attached to the database connection. 此编译指示的" PRAGMA cache_spill = boolean "形式适用于连接到数据库连接的所有数据库. But the "PRAGMA cache_spill= N " form of this statement only applies to the "main" schema or whatever other schema is specified as part of the statement. 但是此语句的" PRAGMA cache_spill = N "形式仅适用于"主"模式或该语句中指定的任何其他模式. PRAGMA case_sensitive_like PRAGMA case_sensitive_like


PRAGMA case_sensitive_like = boolean ; PRAGMA case_sensitive_like = 布尔值 ;

The default behavior of the LIKE operator is to ignore case for ASCII characters. LIKE运算符的默认行为是忽略ASCII字符的大小写. Hence, by default 'a' LIKE 'A' is true. 因此,默认情况下, " a"类似于" A"为true. The case_sensitive_like pragma installs a new application-defined LIKE function that is either case sensitive or insensitive depending on the value of the case_sensitive_like pragma. case_sensitive_like编译指示将安装一个新的应用程序定义的LIKE函数,该函数根据case_sensitive_like编译指示的值区分大小写或不区分大小写. When case_sensitive_like is disabled, the default LIKE behavior is expressed. 禁用case_sensitive_like时,将表示默认的LIKE行为. When case_sensitive_like is enabled, case becomes significant. 启用case_sensitive_like时,大小写变得重要. So, for example, 'a' LIKE 'A' is false but 'a' LIKE 'a' is still true. 因此,例如, " a"(如" A")为假,但" a"(如" a" )仍为真.

This pragma uses sqlite3_create_function() to overload the LIKE and GLOB functions, which may override previous implementations of LIKE and GLOB registered by the application. 此编译指示使用sqlite3_create_function()重载LIKE和GLOB函数,这可能会覆盖应用程序注册的LIKE和GLOB的先前实现. This pragma only changes the behavior of the SQL LIKE operator. 此编译指示仅更改SQL LIKE运算符的行为. It does not change the behavior of the sqlite3_strlike() C-language interface, which is always case insensitive. 它不会更改sqlite3_strlike() C语言接口的行为,该行为始终不区分大小写.

PRAGMA cell_size_check PRAGMA cell_size_check

PRAGMA cell_size_check PRAGMA cell_size_check
PRAGMA cell_size_check = boolean ; PRAGMA cell_size_check = 布尔值 ;

The cell_size_check pragma enables or disables additional sanity checking on database b-tree pages as they are initially read from disk. 当最初从磁盘读取数据库b树页面时,cell_size_check编译指示启用或禁用对数据库b树页面的其他完整性检查. With cell size checking enabled, database corruption is detected earlier and is less likely to "spread". 启用单元格大小检查后,可以更早地检测到数据库损坏,并且不太可能"扩散". However, there is a small performance hit for doing the extra checks and so cell size checking is turned off by default. 但是,进行额外的检查会降低性能,因此默认情况下将禁用单元格大小检查. PRAGMA checkpoint_fullfsync PRAGMA checkpoint_fullfsync


PRAGMA checkpoint_fullfsync PRAGMA checkpoint_fullfsync
PRAGMA checkpoint_fullfsync = boolean ; PRAGMA checkpoint_fullfsync = 布尔值 ;

Query or change the fullfsync flag for checkpoint operations. 查询或更改checkpoint操作的fullfsync标志. If this flag is set, then the F_FULLFSYNC syncing method is used during checkpoint operations on systems that support F_FULLFSYNC. 如果设置了此标志,则在支持F_FULLFSYNC的系统上的检查点操作期间将使用F_FULLFSYNC同步方法. The default value of the checkpoint_fullfsync flag is off. checkpoint_fullfsync标志的默认值是off. Only Mac OS-X supports F_FULLFSYNC. 仅Mac OS-X支持F_FULLFSYNC.

If the fullfsync flag is set, then the F_FULLFSYNC syncing method is used for all sync operations and the checkpoint_fullfsync setting is irrelevant. 如果设置了fullfsync标志,则将F_FULLFSYNC同步方法用于所有同步操作,并且checkpoint_fullfsync设置无关紧要.

PRAGMA collation_list PRAGMA collat​​ion_list

PRAGMA collation_list; PRAGMA collat​​ion_list;

Return a list of the collating sequences defined for the current database connection. 返回为当前数据库连接定义的整理序列的列表.

PRAGMA compile_options PRAGMA compile_options

PRAGMA compile_options; PRAGMA compile_options;

This pragma returns the names of compile-time options used when building SQLite, one option per row. 此编译指示返回在构建SQLite 使用的编译时选项的名称,每行一个选项. The "SQLITE_" prefix is omitted from the returned option names. 返回的选项名称中省略了" SQLITE_"前缀. See also the sqlite3_compileoption_get() C/C++ interface and the sqlite_compileoption_get() SQL functions. 另请参见sqlite3_compileoption_get() C / C ++接口和sqlite_compileoption_get() SQL函数.

PRAGMA count_changes PRAGMA count_changes

PRAGMA count_changes; PRAGMA count_changes;
PRAGMA count_changes = boolean ; PRAGMA count_changes =布尔值;

Query or change the count-changes flag. 查询或更改计数更改标志. Normally, when the count-changes flag is not set, INSERT , UPDATE and DELETE statements return no data. 通常,当未设置count-changes标志时, INSERTUPDATEDELETE语句不返回任何数据. When count-changes is set, each of these commands returns a single row of data consisting of one integer value - the number of rows inserted, modified or deleted by the command. 设置计数更改后,这些命令中的每条命令都返回一行数据,该数据由一个整数值组成-该命令插入,修改或删除的行数. The returned change count does not include any insertions, modifications or deletions performed by triggers, any changes made automatically by foreign key actions , or updates caused by an upsert . 返回的变更计数不包括触发器执行的任何插入,修改或删除, 外键动作自动进行的任何更改或upsert引起的更新 .

Another way to get the row change counts is to use the sqlite3_changes() or sqlite3_total_changes() interfaces. 获取行更改计数的另一种方法是使用sqlite3_changes()sqlite3_total_changes()接口. There is a subtle different, though. 但是,有一个细微的不同. When an INSERT, UPDATE, or DELETE is run against a view using an INSTEAD OF trigger , the count_changes pragma reports the number of rows in the view that fired the trigger, whereas sqlite3_changes() and sqlite3_total_changes() do not. 当使用INSTEAD OF触发器对视图运行INSERT,UPDATE或DELETE时,count_changes编译指示将报告触发该触发器的视图中的行数,而sqlite3_changes()sqlite3_total_changes()则不会.

This pragma is deprecated and exists for backwards compatibility only. 不建议使用此编译指示,并且仅为了向后兼容而存在. New applications should avoid using this pragma. 新的应用程序应避免使用此编译指示. Older applications should discontinue use of this pragma at the earliest opportunity. 较旧的应用程序应尽早停止使用该实用程序. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED . 使用SQLITE_OMIT_DEPRECATED编译SQLite时,可以从构建中省略此编译指示.

PRAGMA data_store_directory PRAGMA data_store_directory

PRAGMA data_store_directory; PRAGMA data_store_directory;
PRAGMA data_store_directory = ' directory-name '; PRAGMA data_store_directory =' 目录名 ';

Query or change the value of the sqlite3_data_directory global variable, which windows operating-system interface backends use to determine where to store database files specified using a relative pathname. 查询或更改sqlite3_data_directory全局变量的值,Windows操作系统接口后端使用该变量来确定使用相对路径名指定的数据库文件的存储位置.

Changing the data_store_directory setting is not threadsafe. 更改data_store_directory设置不是线程安全的. Never change the data_store_directory setting if another thread within the application is running any SQLite interface at the same time. 如果应用程序中的另一个线程正在同时运行任何SQLite接口,则切勿更改data_store_directory设置. Doing so results in undefined behavior. 这样做会导致未定义的行为. Changing the data_store_directory setting writes to the sqlite3_data_directory global variable and that global variable is not protected by a mutex. 更改data_store_directory设置将写入sqlite3_data_directory全局变量,并且该全局变量不受互斥锁的保护.

This facility is provided for WinRT which does not have an OS mechanism for reading or changing the current working directory. 此功能是为WinRT提供的,它不具有用于读取或更改当前工作目录的OS机制. The use of this pragma in any other context is discouraged and may be disallowed in future releases. 不建议在任何其他情况下使用此实用程序,并且在将来的发行版中可能会禁止使用该实用程序.

This pragma is deprecated and exists for backwards compatibility only. 不建议使用此编译指示,并且仅为了向后兼容而存在. New applications should avoid using this pragma. 新的应用程序应避免使用此编译指示. Older applications should discontinue use of this pragma at the earliest opportunity. 较旧的应用程序应尽早停止使用该实用程序. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED . 使用SQLITE_OMIT_DEPRECATED编译SQLite时,可以从构建中省略此编译指示.

PRAGMA data_version PRAGMA data_version

PRAGMA schema. PRAGMA 模式. data_version; data_version;

The "PRAGMA data_version" command provides an indication that the database file has been modified. " PRAGMA data_version"命令提供数据库文件已被修改的指示. Interactive programs that hold database content in memory or that display database content on-screen can use the PRAGMA data_version command to determine if they need to flush and reload their memory or update the screen display. 将数据库内容保存在内存中或在屏幕上显示数据库内容的交互式程序可以使用PRAGMA data_version命令来确定是否需要刷新并重新加载其内存或更新屏幕显示.

The integer values returned by two invocations of "PRAGMA data_version" from the same connection will be different if changes were committed to the database by any other connection in the interim. 如果在此期间任何其他连接将更改提交给数据库,则从同一连接两次调用" PRAGMA data_version"返回的整数值将有所不同. The "PRAGMA data_version" value is unchanged for commits made on the same database connection. 对于在同一数据库连接上进行的提交," PRAGMA data_version"值不变. The behavior of "PRAGMA data_version" is the same for all database connections, including database connections in separate processes and shared cache database connections. 所有数据库连接(包括独立进程中的数据库连接和共享缓存数据库连接)的" PRAGMA data_version"的行为都相同.

The "PRAGMA data_version" value is a local property of each database connection and so values returned by two concurrent invocations of "PRAGMA data_version" on separate database connections are often different even though the underlying database is identical. " PRAGMA data_version"值是每个数据库连接的本地属性,因此,即使基础数据库相同,两次在不同数据库连接上并发调用" PRAGMA data_version"所返回的值也常常不同. It is only meaningful to compare the "PRAGMA data_version" values returned by the same database connection at two different points in time. 比较在两个不同时间点由同一数据库连接返回的" PRAGMA data_version"值才有意义. PRAGMA database_list PRAGMA database_list


PRAGMA database_list; PRAGMA database_list;

This pragma works like a query to return one row for each database attached to the current database connection. 此实用程序的作用就像查询一样,为连接到当前数据库连接的每个数据库返回一行. The second column is "main" for the main database file, "temp" for the database file used to store TEMP objects, or the name of the ATTACHed database for other database files. 第二列是用于主数据库文件的" main",用于存储TEMP对象的数据库文件的" temp"或用于其他数据库文件的ATTACHed数据库的名称. The third column is the name of the database file itself, or an empty string if the database is not associated with a file. 第三列是数据库文件本身的名称,如果数据库未与文件关联,则为空字符串.

PRAGMA default_cache_size PRAGMA default_cache_size
PRAGMA schema. PRAGMA 模式. default_cache_size; default_cache_size;
PRAGMA schema. PRAGMA 模式. default_cache_size = Number-of-pages ; default_cache_size = 页数 ;

This pragma queries or sets the suggested maximum number of pages of disk cache that will be allocated per open database file. 此实用程序查询或设置建议为每个打开的数据库文件分配的最大磁盘高速缓存页数. The difference between this pragma and cache_size is that the value set here persists across database connections. 该编译指示和cache_size之间的区别在于,此处设置的值在数据库连接之间保持不变 . The value of the default cache size is stored in the 4-byte big-endian integer located at offset 48 in the header of the database file. 默认高速缓存大小的值存储在数据库文件头中偏移量48处的4字节big-endian整数中.

This pragma is deprecated and exists for backwards compatibility only. 不建议使用此编译指示,并且仅为了向后兼容而存在. New applications should avoid using this pragma. 新的应用程序应避免使用此编译指示. Older applications should discontinue use of this pragma at the earliest opportunity. 较旧的应用程序应尽早停止使用该实用程序. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED . 使用SQLITE_OMIT_DEPRECATED编译SQLite时,可以从构建中省略此编译指示.

PRAGMA defer_foreign_keys PRAGMA defer_foreign_keys

PRAGMA defer_foreign_keys PRAGMA defer_foreign_keys
PRAGMA defer_foreign_keys = boolean ; PRAGMA defer_foreign_keys = 布尔值 ;

When the defer_foreign_keys PRAGMA is on, enforcement of all foreign key constraints is delayed until the outermost transaction is committed. 当defer_foreign_keys PRAGMA处于打开状态时,所有外键约束的实施都将延迟,直到最外面的事务被提交为止. The defer_foreign_keys pragma defaults to OFF so that foreign key constraints are only deferred if they are created as "DEFERRABLE INITIALLY DEFERRED". defer_foreign_keys编译指示默认为OFF,以便仅在将外键约束创建为" DEFERRABLE INITIALIALDEFERRED"时才将其推迟. The defer_foreign_keys pragma is automatically switched off at each COMMIT or ROLLBACK. defer_foreign_keys编译指示会在每个COMMIT或ROLLBACK处自动关闭. Hence, the defer_foreign_keys pragma must be separately enabled for each transaction. 因此,必须为每个事务分别启用defer_foreign_keys编译指示. This pragma is only meaningful if foreign key constraints are enabled, of course. 当然,只有在启用了外键约束的情况下,这种编译指示才有意义.

The sqlite3_db_status (db, SQLITE_DBSTATUS_DEFERRED_FKS ,...) C-language interface can be used during a transaction to determine if there are deferred and unresolved foreign key constraints. 在事务期间可以使用sqlite3_db_status (db, SQLITE_DBSTATUS_DEFERRED_FKS ,...)C语言接口来确定是否存在延迟和未解决的外键约束.

PRAGMA empty_result_callbacks PRAGMA empty_result_callbacks

PRAGMA empty_result_callbacks; PRAGMA empty_result_callbacks;
PRAGMA empty_result_callbacks = boolean ; PRAGMA empty_result_callbacks = 布尔值 ;

Query or change the empty-result-callbacks flag. 查询或更改empty-result-callbacks标志.

The empty-result-callbacks flag affects the sqlite3_exec() API only. empty-result-callbacks标志仅影响sqlite3_exec() API. Normally, when the empty-result-callbacks flag is cleared, the callback function supplied to the sqlite3_exec() is not invoked for commands that return zero rows of data. 通常,当清除empty-result-callbacks标志时,不会为返回零行数据的命令调用提供给sqlite3_exec()的回调函数. When empty-result-callbacks is set in this situation, the callback function is invoked exactly once, with the third parameter set to 0 (NULL). 如果在这种情况下设置了empty-result-callbacks,则将回调函数仅调用一次,第三个参数设置为0(NULL). This is to enable programs that use the sqlite3_exec() API to retrieve column-names even when a query returns no data. 这是为了使使用sqlite3_exec() API的程序即使在查询不返回任何数据时也可以检索列名.

This pragma is deprecated and exists for backwards compatibility only. 不建议使用此编译指示,并且仅为了向后兼容而存在. New applications should avoid using this pragma. 新的应用程序应避免使用此编译指示. Older applications should discontinue use of this pragma at the earliest opportunity. 较旧的应用程序应尽早停止使用该实用程序. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED . 使用SQLITE_OMIT_DEPRECATED编译SQLite时,可以从构建中省略此编译指示.

PRAGMA encoding PRAGMA编码

PRAGMA encoding; PRAGMA编码;
PRAGMA encoding = 'UTF-8'; PRAGMA编码='UTF-8';
PRAGMA encoding = 'UTF-16'; PRAGMA编码='UTF-16';
PRAGMA encoding = 'UTF-16le'; PRAGMA编码='UTF-16le';
PRAGMA encoding = 'UTF-16be'; PRAGMA编码='UTF-16be';

In first form, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of 'UTF-8', 'UTF-16le' (little-endian UTF-16 encoding) or 'UTF-16be' (big-endian UTF-16 encoding). 在第一种形式中,如果已经创建了主数据库,则此编译指示将返回主数据库使用的文本编码,即'UTF-8','UTF-16le'(小尾数UTF-16编码)或' UTF-16be"(Big-endian UTF-16编码). If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session. 如果尚未创建主数据库,则返回的值是将用于创建主数据库的文本编码(如果此会话已创建).

The second through fifth forms of this pragma set the encoding that the main database will be created with if it is created by this session. 此编译指示的第二种形式到第五种形式设置了如果通过此会话创建主数据库将使用的编码. The string 'UTF-16' is interpreted as "UTF-16 encoding using native machine byte-ordering". 字符串'UTF-16'被解释为"使用本地机器字节顺序的UTF-16编码". It is not possible to change the text encoding of a database after it has been created and any attempt to do so will be silently ignored. 创建数据库后,无法更改其文本编码,并且任何尝试都会被忽略.

Once an encoding has been set for a database, it cannot be changed. 一旦为数据库设置了编码,便无法更改.

Databases created by the ATTACH command always use the same encoding as the main database. ATTACH命令创建的数据库始终使用与主数据库相同的编码. An attempt to ATTACH a database with a different text encoding from the "main" database will fail. 尝试使用与"主"数据库不同的文本编码来附加数据库的尝试将失败.

PRAGMA foreign_key_check PRAGMA foreign_key_check

PRAGMA schema. PRAGMA 模式. foreign_key_check; foreign_key_check;
PRAGMA schema. PRAGMA 模式. foreign_key_check( table-name ); foreign_key_check( table-name );

The foreign_key_check pragma checks the database, or the table called " table-name ", for foreign key constraints that are violated and returns one row of output for each violation. foreign_key_check编译指示检查数据库或称为" table-name "的表是否存在违反的外键约束 ,并为每次违反返回一行输出. There are four columns in each result row. 每个结果行有四列. The first column is the name of the table that contains the REFERENCES clause. 第一列是包含REFERENCES子句的表的名称. The second column is the rowid of the row that contains the invalid REFERENCES clause, or NULL if the child table is a WITHOUT ROWID table. 第二列是包含无效REFERENCES子句的行的rowid ;如果子表是WITHOUT ROWID表,则为NULL. The third column is the name of the table that is referred to. 第三列是所引用表的名称. The fourth column is the index of the specific foreign key constraint that failed. 第四列是失败的特定外键约束的索引. The fourth column in the output of the foreign_key_check pragma is the same integer as the first column in the output of the foreign_key_list pragma . 在foreign_key_check编译指示的输出处的第四列是相同的整数作为输出的第一列foreign_key_list编译 . When a " table-name " is specified, the only foreign key constraints checked are those created by REFERENCES clauses in the CREATE TABLE statement for table-name . 指定" table-name "时,检查的唯一外键约束是由CREATE TABLE语句中的table-name的 REFERENCES子句创建的约束.

PRAGMA foreign_key_list PRAGMA foreign_key_list

PRAGMA foreign_key_list( table-name ); PRAGMA foreign_key_list( table-name );

This pragma returns one row for each foreign key constraint created by a REFERENCES clause in the CREATE TABLE statement of table " table-name ". 对于由" 表名 "表的CREATE TABLE语句中的REFERENCES子句创建的每个外键约束,此编译指示均返回一行. PRAGMA foreign_keys PRAGMA外键


PRAGMA foreign_keys; PRAGMA外键;
PRAGMA foreign_keys = boolean ; PRAGMA foreign_keys = 布尔值 ;

Query, set, or clear the enforcement of foreign key constraints . 查询,设置或清除外键约束的执行.

This pragma is a no-op within a transaction; 这种务实是交易中的禁忌. foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT . 仅当没有待处理的BEGINSAVEPOINT时,才可以启用或禁用外键约束强制.

Changing the foreign_keys setting affects the execution of all statements prepared using the database connection, including those prepared before the setting was changed. 更改foreign_keys设置会影响使用数据库连接准备的所有语句的执行,包括更改设置之前准备的语句. Any existing statements prepared using the legacy sqlite3_prepare() interface may fail with an SQLITE_SCHEMA error after the foreign_keys setting is changed. 更改foreign_keys设置后,使用旧版sqlite3_prepare()接口准备的任何现有语句都可能因SQLITE_SCHEMA错误而失败.

As of SQLite version 3.6.19 , the default setting for foreign key enforcement is OFF. 从SQLite 3.6.19版本开始 ,外键强制的默认设置为OFF. However, that might change in a future release of SQLite. 但是,这可能会在将来的SQLite版本中发生变化. The default setting for foreign key enforcement can be specified at compile-time using the SQLITE_DEFAULT_FOREIGN_KEYS preprocessor macro. 可以在编译时使用SQLITE_DEFAULT_FOREIGN_KEYS预处理程序宏指定外键强制的默认设置. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting. 为了最大程度地减少将来的问题,应用程序应根据应用程序的要求设置外键强制标志,而不应依赖于默认设置. PRAGMA freelist_count PRAGMA freelist_count


PRAGMA schema. PRAGMA 模式. freelist_count; freelist_count;

Return the number of unused pages in the database file. 返回数据库文件中未使用的页数.

PRAGMA full_column_names PRAGMA full_column_names

PRAGMA full_column_names; PRAGMA full_column_names;
PRAGMA full_column_names = boolean ; PRAGMA full_column_names = 布尔值 ;

Query or change the full_column_names flag. 查询或更改full_column_names标志. This flag together with the short_column_names flag determine the way SQLite assigns names to result columns of SELECT statements. 此标志与short_column_names标志一起确定SQLite将名称分配给SELECT语句的结果列的方式. Result columns are named by applying the following rules in order: 通过按以下顺序应用以下规则来命名结果列:

  1. If there is an AS clause on the result, then the name of the column is the right-hand side of the AS clause. 如果结果上有一个AS子句,则该列的名称位于AS子句的右侧.

  2. If the result is a general expression, not a just the name of a source table column, then the name of the result is a copy of the expression text. 如果结果是通用表达式,而不仅仅是源表列的名称,那么结果的名称就是表达式文本的副本.

  3. If the short_column_names pragma is ON, then the name of the result is the name of the source table column without the source table name prefix: COLUMN. 如果short_column_names编译指示为ON,则结果的名称为没有源表名称前缀COLUMN的源表列的名称.

  4. If both pragmas short_column_names and full_column_names are OFF then case (2) applies. 如果编译指示short_column_namesfull_column_names均为OFF,则情况(2)适用.

  5. The name of the result column is a combination of the source table and source column name: TABLE.COLUMN 结果列的名称是源表和源列名称的组合:TABLE.COLUMN

This pragma is deprecated and exists for backwards compatibility only. 不建议使用此编译指示,并且仅为了向后兼容而存在. New applications should avoid using this pragma. 新的应用程序应避免使用此编译指示. Older applications should discontinue use of this pragma at the earliest opportunity. 较旧的应用程序应尽早停止使用该实用程序. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED . 使用SQLITE_OMIT_DEPRECATED编译SQLite时,可以从构建中省略此编译指示.

PRAGMA fullfsync PRAGMA fullfsync

PRAGMA fullfsync PRAGMA fullfsync
PRAGMA fullfsync = boolean ; PRAGMA fullfsync = 布尔值 ;

Query or change the fullfsync flag. 查询或更改fullfsync标志. This flag determines whether or not the F_FULLFSYNC syncing method is used on systems that support it. 此标志确定F_FULLFSYNC同步方法是否在支持该方法的系统上使用. The default value of the fullfsync flag is off. fullfsync标志的默认值是off. Only Mac OS X supports F_FULLFSYNC. 仅Mac OS X支持F_FULLFSYNC.

See also checkpoint_fullfsync . 另请参阅checkpoint_fullfsync .

PRAGMA function_list PRAGMA function_list

PRAGMA function_list; PRAGMA function_list;

This pragma returns a list of SQL functions known to the database connection. 该编译指示返回数据库连接已知的SQL函数列表. Each row of the result describes a single calling signature for a single SQL function. 结果的每一行都描述单个SQL函数的单个调用签名. Some SQL functions will have multiple rows in the result set if they can (for example) be invoked with a varying number of arguments or can accept text in various encodings. 如果某些SQL函数(例如)可以用不同数量的参数调用或可以接受各种编码的文本,则它们在结果集中将具有多行. PRAGMA hard_heap_limit PRAGMA hard_heap_limit


PRAGMA hard_heap_limit PRAGMA hard_heap_limit
PRAGMA hard_heap_limit= N PRAGMA hard_heap_limit = N

This pragma invokes the sqlite3_hard_heap_limit64() interface with the argument N, if N is specified and N is a positive integer that is less than the current hard heap limit. 如果指定了N且N是小于当前硬堆限制的正整数,则该编译指示使用参数N调用sqlite3_hard_heap_limit64()接口. The hard_heap_limit pragma always returns the same integer that would be returned by the sqlite3_hard_heap_limit64 (-1) C-language function. hard_heap_limit杂注总是返回与sqlite3_hard_heap_limit64 (-1)C语言函数返回的整数相同的整数. That is to say, it always returns the value of the hard heap limit that is set after any changes imposed by this PRAGMA. 也就是说,它总是返回在此PRAGMA施加任何更改之后设置的硬堆限制的值.

This pragma can only lower the heap limit, never raise it. 此编译指示只能降低堆限制,而不能提高堆限制. The C-language interface sqlite3_hard_heap_limit64() must be used to raise the heap limit. 必须使用C语言接口sqlite3_hard_heap_limit64()来提高堆限制.

See also the soft_heap_limit pragma . 另请参阅soft_heap_limit杂注 . PRAGMA ignore_check_constraints PRAGMA ignore_check_constraints


PRAGMA ignore_check_constraints = boolean ; PRAGMA ignore_check_constraints = 布尔值 ;

This pragma enables or disables the enforcement of CHECK constraints. 此实用程序启用或禁用CHECK约束的执行. The default setting is off, meaning that CHECK constraints are enforced by default. 默认设置为off,表示默认情况下会强制执行CHECK约束.

PRAGMA incremental_vacuum PRAGMA增量真空

PRAGMA schema. PRAGMA 模式. incremental_vacuum (N) ; 增量真空 (N) ;
PRAGMA schema. PRAGMA 模式. incremental_vacuum; 增量真空;

The incremental_vacuum pragma causes up to N pages to be removed from the freelist . incremental_vacuum用法导致从空闲列表中删除最多N个页面. The database file is truncated by the same amount. 数据库文件将被截断相同数量. The incremental_vacuum pragma has no effect if the database is not in auto_vacuum=incremental mode or if there are no pages on the freelist. If there are fewer than N pages on the freelist, or if N is less than 1, or if the "( N )" argument is omitted, then the entire freelist is cleared.

PRAGMA index_info

PRAGMA schema. index_info( index-name );

This pragma returns one row for each key column in the named index. A key column is a column that is actually named in the CREATE INDEX index statement or UNIQUE constraint or PRIMARY KEY constraint that created the index. Index entries also usually contain auxiliary columns that point back to the table row being indexed. The auxiliary index-columns are not shown by the index_info pragma, but they are listed by the index_xinfo pragma .

Output columns from the index_info pragma are as follows:

  1. The rank of the column within the index. (0 means left-most.)
  2. The rank of the column within the table being indexed. A value of -1 means rowid and a value of -2 means that an expression is being used.
  3. The name of the column being indexed. This columns is NULL if the column is the rowid or an expression .

If there is no index named index-name but there is a WITHOUT ROWID table with that name, then (as of SQLite version 3.30.0 on 2019-10-04) this pragma returns the PRIMARY KEY columns of the WITHOUT ROWID table as they are used in the records of the underlying b-tree, which is to say with duplicate columns removed. PRAGMA index_list


PRAGMA schema. index_list( table-name );

This pragma returns one row for each index associated with the given table.

Output columns from the index_list pragma are as follows:

  1. A sequence number assigned to each index for internal tracking purposes.
  2. The name of the index.
  3. "1" if the index is UNIQUE and "0" if not.
  4. "c" if the index was created by a CREATE INDEX statement, "u" if the index was created by a UNIQUE constraint , or "pk" if the index was created by a PRIMARY KEY constraint .
  5. "1" if the index is a partial index and "0" if not.

PRAGMA index_xinfo

PRAGMA schema. index_xinfo( index-name );

This pragma returns information about every column in an index. Unlike this index_info pragma , this pragma returns information about every column in the index, not just the key columns. (A key column is a column that is actually named in the CREATE INDEX index statement or UNIQUE constraint or PRIMARY KEY constraint that created the index. Auxiliary columns are additional columns needed to locate the table entry that corresponds to each index entry.)

Output columns from the index_xinfo pragma are as follows:

  1. The rank of the column within the index. (0 means left-most. Key columns come before auxiliary columns.)
  2. The rank of the column within the table being indexed, or -1 if the index-column is the rowid of the table being indexed and -2 if the index is on an expression .
  3. The name of the column being indexed, or NULL if the index-column is the rowid of the table being indexed or an expression .
  4. 1 if the index-column is sorted in reverse (DESC) order by the index and 0 otherwise.
  5. The name for the collating sequence used to compare values in the index-column.
  6. 1 if the index-column is a key column and 0 if the index-column is an auxiliary column.

If there is no index named index-name but there is a WITHOUT ROWID table with that name, then (as of SQLite version 3.30.0 on 2019-10-04) this pragma returns the columns of the WITHOUT ROWID table as they are used in the records of the underlying b-tree, which is to say with de-duplicated PRIMARY KEY columns first followed by data columns. PRAGMA integrity_check


PRAGMA schema. integrity_check;
PRAGMA schema. integrity_check( N )

This pragma does an integrity check of the entire database. The integrity_check pragma looks for out-of-order records, missing pages, malformed records, missing index entries, and UNIQUE, CHECK, and NOT NULL constraint errors. If the integrity_check pragma finds problems, strings are returned (as multiple rows with a single column per row) which describe the problems. Pragma integrity_check will return at most N errors before the analysis quits, with N defaulting to 100. If pragma integrity_check finds no errors, a single row with the value 'ok' is returned.

PRAGMA integrity_check does not find FOREIGN KEY errors. Use the PRAGMA foreign_key_check command for to find errors in FOREIGN KEY constraints.

See also the PRAGMA quick_check command which does most of the checking of PRAGMA integrity_check but runs much faster.

PRAGMA journal_mode

PRAGMA schema. journal_mode;
PRAGMA schema. journal_mode = DELETE | TRUNCATE | PERSIST | MEMORY | WAL | OFF

This pragma queries or sets the journal mode for databases associated with the current database connection .

The first form of this pragma queries the current journaling mode for database . When database is omitted, the "main" database is queried.

The second form changes the journaling mode for " database " or for all attached databases if " database " is omitted. The new journal mode is returned. If the journal mode could not be changed, the original journal mode is returned.

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the document titled Atomic Commit In SQLite for additional detail.)

The TRUNCATE journaling mode commits transactions by truncating the rollback journal to zero-length instead of deleting it. On many systems, truncating a file is much faster than deleting the file since the containing directory does not need to be changed.

The PERSIST journaling mode prevents the rollback journal from being deleted at the end of each transaction. Instead, the header of the journal is overwritten with zeros. This will prevent other database connections from rolling the journal back. The PERSIST journaling mode is useful as an optimization on platforms where deleting or truncating a file is much more expensive than overwriting the first block of a file with zeros. See also: PRAGMA journal_size_limit and SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT .

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt .

The WAL journaling mode uses a write-ahead log instead of a rollback journal to implement transactions. The WAL journaling mode is persistent; after being set it stays in effect across multiple database connections and after closing and reopening the database. A database in WAL journaling mode can only be accessed by SQLite version 3.7.0 (2010-07-21) or later.

The OFF journaling mode disables the rollback journal completely. No rollback journal is ever created and hence there is never a rollback journal to delete. The OFF journaling mode disables the atomic commit and rollback capabilities of SQLite. The ROLLBACK command no longer works; it behaves in an undefined way. Applications must avoid using the ROLLBACK command when the journal mode is OFF. If the application crashes in the middle of a transaction when the OFF journaling mode is set, then the database file will very likely go corrupt . Without a journal, there is no way for a statement to unwind partially completed operations following a constraint error. This might also leave the database in a corrupted state. For example, if a duplicate entry causes a CREATE UNIQUE INDEX statement to fail half-way through, it will leave behind a partially created, and hence corrupt, index. Because OFF journaling mode allows the database file to be corrupted using ordinary SQL, it is disabled when SQLITE_DBCONFIG_DEFENSIVE is enabled.

Note that the journal_mode for an in-memory database is either MEMORY or OFF and can not be changed to a different value. An attempt to change the journal_mode of an in-memory database to any setting other than MEMORY or OFF is ignored. Note also that the journal_mode cannot be changed while a transaction is active.

PRAGMA journal_size_limit

PRAGMA schema. journal_size_limit
PRAGMA schema. journal_size_limit = N ;

If a database connection is operating in exclusive locking mode or in persistent journal mode (PRAGMA journal_mode=persist) then after committing a transaction the rollback journal file may remain in the file-system. This increases performance for subsequent transactions since overwriting an existing file is faster than append to a file, but it also consumes file-system space. After a large transaction (eg a VACUUM ), the rollback journal file may consume a very large amount of space.

Similarly, in WAL mode , the write-ahead log file is not truncated following a checkpoint . Instead, SQLite reuses the existing file for subsequent WAL entries since overwriting is faster than appending.

The journal_size_limit pragma may be used to limit the size of rollback-journal and WAL files left in the file-system after transactions or checkpoints. Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit.

The second form of the pragma listed above is used to set a new limit in bytes for the specified database. A negative number implies no limit. To always truncate rollback journals and WAL files to their minimum size, set the journal_size_limit to zero. Both the first and second forms of the pragma listed above return a single result row containing a single integer column - the value of the journal size limit in bytes. The default journal size limit is -1 (no limit). The SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT preprocessor macro can be used to change the default journal size limit at compile-time.

This pragma only operates on the single database specified prior to the pragma name (or on the "main" database if no database is specified.) There is no way to change the journal size limit on all attached databases using a single PRAGMA statement. The size limit must be set separately for each attached database. PRAGMA legacy_alter_table


PRAGMA legacy_alter_table;
PRAGMA legacy_alter_table = boolean

This pragma sets or queries the value of the legacy_alter_table flag. When this flag is on, the ALTER TABLE RENAME command (for changing the name of a table) works as it did in SQLite 3.24.0 (2018-06-04) and earlier. More specifically, when this flag is on the ALTER TABLE RENAME command only rewrites the initial occurrence of the table name in its CREATE TABLE statement and in any associated CREATE INDEX and CREATE TRIGGER statements. Other references to the table are unmodified, including:

The default setting for this pragma is OFF, which means that all references to the table anywhere in the schema are converted to the new name.

This pragma is provided as a work-around for older programs that contain code that expect the incomplete behavior of ALTER TABLE RENAME found in older versions of SQLite. New applications should leave this flag turned off.

For compatibility with older virtual table implementations, this flag is turned on temporarily while the sqlite3_module.xRename method is being run. The value of this flag is restore after the sqlite3_module.xRename method finishes.

The legacy alter table behavior can also be toggled on and off using the SQLITE_DBCONFIG_LEGACY_ALTER_TABLE option to the sqlite3_db_config() interface.

The legacy alter table behavior is a per-connection setting. Turning this features on or off affects all attached database files within the database connection . The setting does not persist. Changing this setting in one connection does not affect any other connections. PRAGMA legacy_file_format


PRAGMA legacy_file_format;

This pragma no longer functions. It has become a no-op. The capabilities formerly provided by PRAGMA legacy_file_format are now available using the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option to the sqlite3_db_config() C-language interface.

PRAGMA locking_mode


PRAGMA schema. locking_mode;
PRAGMA schema. locking_mode = NORMAL | EXCLUSIVE

This pragma sets or queries the database connection locking-mode. The locking-mode is either NORMAL or EXCLUSIVE.

In NORMAL locking-mode (the default unless overridden at compile-time using SQLITE_DEFAULT_LOCKING_MODE ), a database connection unlocks the database file at the conclusion of each read or write transaction. When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.

Database locks obtained by a connection in EXCLUSIVE mode may be released either by closing the database connection, or by setting the locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). Simply setting the locking-mode to NORMAL is not enough - locks are not released until the next time the database file is accessed.

There are three reasons to set the locking-mode to EXCLUSIVE.

  1. The application wants to prevent other processes from accessing the database file.
  2. The number of system calls for filesystem operations is reduced, possibly resulting in a small performance increase.
  3. WAL databases can be accessed in EXCLUSIVE mode without the use of shared memory. ( Additional information )

When the locking_mode pragma specifies a particular database, for example:

PRAGMA main. locking_mode=EXCLUSIVE;

Then the locking mode applies only to the named database. If no database name qualifier precedes the "locking_mode" keyword then the locking mode is applied to all databases, including any new databases added by subsequent ATTACH commands.

The "temp" database (in which TEMP tables and indices are stored) and in-memory databases always uses exclusive locking mode. The locking mode of temp and in-memory databases cannot be changed. All other databases use the normal locking mode by default and are affected by this pragma.

If the locking mode is EXCLUSIVE when first entering WAL journal mode , then the locking mode cannot be changed to NORMAL until after exiting WAL journal mode. If the locking mode is NORMAL when first entering WAL journal mode, then the locking mode can be changed between NORMAL and EXCLUSIVE and back again at any time and without needing to exit WAL journal mode.

PRAGMA max_page_count

PRAGMA schema. max_page_count;
PRAGMA schema. max_page_count = N ;

Query or set the maximum number of pages in the database file. Both forms of the pragma return the maximum page count. The second form attempts to modify the maximum page count. The maximum page count cannot be reduced below the current database size.

PRAGMA mmap_size


PRAGMA schema. mmap_size;
PRAGMA schema. mmap_size= N

Query or change the maximum number of bytes that are set aside for memory-mapped I/O on a single database. The first form (without an argument) queries the current limit. The second form (with a numeric argument) sets the limit for the specified database, or for all databases if the optional database name is omitted. In the second form, if the database name is omitted, the limit that is set becomes the default limit for all databases that are added to the database connection by subsequent ATTACH statements.

The argument N is the maximum number of bytes of the database file that will be accessed using memory-mapped I/O. If N is zero then memory mapped I/O is disabled. If N is negative, then the limit reverts to the default value determined by the most recent sqlite3_config ( SQLITE_CONFIG_MMAP_SIZE ), or to the compile time default determined by SQLITE_DEFAULT_MMAP_SIZE if not start-time limit has been set.

The PRAGMA mmap_size statement will never increase the amount of address space used for memory-mapped I/O above the hard limit set by the SQLITE_MAX_MMAP_SIZE compile-time option, nor the hard limit set start-time by the second argument to sqlite3_config( SQLITE_CONFIG_MMAP_SIZE )

The size of the memory-mapped I/O region cannot be changed while the memory-mapped I/O region is in active use, to avoid unmapping memory out from under running SQL statements. For this reason, the mmap_size pragma may be a no-op if the prior mmap_size is non-zero and there are other SQL statements running concurrently on the same database connection .

PRAGMA module_list

PRAGMA module_list;

This pragma returns a list of virtual table modules registered with the database connection. PRAGMA optimize


PRAGMA optimize;
PRAGMA optimize( MASK );
PRAGMA schema .optimize;
PRAGMA schema .optimize( MASK );

Attempt to optimize the database. All schemas are optimized in the first two forms, and only the specified schema is optimized in the latter two.

To achieve the best long-term query performance without the need to do a detailed engineering analysis of the application schema and SQL, it is recommended that applications run "PRAGMA optimize" (with no arguments) just before closing each database connection . Long-running applications might also benefit from setting a timer to run "PRAGMA optimize" every few hours.

This pragma is usually a no-op or nearly so and is very fast. However if SQLite feels that performing database optimizations (such as running ANALYZE or creating new indexes) will improve the performance of future queries, then some database I/O may be done. Applications that want to limit the amount of work performed can set a timer that will invoke sqlite3_interrupt() if the pragma goes on for too long. Or, since SQLite 3.32.0, the application can use PRAGMA analysis_limit= N for some small value of N (a few hundred or a few thousand) to limit the depth of analyze.

The details of optimizations performed by this pragma are expected to change and improve over time. Applications should anticipate that this pragma will perform new optimizations in future releases.

The optional MASK argument is a bitmask of optimizations to perform:

  1. Debugging mode. Do not actually perform any optimizations but instead return one line of text for each optimization that would have been done. Off by default.

  2. Run ANALYZE on tables that might benefit. On by default. See below for additional information.

  3. (Not yet implemented) Record usage and performance information from the current session in the database file so that it will be available to "optimize" pragmas run by future database connections.

  4. (Not yet implemented) Create indexes that might have been helpful to recent queries.

The default MASK is and always shall be 0xfffe. The 0xfffe mask means perform all of the optimizations listed above except Debug Mode. If new optimizations are added in the future that should be off by default, those new optimizations will be given a mask of 0x10000 or larger.

To see all optimizations that would have been done without actually doing them, run "PRAGMA optimize(-1)". To use only the ANALYZE optimization, run "PRAGMA optimize(0x02)".

Determination Of When To Run Analyze

In the current implementation, a table is analyzed if and only if all of the following are true:

The rules for when tables are analyzed are likely to change in future releases. PRAGMA page_count


PRAGMA schema. page_count;

Return the total number of pages in the database file.

PRAGMA page_size

PRAGMA schema. page_size;
PRAGMA schema. page_size = bytes ;

Query or set the page size of the database. The page size must be a power of two between 512 and 65536 inclusive.

When a new database is created, SQLite assigns a page size to the database based on platform and filesystem. For many years, the default page size was almost always 1024 bytes, but beginning with SQLite version 3.12.0 (2016-03-29), the default page size increased to 4096. The default page size is recommended for most applications.

Specifying a new page size does not change the page size immediately. Instead, the new page size is remembered and is used to set the page size when the database is first created, if it does not already exist when the page_size pragma is issued, or at the next VACUUM command that is run on the same database connection while not in WAL mode .

The SQLITE_DEFAULT_PAGE_SIZE compile-time option can be used to change the default page size assigned to new databases. PRAGMA parser_trace


PRAGMA parser_trace = boolean ;

If SQLite has been compiled with the SQLITE_DEBUG compile-time option, then the parser_trace pragma can be used to turn on tracing for the SQL parser used internally by SQLite. This feature is used for debugging SQLite itself.

This pragma is intended for use when debugging SQLite itself. It is only available when the SQLITE_DEBUG compile-time option is used.

PRAGMA pragma_list

PRAGMA pragma_list;

This pragma returns a list of PRAGMA commands known to the database connection. PRAGMA query_only


PRAGMA query_only;
PRAGMA query_only = boolean ;

The query_only pragma prevents all changes to database files when enabled.

PRAGMA quick_check

PRAGMA schema. quick_check;
PRAGMA schema. quick_check( N )

The pragma is like integrity_check except that it does not verify UNIQUE constraints and does not verify that index content matches table content. By skipping UNIQUE and index consistency checks, quick_check is able to run much faster than integrity_check. Otherwise the two pragmas are the same.

PRAGMA read_uncommitted

PRAGMA read_uncommitted;
PRAGMA read_uncommitted = boolean ;

Query, set, or clear READ UNCOMMITTED isolation. The default isolation level for SQLite is SERIALIZABLE. Any process or thread can select READ UNCOMMITTED isolation, but SERIALIZABLE will still be used except between connections that share a common page and schema cache. Cache sharing is enabled using the sqlite3_enable_shared_cache() API. Cache sharing is disabled by default.

See SQLite Shared-Cache Mode for additional information.

PRAGMA recursive_triggers

PRAGMA recursive_triggers;
PRAGMA recursive_triggers = boolean ;

Query, set, or clear the recursive trigger capability.

Changing the recursive_triggers setting affects the execution of all statements prepared using the database connection, including those prepared before the setting was changed. Any existing statements prepared using the legacy sqlite3_prepare() interface may fail with an SQLITE_SCHEMA error after the recursive_triggers setting is changed.

Prior to SQLite version 3.6.18 (2009-09-11), recursive triggers were not supported. The behavior of SQLite was always as if this pragma was set to OFF. Support for recursive triggers was added in version 3.6.18 but was initially turned OFF by default, for compatibility. Recursive triggers may be turned on by default in future versions of SQLite.

The depth of recursion for triggers has a hard upper limit set by the SQLITE_MAX_TRIGGER_DEPTH compile-time option and a run-time limit set by sqlite3_limit (db, SQLITE_LIMIT_TRIGGER_DEPTH ,...).

PRAGMA reverse_unordered_selects

PRAGMA reverse_unordered_selects;
PRAGMA reverse_unordered_selects = boolean ;

When enabled, this PRAGMA causes many SELECT statements without an ORDER BY clause to emit their results in the reverse order from what they normally would. This can help debug applications that are making invalid assumptions about the result order. The reverse_unordered_selects pragma works for most SELECT statements, however the query planner may sometimes choose an algorithm that is not easily reversed, in which case the output will appear in the same order regardless of the reverse_unordered_selects setting.

SQLite makes no guarantees about the order of results if a SELECT omits the ORDER BY clause. Even so, the order of results does not change from one run to the next, and so many applications mistakenly come to depend on the arbitrary output order whatever that order happens to be. However, sometimes new versions of SQLite will contain optimizer enhancements that will cause the output order of queries without ORDER BY clauses to shift. When that happens, applications that depend on a certain output order might malfunction. By running the application multiple times with this pragma both disabled and enabled, cases where the application makes faulty assumptions about output order can be identified and fixed early, reducing problems that might be caused by linking against a different version of SQLite.

PRAGMA schema_version

PRAGMA schema. schema_version;
PRAGMA schema. schema_version = integer ;

The schema_version pragma will get or set the value of the schema-version integer at offset 40 in the database header .

SQLite automatically increments the schema-version whenever the schema changes. As each SQL statement runs, the schema version is checked to ensure that the schema has not changed since the SQL statement was prepared . Subverting this mechanism by using "PRAGMA schema_version" may cause SQL statement to run using an obsolete schema, which can lead to incorrect answers and/or database corruption .

Warning: Misuse of this pragma can result in database corruption .

For the purposes of this pragma, the VACUUM command is considered a schema change, since VACUUM will usual alter the "rootpage" values for entries in the sqlite_master table .

See also the application_id pragma and user_version pragma . PRAGMA secure_delete


PRAGMA schema. secure_delete;
PRAGMA schema. secure_delete = boolean | FAST

Query or change the secure-delete setting. When secure_delete is on, SQLite overwrites deleted content with zeros. The default setting for secure_delete is determined by the SQLITE_SECURE_DELETE compile-time option and is normally off. The off setting for secure_delete improves performance by reducing the number of CPU cycles and the amount of disk I/O. Applications that wish to avoid leaving forensic traces after content is deleted or updated should enable the secure_delete pragma prior to performing the delete or update, or else run VACUUM after the delete or update.

The "fast" setting for secure_delete (added circa 2017-08-01) is an intermediate setting in between "on" and "off". When secure_delete is set to "fast", SQLite will overwrite deleted content with zeros only if doing so does not increase the amount of I/O. In other words, the "fast" setting uses more CPU cycles but does not use more I/O. This has the effect of purging all old content from b-tree pages , but leaving forensic traces on freelist pages .

When there are attached databases and no database is specified in the pragma, all databases have their secure-delete setting altered. The secure-delete setting for newly attached databases is the setting of the main database at the time the ATTACH command is evaluated.

When multiple database connections share the same cache, changing the secure-delete flag on one database connection changes it for them all.

PRAGMA short_column_names

PRAGMA short_column_names;
PRAGMA short_column_names = boolean ;

Query or change the short-column-names flag. This flag affects the way SQLite names columns of data returned by SELECT statements. See the full_column_names pragma for full details.

This pragma is deprecated and exists for backwards compatibility only. New applications should avoid using this pragma. Older applications should discontinue use of this pragma at the earliest opportunity. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED .

PRAGMA shrink_memory

PRAGMA shrink_memory

This pragma causes the database connection on which it is invoked to free up as much memory as it can, by calling sqlite3_db_release_memory() .

PRAGMA soft_heap_limit

PRAGMA soft_heap_limit
PRAGMA soft_heap_limit= N

This pragma invokes the sqlite3_soft_heap_limit64() interface with the argument N, if N is specified and is a non-negative integer. The soft_heap_limit pragma always returns the same integer that would be returned by the sqlite3_soft_heap_limit64 (-1) C-language function.

See also the hard_heap_limit pragma . PRAGMA stats


PRAGMA stats;

This pragma returns auxiliary information about tables and indices. The returned information is used during testing to help verify that the query planner is operating correctly. The format and meaning of this pragma will likely change from one release to the next. Because of its volatility, the behavior and output format of this pragma are deliberately undocumented.

The intended use of this pragma is only for testing and validation of SQLite. This pragma is subject to change without notice and is not recommended for use by application programs.

PRAGMA synchronous

PRAGMA schema. synchronous;
PRAGMA schema. synchronous = 0 | OFF | 1 | 1 | NORMAL | 2 | 2 | FULL | 3 | EXTRA ;

Query or change the setting of the "synchronous" flag. The first (query) form will return the synchronous setting as an integer. The second form changes the synchronous setting. The meanings of the various synchronous settings are as follows:

EXTRA (3)
EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss.
FULL (2)
When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode .
NORMAL (1)
When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode =DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode .
OFF (0)
With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.

In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions. With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss. Transactions are consistent with or without the extra syncs provided by synchronous=FULL. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode.

The TEMP schema always has synchronous=OFF since the content of of TEMP is ephemeral and is not expected to survive a power outage. Attempts to change the synchronous setting for TEMP are silently ignored.

See also the fullfsync and checkpoint_fullfsync pragmas.

PRAGMA table_info

PRAGMA schema. table_info( table-name );

This pragma returns one row for each column in the named table. Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

The table named in the table_info pragma can also be a view.

See also: PRAGMA table_xinfo PRAGMA table_xinfo


PRAGMA schema. table_xinfo( table-name );

This pragma returns one row for each column in the named table, including hidden columns in virtual tables. The output is the same as for PRAGMA table_info except that hidden columns are shown rather than being omitted. PRAGMA temp_store


PRAGMA temp_store;
PRAGMA temp_store = 0 | DEFAULT | 1 | 1 | FILE | 2 | 2 | MEMORY ;

Query or change the setting of the " temp_store " parameter. When temp_store is DEFAULT (0), the compile-time C preprocessor macro SQLITE_TEMP_STORE is used to determine where temporary tables and indices are stored. When temp_store is MEMORY (2) temporary tables and indices are kept in as if they were pure in-memory databases memory. When temp_store is FILE (1) temporary tables and indices are stored in a file. The temp_store_directory pragma can be used to specify the directory containing temporary files when FILE is specified. When the temp_store setting is changed, all existing temporary tables, indices, triggers, and views are immediately deleted.

It is possible for the library compile-time C preprocessor symbol SQLITE_TEMP_STORE to override this pragma setting. The following table summarizes the interaction of the SQLITE_TEMP_STORE preprocessor macro and the temp_store pragma:

SQLITE_TEMP_STORE PRAGMA
temp_store
Storage used for
TEMP tables and indices
0 0 any 任何 file 文件
1 1个 0 0 file 文件
1 1个 1 1个 file 文件
1 1个 2 2 memory 记忆
2 2 0 0 memory 记忆
2 2 1 1个 file 文件
2 2 2 2 memory 记忆
3 3 any 任何 memory 记忆
PRAGMA temp_store_directory

PRAGMA temp_store_directory;
PRAGMA temp_store_directory = ' directory-name ';

Query or change the value of the sqlite3_temp_directory global variable, which many operating-system interface backends use to determine where to store temporary tables and indices.

When the temp_store_directory setting is changed, all existing temporary tables, indices, triggers, and viewers in the database connection that issued the pragma are immediately deleted. In practice, temp_store_directory should be set immediately after the first database connection for a process is opened. If the temp_store_directory is changed for one database connection while other database connections are open in the same process, then the behavior is undefined and probably undesirable.

Changing the temp_store_directory setting is not threadsafe. Never change the temp_store_directory setting if another thread within the application is running any SQLite interface at the same time. Doing so results in undefined behavior. Changing the temp_store_directory setting writes to the sqlite3_temp_directory global variable and that global variable is not protected by a mutex.

The value directory-name should be enclosed in single quotes. To revert the directory to the default, set the directory-name to an empty string, eg, PRAGMA temp_store_directory = '' . An error is raised if directory-name is not found or is not writable.

The default directory for temporary files depends on the OS. Some OS interfaces may choose to ignore this variable and place temporary files in some other directory different from the directory specified here. In that sense, this pragma is only advisory.

This pragma is deprecated and exists for backwards compatibility only. New applications should avoid using this pragma. Older applications should discontinue use of this pragma at the earliest opportunity. This pragma may be omitted from the build when SQLite is compiled using SQLITE_OMIT_DEPRECATED .

PRAGMA threads

PRAGMA threads;
PRAGMA threads = N ;

Query or change the value of the sqlite3_limit (db, SQLITE_LIMIT_WORKER_THREADS ,...) limit for the current database connection. This limit sets an upper bound on the number of auxiliary threads that a prepared statement is allowed to launch to assist with a query. The default limit is 0 unless it is changed using the SQLITE_DEFAULT_WORKER_THREADS compile-time option. When the limit is zero, that means no auxiliary threads will be launched.

This pragma is a thin wrapper around the sqlite3_limit (db, SQLITE_LIMIT_WORKER_THREADS ,...) interface.

PRAGMA trusted_schema

PRAGMA trusted_schema;
PRAGMA trusted_schema = boolean ;

The trusted_schema setting is a per-connection boolean that determines whether or not SQL functions and virtual tables that have not been security audited are allowed to be run by views, triggers, or in expressions of the schema such as CHECK constraints , DEFAULT clauses , generated columns , expression indexes , and/or partial indexes . This setting can also be controlled using the sqlite3_db_config (db, SQLITE_DBCONFIG_TRUSTED_SCHEMA ,...) C-language interface.

In order to maintain backwards compatibility, this setting is ON by default. There are advantages to turning it off, and most applications will be unaffected if it is turned off. For that reason, all applications are encouraged to switch this setting off on every database connection as soon as that connection is opened.

The -DSQLITE_TRUSTED_SCHEMA=0 compile-time option will cause this setting to default to OFF. PRAGMA user_version


PRAGMA schema. user_version;
PRAGMA schema. user_version = integer ;

The user_version pragma will to get or set the value of the user-version integer at offset 60 in the database header . The user-version is an integer that is available to applications to use however they want. SQLite makes no use of the user-version itself.

See also the application_id pragma and schema_version pragma . PRAGMA vdbe_addoptrace


PRAGMA vdbe_addoptrace = boolean ;

If SQLite has been compiled with the SQLITE_DEBUG compile-time option, then the vdbe_addoptrace pragma can be used to cause a complete VDBE opcodes to be displayed as they are created during code generation. This feature is used for debugging SQLite itself. See the VDBE documentation for more information.

This pragma is intended for use when debugging SQLite itself. It is only available when the SQLITE_DEBUG compile-time option is used.

PRAGMA vdbe_debug

PRAGMA vdbe_debug = boolean ;

If SQLite has been compiled with the SQLITE_DEBUG compile-time option, then the vdbe_debug pragma is a shorthand for three other debug-only pragmas: vdbe_addoptrace, vdbe_listing, and vdbe_trace. This feature is used for debugging SQLite itself. See the VDBE documentation for more information.

This pragma is intended for use when debugging SQLite itself. It is only available when the SQLITE_DEBUG compile-time option is used.

PRAGMA vdbe_listing

PRAGMA vdbe_listing = boolean ;

If SQLite has been compiled with the SQLITE_DEBUG compile-time option, then the vdbe_listing pragma can be used to cause a complete listing of the virtual machine opcodes to appear on standard output as each statement is evaluated. With listing is on, the entire content of a program is printed just prior to beginning execution. The statement executes normally after the listing is printed. This feature is used for debugging SQLite itself. See the VDBE documentation for more information.

This pragma is intended for use when debugging SQLite itself. It is only available when the SQLITE_DEBUG compile-time option is used.

PRAGMA vdbe_trace

PRAGMA vdbe_trace = boolean ;

If SQLite has been compiled with the SQLITE_DEBUG compile-time option, then the vdbe_trace pragma can be used to cause virtual machine opcodes to be printed on standard output as they are evaluated. This feature is used for debugging SQLite. See the VDBE documentation for more information.

This pragma is intended for use when debugging SQLite itself. It is only available when the SQLITE_DEBUG compile-time option is used.

PRAGMA wal_autocheckpoint

PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint= N ;

This pragma queries or sets the write-ahead log auto-checkpoint interval. When the write-ahead log is enabled (via the journal_mode pragma ) a checkpoint will be run automatically whenever the write-ahead log equals or exceeds N pages in length. Setting the auto-checkpoint size to zero or a negative value turns auto-checkpointing off.

This pragma is a wrapper around the sqlite3_wal_autocheckpoint() C interface. All automatic checkpoints are PASSIVE .

Autocheckpointing is enabled by default with an interval of 1000 or SQLITE_DEFAULT_WAL_AUTOCHECKPOINT .

PRAGMA wal_checkpoint

PRAGMA schema. wal_checkpoint;
PRAGMA schema. wal_checkpoint(PASSIVE);
PRAGMA schema. wal_checkpoint(FULL);
PRAGMA schema. wal_checkpoint(RESTART);
PRAGMA schema. wal_checkpoint(TRUNCATE);

If the write-ahead log is enabled (via the journal_mode pragma ), this pragma causes a checkpoint operation to run on database database , or on all attached databases if database is omitted. If write-ahead log mode is disabled, this pragma is a harmless no-op.

Invoking this pragma without an argument is equivalent to calling the sqlite3_wal_checkpoint() C interface.

Invoking this pragma with an argument is equivalent to calling the sqlite3_wal_checkpoint_v2() C interface with a 3rd parameter corresponding to the argument:
PASSIVE
Checkpoint as many frames as possible without waiting for any database readers or writers to finish. Sync the db file if all frames in the log are checkpointed. This mode is the same as calling the sqlite3_wal_checkpoint() C interface. The busy-handler callback is never invoked in this mode.
FULL
This mode blocks (invokes the busy-handler callback ) until there is no database writer and all readers are reading from the most recent database snapshot. It then checkpoints all frames in the log file and syncs the database file. FULL blocks concurrent writers while it is running, but readers can proceed.
RESTART
This mode works the same way as FULL with the addition that after checkpointing the log file it blocks (calls the busy-handler callback ) until all readers are finished with the log file. This ensures that the next client to write to the database file restarts the log file from the beginning. RESTART blocks concurrent writers while it is running, but allowed readers to proceed.
TRUNCATE
This mode works the same way as RESTART with the addition that the WAL file is truncated to zero bytes upon successful completion.

The wal_checkpoint pragma returns a single row with three integer columns. The first column is usually 0 but will be 1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing, for example because another thread or process was actively using the database. In other words, the first column is 0 if the equivalent call to sqlite3_wal_checkpoint_v2() would have returned SQLITE_OK or 1 if the equivalent call would have returned SQLITE_BUSY . The second column is the number of modified pages that have been written to the write-ahead log file. The third column is the number of pages in the write-ahead log file that have been successfully moved back into the database file at the conclusion of the checkpoint. The second and third column are -1 if there is no write-ahead log, for example if this pragma is invoked on a database connection that is not in WAL mode .

PRAGMA writable_schema

PRAGMA writable_schema = boolean ;

When this pragma is on, and the SQLITE_DBCONFIG_DEFENSIVE flag is off, then the sqlite_master table can be changed using ordinary UPDATE , INSERT , and DELETE statements. Warning: misuse of this pragma can easily result in a corrupt database file .


by  ICOPY.SITE