修改表

、概述

改变表 stmt:

ALTER TABLE schema-name . table-name RENAME TO new-table-name RENAME COLUMN column-name TO new-column-name ADD COLUMN column-def DROP COLUMN column-name

列定义:

SQLite 支持 ALTER TABLE 的有限子集。SQLite 中的 ALTER TABLE 命令允许对现有表进行这些更改:它可以重命名;可以重命名列;可以向其中添加一列;或者可以从中删除一列。

2.更改表重命名

RENAME TO 语法将table-name的名称更改 为new-table-name此命令不能用于在附加数据库之间移动表,只能用于重命名同一数据库中的表。如果被重命名的表有触发器或索引,那么它们在重命名后仍然附加到表。

兼容性说明: 在版本 3.25.0 (2018-09-15) 和 3.26.0 (2018-12-01) 中增强了重命名表时 ALTER TABLE 的行为,以便将重命名操作转发到触发器和视图中引用重命名的表。这被认为是一种改进。依赖于旧的(并且可以说是错误的)行为的应用程序可以使用 PRAGMA legacy_alter_table=ON语句或 sqlite3_db_config()接口上的SQLITE_DBCONFIG_LEGACY_ALTER_TABLE配置参数来使 ALTER TABLE RENAME 的行为与 3.25.0 之前的版本一样。

从版本 3.25.0 (2018-09-15) 开始,触发器主体和视图定义中对表的引用也被重命名。

在版本 3.26.0 (2018-12-01) 之前,仅当PRAGMA foreign_keys=ON时,或者换句话说,如果 强制执行外键约束,才会编辑对重命名表的外键引用 使用 PRAGMA foreign_keys=OFF ,当外键引用的表(“父表”)被重命名时,外键约束不会改变。从版本 3.26.0 开始,FOREIGN KEY 约束总是在重命名表时转换,除非使用了 PRAGMA legacy_alter_table=ON设置。下表总结了差异:

PRAGMA foreign_keys PRAGMA legacy_alter_table Parent Table references are updated SQLite version
OffOffNo< 3.26.0
OffOffYes>= 3.26.0
OnOffYesall
OffOnNoall
OnOnYesall

3.更改表重命名列

RENAME COLUMN TO 语法将 表表名的列名更改 为新列名列名在表定义本身以及引用该列的所有索引、触发器和视图中都会更改。如果列名更改会导致触发器或视图中出现语义歧义,则 RENAME COLUMN 将失败并出现错误,并且不会应用任何更改。

4.更改表添加列

ADD COLUMN 语法用于向现有表添加新列。新列总是附加到现有列列表的末尾。column-def规则定义新列的特征。新列可以采用CREATE TABLE 语句中允许的任何形式,但有以下限制:

  • 该列可能没有 PRIMARY KEY 或 UNIQUE 约束。
  • 该列可能没有默认值 CURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMP 或括号中的表达式。
  • 如果指定了 NOT NULL 约束,则该列必须具有 NULL 以外的默认值。
  • 如果启用外键约束并添加带有REFERENCES 子句 的列,则该列必须具有默认值 NULL。
  • 尽管允许使用 VIRTUAL 列,但 该列可能不会GENERATED ALWAYS ... STORED 。

当在生成的列上添加一个带有CHECK 约束或 NOT NULL 约束的时,添加的约束将针对表中所有预先存在的行进行测试,如果任何约束失败,则 ADD COLUMN 将失败。从 SQLite 版本 3.37.0 (2021-11-27) 开始,针对预先存在的行测试添加的约束是一项新的增强功能。

ALTER TABLE 命令通过修改存储在sqlite_schema 表中的模式的 SQL 文本来工作。没有约束的重命名或列添加不会对表内容进行任何更改。因此,此类 ALTER TABLE 命令的执行时间与表中的数据量无关,并且此类命令在具有 1000 万行的表上的运行速度与在具有 1 行的表上的运行速度一样快。添加具有 CHECK 约束的新列,或添加具有 NOT NULL 约束的生成列,或删除列时,必须读取表中的所有现有数据(以测试针对现有行的新约束)或写入(以删除已删除的列). 在这些情况下,ALTER TABLE 命令花费的时间与表中被更改的内容量成正比。

在数据库上运行 ADD COLUMN 后,SQLite 3.1.3 (2005-02-20) 及更早版本将无法读取该数据库。

