如何在数据库中添加默认值的列

Question

我有一个数据库，想要添加一列。这一列应该是时间戳类型的信息，我希望在完成后每一行都具有相同的时间戳（当前时间）。

目前我尝试了：

cursor.execute('''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT ?''', (datetime.datetime.utcnow(),))

这导致出现了sqlite3.OperationalError: near "?": syntax error的错误。

于是我尝试了以下操作：

cursor.execute(f'''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT {datetime.datetime.utcnow()}''')

导致 sqlite3.OperationalError: near "-": syntax error 错误。

另外，执行以下操作

cursor.execute(f'''ALTER TABLE my_table ADD COLUMN time timestamp DEFAULT CURRENT_TIMESTAMP''')

出现了sqlite3.OperationalError: Cannot add a column with non-constant default错误。

我如何添加新列并在该列中设置值？（可以通过DEFAULT或其他机制实现。）

- Pro Q

---

这个回答解决了你的问题吗？sqlite数据库默认时间值'now' - sticky bit

Answer 1

SQLite不允许添加一个具有非常量值的新列。因此，这个：

alter table my_table add column my_time timestamp default current_timestamp;

... 生成错误：

Cannot add a column with non-constant default

一个简单的选项是重新创建表格。假设您有一个名为id的单列，它会像这样：

create table my_table_new(
    id int primary key, 
     my_time timestamp default current_timestamp
);

insert into my_table_new(id) select id from my_table;
drop table my_table;  -- back it up first!
alter table my_table_new rename to my_table;

- GMB

---

但是，如果删除，数据将会丢失。 - Eric

@Eric 第二个查询将所有数据从旧表复制到新数据，因此不会丢失任何数据。 - FWDekker

请注意，此过程可能会触发外键约束和触发器！如果您想要一种可靠的方法，请阅读 https://www.sqlite.org/lang_altertable.html 中的“7.进行其他类型的表模式更改”部分。 - FWDekker

@FWDekker 精确链接到具有锚点的命名部分：https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes - undefined

Answer 2

您可以先添加新列，然后将表中每个现有行更新为所需的值：

ALTER TABLE my_table ADD COLUMN time;
UPDATE my_table SET time = CURRENT_TIMESTAMP;

- Shawn

---

我从这里学到了“时间戳”，但显然是错误的，我应该使用TEXT值来满足我的需求。 - Pro Q

嗯... 当我将它更改为 TEXT 时，我的格式不再像以前那样工作 - 我得研究一下这个问题。很奇怪的是，一个未记录的类型可以工作，但记录的类型却不能。 - Pro Q

我弄明白了 - SQLite确实有一个"timestamp"类型。它用于将Python的datetime.datetime转换器。 - Pro Q

@ProQ 小更正：SQLite 没有“timestamp”类型。“timestamp”是一个未知的类型，因此在 SQLite 中被定义为“numeric”。与“timestamp”相同的结果，您也可以写成“kmsnfrwtx”。此外，那些不是类型，而是类型亲和性。请查看：https://www.sqlite.org/datatype3.html#determination_of_column_affinity - Toxiro

这仍然没有解决将值设置为列默认值的问题。 - Taosif7