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