将sqlite表ALTER为添加具有默认值的时间戳列

Question

在sqlite中不可能使用ALTER table ADD column foo timestamp default CURRENT_TIMESTAMP的方式添加列，但是是否有巧妙的解决方法？

- user3791372

---

嗯，那个问题的答案是“做不到” - 我在我的问题中已经表明了我知道这一点。我正在寻求巧妙的解决方法。 - user3791372

Answer 1

当向表添加列时，SQLite实际上并不修改表内容，因此默认值必须是一个不需要读取表的值。

为了解决这个问题，你可以使用触发器来代替默认值：

1. 添加不带默认值的列：

ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL;

使用触发器设置默认值：

ADD TRIGGER MyTable_foo_default
AFTER INSERT ON MyTable
FOR EACH ROW
WHEN NEW.foo IS NULL
BEGIN
    UPDATE MyTable
    SET foo = CURRENT_TIMESTAMP
    WHERE rowid = NEW.rowid;
END;

或者，先修改表格内容，使所有行都有值，然后再设置默认值：

1. 添加不带默认值的列：

ALTER TABLE MyTable ADD COLUMN foo timestamp DEFAULT NULL /* replace me */;

将所有行中的列值设置为something（实际值并不重要，重要的是所有行现在都有了新列）：

UPDATE MyTable SET foo = CURRENT_TIMESTAMP;

更改默认值（文档：PRAGMA writable_schema，sqlite_master）：

PRAGMA writable_schema = on;

UPDATE sqlite_master
SET sql = replace(sql, 'DEFAULT NULL /* replace me */',
                       'DEFAULT CURRENT_TIMESTAMP')
WHERE type = 'table'
  AND name = 'MyTable';

PRAGMA writable_schema = off;

重新打开数据库（否则，SQLite将不知道新的默认值）。

- CL.

---

第二个解决方案感觉更好，而且感觉像是一个非常聪明的变通方法！不错！ - user3791372

第一种解决方案对我没用，但第二种解决方案很有效。 - Julfikar

@AmbroiseRabier 对我来说可以。这个表的“sql”列中显示了什么？它没有“DEFAULT NULL /* replace me */”吗？ - CL.

@CL。我使用了冗长的重新创建表格方法。在“sql”列中，原始的“CREATE TABLE”语句没有包含“foo timestamp DEFAULT NULL /* replace me */;”任何通过“ALTER TABLE”添加的字段都不见了。这似乎对应于我上面链接的文档。 - Ambroise Rabier

@AmbroiseRabier 这意味着ALTER TABLE根本没有起作用。我猜您是从另一个连接查看了sqlite_master，而原始连接还有一个未提交的事务。 - CL.