5.改变表删除列

DROP COLUMN 语法用于从表中删除现有列。DROP COLUMN 命令从表中删除命名列,并重写其内容以清除与该列关联的数据。DROP COLUMN 命令仅在该列未被模式的任何其他部分引用并且不是 PRIMARY KEY 且没有 UNIQUE 约束时才有效。DROP COLUMN 命令失败的可能原因包括:

  • 该列是 PRIMARY KEY 或其中的一部分。
  • 该列具有 UNIQUE 约束。
  • 该列已编入索引。
  • 该列在部分索引的 WHERE 子句中命名。
  • 该列在与要删除的列无关 的表或列CHECK 约束中命名。
  • 该列用于外键约束
  • 该列用于生成列的表达式。
  • 该列出现在触发器或视图中。

5.1. 这个怎么运作

SQLite 将模式作为纯文本存储在sqlite_schema 表中DROP COLUMN 命令(以及 ALTER TABLE 的所有其他变体)修改该文本,然后尝试重新分析整个模式。仅当模式在修改文本后仍然有效时,该命令才会成功。在 DROP COLUMN 命令的情况下,唯一修改的文本是列定义从 CREATE TABLE 语句中删除。如果在修改 CREATE TABLE 语句后阻止模式在模式的其他部分中进行解析,则 DROP COLUMN 命令将失败。

6.使用 PRAGMA writable_schema=ON 禁用错误检查

如果 ALTER TABLE 在sqlite_schema 表中遇到任何未解析的条目,通常会失败并且不会进行任何更改。例如,如果有一个格式错误的 VIEW 或 TRIGGER 关联了一些名为“tbl1”的表,那么将“tbl1”重命名为“tbl1neo”的尝试将失败,因为无法解析关联的视图和触发器。

从 SQLite 3.38.0 (2022-02-22) 开始,可以通过设置“ PRAGMA writable_schema=ON; ”来禁用此错误检查。当模式可写时,ALTER TABLE 会默默地忽略 sqlite_schema 表中未解析的任何行。

7.进行其他类型的表架构更改

SQLite 直接支持的唯一模式更改命令是上面显示的“重命名表”、“重命名列”、“添加列”、“删除列”命令。但是,应用程序可以使用简单的操作序列对表的格式进行其他任意更改。对某表X的schema设计进行任意改动的步骤如下:

  1. 如果启用了外键约束,请使用PRAGMA foreign_keys=OFF禁用它们。

  2. 开始交易。

  3. 记住与表 X 关联的所有索引、触发器和视图的格式。下面的第 8 步将需要此信息。一种方法是运行如下查询:SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'。

  4. 使用CREATE TABLE构建一个新表“new_X”,它采用表 X 的所需修订格式。当然,请确保名称“new_X”不与任何现有表名冲突。

  5. 使用如下语句将内容从 X 传输到 new_X:INSERT INTO new_X SELECT ... FROM X。

  6. 删除旧表 X: DROP TABLE X。

  7. 使用以下命令将 new_X 的名称更改为 X:ALTER TABLE new_X RENAME TO X。

  8. 使用CREATE INDEXCREATE TRIGGERCREATE VIEW 重建与表 X 关联的索引、触发器和视图。也许使用从上面的步骤 3 中保存的触发器、索引和视图的旧格式作为指南,根据需要进行适当的更改改动。

  9. 如果任何视图以受架构更改影响的方式引用表 X,则使用DROP VIEW删除这些视图,并使用CREATE VIEW使用任何必要的更改重新创建它们以适应架构更改

  10. 如果最初启用了外键约束,则运行PRAGMA foreign_key_check以验证模式更改没有破坏任何外键约束。

  11. 提交在步骤 2 中开始的事务。

  12. 如果最初启用了外键约束,现在重新启用它们。

注意:注意 严格按照上述步骤操作。下面的框总结了修改表定义的两个过程。乍一看,他们似乎都在完成同一件事。但是,右侧的过程并不总是有效,尤其是在版本 3.25.0 和 3.26.0 添加了增强的重命名表功能的情况下。在右侧的过程中,将表初始重命名为临时名称可能会破坏触发器、视图和外键约束中对该表的引用。左边的安全过程使用新的临时名称构造修改后的表定义,然后将表重命名为其最终名称,这不会中断链接。

  1. Create new table
  2. Copy data
  3. Drop old table
  4. Rename new into old
  1. Rename old table
  2. Create new table
  3. Copy data
  4. Drop old table

