The Schema Table


每个 SQLite 数据库都包含一个“模式表”,用于存储该数据库的模式。数据库的模式是对数据库中包含的所有其他表、索引、触发器和视图的描述。架构表如下所示:

CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text

sqlite_schema 表包含模式中每个表、索引、视图和触发器(统称为“对象”)的一行,除了 sqlite_schema 表本身没有条目。有关SQLite 如何在内部使用 sqlite_schema 表的更多信息, 请参阅 文件格式文档的模式存储部分。



  1. sqlite_master
  2. sqlite_temp_schema
  3. sqlite_temp_master

备选方案 (2) 和 (3) 仅适用于与每个数据库连接关联的 TEMP 数据库,但备选方案 (1) 适用于任何地方。由于历史原因,来自sqlite3_set_authorizer() 接口的回调总是使用名称 (1) 或 (3) 引用模式表。




The sqlite_schema.type column will be one of the following text strings: 'table', 'index', 'view', or 'trigger' according to the type of object defined. The 'table' string is used for both ordinary and virtual tables.


The column will hold the name of the object. (UNIQUE and PRIMARY KEY constraints on tables cause SQLite to create internal indexes with names of the form "sqlite_autoindex_TABLE_N" where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen in the table definition. In a WITHOUT ROWID table, there is no sqlite_schema entry for the PRIMARY KEY, but the "sqlite_autoindex_TABLE_N" name is set aside for the PRIMARY KEY as if the sqlite_schema entry did exist. This will affect the numbering of subsequent UNIQUE constraints. The "sqlite_autoindex_TABLE_N" name is never allocated for an INTEGER PRIMARY KEY, either in rowid tables or WITHOUT ROWID tables.


The sqlite_schema.tbl_name column holds the name of a table or view that the object is associated with. For a table or view, the tbl_name column is a copy of the name column. For an index, the tbl_name is the name of the table that is indexed. For a trigger, the tbl_name column stores the name of the table or view that causes the trigger to fire.


The sqlite_schema.rootpage column stores the page number of the root b-tree page for tables and indexes. For rows that define views, triggers, and virtual tables, the rootpage column is 0 or NULL.


The sqlite_schema.sql column stores SQL text that describes the object. This SQL text is a CREATE TABLE, CREATE VIRTUAL TABLE, CREATE INDEX, CREATE VIEW, or CREATE TRIGGER statement that if evaluated against the database file when it is the main database of a database connection would recreate the object. The text is usually a copy of the original statement used to create the object but with normalizations applied so that the text conforms to the following rules:

  • The CREATE, TABLE, VIEW, TRIGGER, and INDEX keywords at the beginning of the statement are converted to all upper case letters.
  • The TEMP or TEMPORARY keyword is removed if it occurs after the initial CREATE keyword.
  • Any database name qualifier that occurs prior to the name of the object being created is removed.
  • Leading spaces are removed.
  • All spaces following the first two keywords are converted into a single space.

The text in the sqlite_schema.sql column is a copy of the original CREATE statement text that created the object, except normalized as described above and as modified by subsequent ALTER TABLE statements. The sqlite_schema.sql is NULL for the internal indexes that are automatically created by UNIQUE or PRIMARY KEY constraints.

4. Schema表的创建和修改

SQLite 在创建数据库时创建模式表,并在 SQLite 用户提交 DDL 语句以供执行时修改其内容。用户一般情况下无需修改,修改后需承担数据库损坏的风险。