是否可以定义一个不为空且没有默认值且在更新时没有特殊行为的时间戳列?

Is it possible to define a timestamp column that is not null and has no default and no special behavior on update?(是否可以定义一个不为空且没有默认值且在更新时没有特殊行为的时间戳列?)

问题描述

当我创建一个带有时间戳列的表时，该列在更新 CURRENT_TIMESTAMP 时神奇地定义为 NOT NULL DEFAULT CURRENT_TIMESTAMP.忽略到目前为止这有多奇怪，我想将其更改为没有默认值并且没有特殊的更新"行为.

When I create a table with a timestamp column, that column is magically defined as NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP. Ignoring how weird this is so far, I would like to change it to have no default and no special "on update" behavior.

我发现如果我将列更改为 NULL，默认值会神奇地设置为 NULL，on update"会神奇地消失.这很好，但是我希望该列不为空.当我改回来时，默认值和更新时"(设置为 CURRENT_TIMESTAMP)都会神奇地重新出现.

I found that if I change the column to be NULL, the default is magically set to NULL and the "on update" magically disappears. This is great, however I would like the column to be NOT NULL. When I change it back, both the default and "on update" (set to CURRENT_TIMESTAMP) magically reappear.

我知道我可以使用 datetime 代替时间戳，但我对时间戳很感兴趣，因为它是时区感知的(似乎就像 Postgres 中的带时区的时间戳").

I know I could use datetime instead of timestamp, but I'm interested in timestamp because it is timezone-aware (seems to be like "timestamp with time zone" in Postgres).

推荐答案

时间戳列是一种特殊情况.请参阅此处:默认情况下，TIMESTAMP 列不为空，不能包含 NULL 值，分配 NULL 会分配当前时间戳.

Timestamp columns are a special case. See here: By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

有关详细信息，请阅读 数据类型默认值价值观.

For more detailed information read up on Data Type Default Values.

具体来说，这种情况适用于不以严格模式运行时.如果在严格模式下运行，插入 NULL 会引发错误.

Specifically that situation applies when not running in strict mode. If running in strict mode, inserting a NULL will throw an error.

这应该照顾它:

ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT;

如果这不起作用，这样做应该会给您留下默认值(很容易被覆盖)，但删除 ON UPDATE:

If that doesn't work, doing this is supposed to leave you with the default (easily overwritten) but remove the ON UPDATE:

ALTER TABLE tableName CHANGE columnName columnName NOT NULL DEFAULT CURRENT_TIMESTAMP;

注意重复的列名.

这篇关于是否可以定义一个不为空且没有默认值且在更新时没有特殊行为的时间戳列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！