Correct

Incorrect

即使模式更改导致存储在表中的信息发生更改,上面的 12 步通用 ALTER TABLE 过程仍然有效。因此,上面完整的 12 步过程适用于删除列、更改列的顺序、添加或删除 UNIQUE 约束或 PRIMARY KEY、添加 CHECK 或 FOREIGN KEY 或 NOT NULL 约束,或更改列的数据类型,例如例子。但是,对于一些不会以任何方式影响磁盘内容的更改,可以选择使用更简单、更快速的过程。以下更简单的过程适用于删除 CHECK 或 FOREIGN KEY 或 NOT NULL 约束,或者添加、删除或更改列的默认值。

  1. 开始交易。

  2. 运行PRAGMA schema_version以确定当前模式版本号。下面的第 6 步将需要此编号。

  3. 使用PRAGMA writable_schema=ON激活模式编辑

  4. 运行UPDATE语句以更改sqlite_schema 表中表 X 的定义: UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';

    注意: 如果更改包含语法错误,那么像这样更改 sqlite_schema 表将导致数据库损坏且不可读。建议在将 UPDATE 语句用于包含重要数据的数据库之前,先在单独的空白数据库上对其进行仔细测试。

  5. 如果对表 X 的更改也影响其他表或索引或触发器是模式中的视图,则运行UPDATE语句来修改那些其他表的索引和视图。例如,如果列的名称发生更改,则必须修改引用该列的所有 FOREIGN KEY 约束、触发器、索引和视图。

    注意: 再一次,如果更改包含错误,像这样对 sqlite_schema 表进行更改将导致数据库损坏且不可读。在包含重要数据的数据库上使用它之前,请在单独的测试数据库上仔细测试整个过程和/或在运行此过程之前制作重要数据库的备份副本。

  6. 使用 PRAGMA schema_version=X增加架构版本号,其中 X 比上面步骤 2 中找到的旧架构版本号多 1。

  7. 使用PRAGMA writable_schema=OFF禁用模式编辑

  8. (可选)运行PRAGMA integrity_check以验证模式更改没有损坏数据库。

  9. 提交在上面的第 1 步开始的事务。

如果 SQLite 的某个未来版本添加了新的 ALTER TABLE 功能,这些功能很可能会使用上面概述的两个过程之一。

8.为什么 ALTER TABLE 对 SQLite 来说是个问题

大多数 SQL 数据库引擎将已解析的模式存储到各种系统表中。在那些数据库引擎上,ALTER TABLE 只需对相应的系统表进行修改。

SQLite 的不同之处在于它将模式存储在sqlite_schema表中,作为定义模式的 CREATE 语句的原始文本。因此 ALTER TABLE 需要修改 CREATE 语句的文本。这样做对于某些“创造性”模式设计来说可能很棘手。

将模式存储为文本的 SQLite 方法对于嵌入式关系数据库具有优势。一方面,这意味着模式在数据库文件中占用的空间更少。这一点很重要,因为常见的 SQLite 使用模式是拥有许多小的、独立的数据库文件,而不是将所有内容都放在一个大的全局数据库文件中,这是客户端/服务器数据库引擎的常用方法。由于模式在每个单独的数据库文件中都是重复的,因此保持模式表示紧凑很重要。

将模式存储为文本而不是解析后的表也为实现提供了灵活性。由于每次打开数据库时都会重新生成模式的内部解析,因此模式的内部表示可能会从一个版本更改为下一个版本。这很重要,因为有时新功能需要增强内部模式表示。如果模式表示在数据库文件中公开,则更改内部模式表示会困难得多。因此,换句话说,将模式存储为文本有助于保持向后兼容性,并有助于确保较旧的数据库文件可以由较新版本的 SQLite 读取和写入。

将模式存储为文本还使 SQLite 数据库文件格式更易于定义、记录和理解。这有助于使 SQLite 数据库文件成为 长期归档数据 的推荐存储格式。

将模式存储为文本的缺点是它会使模式难以修改。出于这个原因,SQLite 中的 ALTER TABLE 支持传统上落后于其他 SQL 数据库引擎,这些引擎将其模式存储为更易于修改的已解析系统表。