11.2.6 TIMESTAMP 和 DATETIME 的自动初始化和更新
TIMESTAMP和DATETIME列可以自动初始化并更新为当前日期和时间(即当前时间戳)。
对于 table 中的任何TIMESTAMP或DATETIME列,您都可以将当前时间戳记指定为默认值和/或自动更新值:
-
对于未为该列指定值的插入行,将自动初始化的列设置为当前时间戳。
-
当行中任何其他列的值从其当前值更改时,自动更新的列将自动更新为当前时间戳。如果将所有其他列设置为其当前值,则自动更新的列将保持不变。为防止自动更新的列在其他列更改时更新,请将其显式设置为其当前值。要即使其他列未更改也要更新自动更新的列,请将其显式设置为它应具有的值(例如,将其设置为CURRENT_TIMESTAMP)。
此外,如果禁用了explicit_defaults_for_timestamp系统变量,则可以通过为其分配NULL
值来初始化或更新任何TIMESTAMP(但不是DATETIME
)列到当前日期和时间,除非已使用NULL
属性定义了该值以允许NULL
值。
要指定自动属性,请在列定义中使用DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
子句。条款的 Sequences 无关紧要。如果列定义中同时存在这两者,则任何一个都可以首先出现。 CURRENT_TIMESTAMP的任何同义词与CURRENT_TIMESTAMP的含义相同。它们是CURRENT_TIMESTAMP(),NOW(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP和LOCALTIMESTAMP()。
DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
的使用特定于TIMESTAMP和DATETIME。 DEFAULT
子句还可以用于指定常量(非自动)默认值(例如DEFAULT 0
或DEFAULT '2000-01-01 00:00:00'
)。
Note
以下示例使用DEFAULT 0
,这是默认值,它会根据启用了严格 SQL 模式还是NO_ZERO_DATE SQL 模式而产生警告或错误。请注意,TRADITIONAL SQL 模式包括严格模式和NO_ZERO_DATE。参见第 5.1.10 节“服务器 SQL 模式”。
TIMESTAMP或DATETIME列定义可以为默认值和自动更新值指定当前时间戳,可以为一个但另一个不指定,也可以都不指定。不同的列可以具有不同的自动属性组合。以下规则描述了可能性:
- 对于
DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
,该列均具有其默认值的当前时间戳,并会自动更新为当前时间戳。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
- 使用
DEFAULT
子句但不使用ON UPDATE CURRENT_TIMESTAMP
子句,该列具有给定的默认值,并且不会自动更新为当前时间戳。
默认值取决于DEFAULT
子句指定CURRENT_TIMESTAMP
还是常数。使用CURRENT_TIMESTAMP
,默认值为当前时间戳。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP
);
使用常数时,默认值为给定值。在这种情况下,该列根本没有自动属性。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0,
dt DATETIME DEFAULT 0
);
- 使用
ON UPDATE CURRENT_TIMESTAMP
子句和常量DEFAULT
子句,该列将自动更新为当前时间戳,并具有给定的常量默认值。
CREATE TABLE t1 (
ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);
- 使用
ON UPDATE CURRENT_TIMESTAMP
子句但不使用DEFAULT
子句,该列将自动更新为当前时间戳,但没有默认值的当前时间戳。
在这种情况下,默认值取决于类型。除非使用NULL
属性定义,否则TIMESTAMP的默认值为 0,在这种情况下,默认值为NULL
。
CREATE TABLE t1 (
ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0
ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);
除非使用NOT NULL
属性定义,否则DATETIME的默认值为NULL
,在这种情况下,默认值为 0.
CREATE TABLE t1 (
dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL
dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);
除非明确指定了TIMESTAMP和DATETIME列,否则它们没有自动属性,但以下情况除外:如果禁用explicit_defaults_for_timestamp系统变量,则* first * TIMESTAMP列同时具有DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
(如果未明确指定)。要取消对第一个TIMESTAMP列的自动属性,请使用以下策略之一:
-
启用explicit_defaults_for_timestamp系统变量。在这种情况下,指定自动初始化和更新的
DEFAULT CURRENT_TIMESTAMP
和ON UPDATE CURRENT_TIMESTAMP
子句是可用的,但是除非列定义中明确包含,否则它们不会分配给任何TIMESTAMP列。 -
或者,如果禁用了explicit_defaults_for_timestamp,请执行以下任一操作:
-
使用
DEFAULT
子句定义该列,该子句指定一个恒定的默认值。- 指定
NULL
属性。这也会导致该列允许NULL
值,这意味着您无法通过将列设置为NULL
来分配当前时间戳。分配NULL
会将列设置为NULL
,而不是当前时间戳。要分配当前时间戳,请将列设置为CURRENT_TIMESTAMP或诸如NOW()的同义词。
- 指定
请考虑以下 table 定义:
CREATE TABLE t1 (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t2 (
ts1 TIMESTAMP NULL,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t3 (
ts1 TIMESTAMP NULL DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
这些 table 具有以下属性:
-
在每个 table 定义中,第一TIMESTAMP列没有自动初始化或更新。
-
这些 table 在
ts1
列处理NULL
值的方式上有所不同。对于t1
,ts1
是NOT NULL
,为其分配值NULL
会将其设置为当前时间戳。对于t2
和t3
,ts1
允许NULL
并为其分配值NULL
将其设置为NULL
。 -
t2
和t3
的默认值ts1
有所不同。对于t2
,将ts1
定义为允许NULL
,因此在没有显式DEFAULT
子句的情况下,默认值也是NULL
。对于t3
,ts1
允许NULL
但显式默认值为 0.
如果TIMESTAMP或DATETIME列定义在任何地方都包含显式的小数秒精度值,则在整个列定义中必须使用相同的值。这是允许的:
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
这是不允许的:
CREATE TABLE t1 (
ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);
TIMESTAMP 初始化和 NULL 属性
如果禁用了explicit_defaults_for_timestamp系统变量,则默认情况下TIMESTAMP列为NOT NULL
,不能包含NULL
值,并且分配NULL
会分配当前时间戳。要允许TIMESTAMP列包含NULL
,请使用NULL
属性显式声明它。在这种情况下,默认值也将变为NULL
,除非被指定不同默认值的DEFAULT
子句覆盖。 DEFAULT NULL
可用于将NULL
明确指定为默认值。 (对于未使用NULL
属性声明的TIMESTAMP列,DEFAULT NULL
无效。)如果TIMESTAMP列允许NULL
值,则分配NULL
会将其设置为NULL
,而不是当前时间戳。
下 table 包含几个TIMESTAMP列,它们允许NULL
个值:
CREATE TABLE t
(
ts1 TIMESTAMP NULL DEFAULT NULL,
ts2 TIMESTAMP NULL DEFAULT 0,
ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
除非有以下情况之一,否则允许NULL
值的TIMESTAMP列在插入时不会*采用当前时间戳记:
-
其默认值定义为CURRENT_TIMESTAMP,并且未为该列指定任何值
-
CURRENT_TIMESTAMP或其任何同义词(例如NOW())已明确插入到列中
换句话说,定义为允许NULL
值的TIMESTAMP列仅在其定义包含DEFAULT CURRENT_TIMESTAMP
时自动初始化:
CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
如果TIMESTAMP列允许NULL
值,但其定义不包含DEFAULT CURRENT_TIMESTAMP
,则必须显式插入与当前日期和时间相对应的值。假设 tablet1
和t2
具有以下定义:
CREATE TABLE t1 (ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00');
CREATE TABLE t2 (ts TIMESTAMP NULL DEFAULT NULL);
要将插入时任一 table 中的TIMESTAMP列设置为当前时间戳,请显式为其分配该值。例如:
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);
INSERT INTO t1 VALUES (NOW());
如果启用了explicit_defaults_for_timestamp系统变量,则只有使用NULL
属性声明时,TIMESTAMP列才允许NULL
值。同样,TIMESTAMP列不允许分配NULL
来分配当前时间戳,无论使用NULL
还是NOT NULL
属性声明。要分配当前时间戳,请将列设置为CURRENT_TIMESTAMP或诸如NOW()之类的同义词。