第11章: 列类型 / 11.3. 日期和时间类型 / 11.3.1. DATETIME、DATE和TIMESTAMP类型
11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性

DATETIMEDATETIMESTAMP类型是相关的。该节描述了它们的特征,它们的相似点和不同点。

当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00''9999-12-31 23:59:59'(支持”表示尽管先前的值可能工作,但没有保证)

当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL'YYYY-MM-DD'格式检索和显示DATE值。支持的范围是'1000-01-01''9999-12-31'

TIMESTAMP列类型的属性不固定,取决于MySQL版本和服务器运行的SQL模式。这些属性将在本节后面描述。

可以使用任何常见格式指定DATETIMEDATETIMESTAMP值:

·         'YYYY-MM-DD HH:MM:SS''YY-MM-DD HH:MM:SS'格式的字符串。允许“不严格”语法:任何标点符都可以用做日期部分或时间部分之间的间割符。例如,'98-12-31 11:30:45''98.12.31 11+30+45''98/12/31 11*30*45''98@12@31 11^30^45'是等价的。

·         'YYYY-MM-DD''YY-MM-DD'格式的字符串。这里也允许使用“不严格的”语法。例如,'98-12-31''98.12.31''98/12/31''98@12@31'是等价的。

·         'YYYYMMDDHHMMSS''YYMMDDHHMMSS'格式的没有间割符的字符串,假定字符串对于日期类型是有意义的。例如,'19970523091528''970523091528'被解释为'1997-05-23 09:15:28',但'971122129015'是不合法的(它有一个没有意义的分钟部分),将变为'0000-00-00 00:00:00'

·         'YYYYMMDD''YYMMDD'格式的没有间割符的字符串,假定字符串对于日期类型是有意义的。例如,'19970523''970523'被解释为 '1997-05-23',但'971332'是不合法的(它有一个没有意义的月和日部分),将变为'0000-00-00'

·         YYYYMMDDHHMMSSYYMMDDHHMMSS格式的数字,假定数字对于日期类型是有意义的。例如,19830905132800830905132800被解释为 '1983-09-05 13:28:00'

·         YYYYMMDDYYMMDD格式的数字,假定数字对于日期类型是有意义的。例如,19830905830905被解释为'1983-09-05'

·         函数返回的结果,其值适合DATETIMEDATE或者TIMESTAMP上下文,例如NOW()CURRENT_DATE

无效DATETIMEDATE或者TIMESTAMP值被转换为相应类型的“”值('0000-00-00 00:00:00''0000-00-00'或者00000000000000)

对于包括日期部分间割符的字符串值,如果日和月的值小于10,不需要指定两位数。'1979-6-9''1979-06-09'是相同的。同样,对于包括时间部分间割符的字符串值,如果时、分和秒的值小于10,不需要指定两位数。'1979-10-30 1:2:3''1979-10-30 01:02:03'相同。

数字值应为6812或者14位长。如果一个数值是814位长,则假定为YYYYMMDDYYYYMMDDHHMMSS格式,前4位数表示年。如果数字 是612位长,则假定为YYMMDDYYMMDDHHMMSS格式,前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

指定为非限定符字符串的值使用给定的长度进行解释。如果字符串为814字符长,前4位数表示年。否则,前2位数表示年。从左向右解释字符串内出现的各部分,以发现年、月、日、小时、分和秒值。这说明不应使用少于6字符的字符串。例如,如果你指定'9903',认为它表示19993月,MySQL将在你的表内插入一个“”日期值。这是因为年和月值是9903,但日部分完全丢失,因此该值不是一个合法的日期。但是,可以明显指定一个零值来代表缺少的月或日部分。例如,可以使用'990300'来插入值'1999-03-00'

在一定程度上,可以将一个日期类型的值分配给一个不同的日期类型。但是,值可能会更改或丢失一些信息:

·         如果你为一个DATETIMETIMESTAMP对象分配一个DATE值,结果值的时间部分被设置为'00:00:00',因为DATE值未包含时间信息。

·         如果你为一个DATE对象分配一个DATETIMETIMESTAMP值,结果值的时间部分被删除,因为DATE值未包含时间信息。

·         记住尽管可以使用相同的格式指定DATETIMEDATETIMESTAMP值,不同类型的值的范围却不同。例如,TIMESTAMP值不能早于1970或晚于2037。这说明一个日期,例如'1968-01-01',虽然对于DATETIMEDATE值是有效的,但对于TIMESTAMP值却无效,如果分配给这样一个对象将被转换为0

当指定日期值时请注意某些缺陷:

·         指定为字符串的值允许的非严格格式可能会欺骗。例如,值'10:11:12'由于‘:’间割符看上去可能象时间值,但如果用于日期上下文值则被解释为年'2010-11-12'。值'10:45:15'被转换为'0000-00-00'因为'45'不是合法月。

·         在非严格模式,MySQL服务器只对日期的合法性进行基本检查:年、月和日的范围分别是1000999900120031。任何包含超出这些范围的部分的日期被转换成'0000-00-00'。请注意仍然允许你保存非法日期,例如'2002-04-31'。要想确保不使用严格模式时日期有效,应检查应用程序。

在严格模式,非法日期不被接受,并且不转换。

详细信息参见5.3.2节,“SQL服务器模式”

·         包含两位年值的日期会令人模糊,因为世纪不知道。MySQL使用以下规则解释两位年值:

o        00-69范围的年值转换为2000-2069

o        70-99范围的年值转换为1970-1999

11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性

注释:旧版本的MySQL(4.1之前)TIMESTAMP列类型的属性在许多方面于本节所描述的大大不同。如果你需要对旧的TIMESTAMP数据进行转化以便在MySQL 5.1中工作,详情请参见MySQL 4.1 参考手册

TIMESTAMP列的显示格式与DATETIME列相同。换句话说,显示宽度固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS

MySQL服务器也可以以MAXDB模式运行。当服务器以该模式运行时,TIMESTAMPDATETIME相等。也就是说,如果创建表时服务器以MAXDB模式运行,TIMESTAMP列创建为DATETIME列。结果是,该列使用DATETIME显示格式,有相同的值范围,并且没有自动对当前的日期和时间进行初始化或更新。

要想启用MAXDB模式,在启动服务器时使用--sql-mode=MAXDB服务器选项或在运行时通过设置全局sql_mode变量将SQL服务器模式设置为MAXDB

mysql> SET GLOBAL sql_mode=MAXDB

客户端可以按照下面方法让服务器为它的连接以MAXDB模式运行:

mysql> SET SESSION sql_mode=MAXDB;

 

MySQL不接受在日或月列包括一个零或包含非法日期值的时间戳值。该规则的唯一例外是特殊值'0000-00-00 00:00:00'

你可以非常灵便地确定什么时候初始化和更新TIMESTAMP和对哪些列进行初始化和更新:

·         你可以将当前的时间戳指定为默认值和自动更新的值。但只能选择一个,或者两者都不选。(不可能一个列选择一个行为而另一个列选择另一个行为)

·         你可以指定哪个TIMESTAMP列自动初始化或更新为当前的日期和时间。不再需要为第1TIMESTAMP列。

请注意下面讨论所信息只适用于创建时未启用MAXDB模式的表的TIMESTAMP列。(如上所述,MAXDB模式使列创建为DATETIME列)控制TIMESTAMP列的初始化和更新的规则如下所示:

·         如果一个表内的第1TIMESTAMP列指定为一个DEFAULT值,则不能忽略。 默认值可以为CURRENT_TIMESTAMP或常量日期和时间值。

·         DEFAULT NULL与第1TIMESTAMP 列的DEFAULT CURRENT_TIMESTAMP相同。对于其它TIMESTAMP列,DEFAULT NULL被视为DEFAULT 0

·         表内的任何一个TIMESTAMP列可以设置为自动初始化为当前时间戳和/或更新。

·         CREATE TABLE语句中,可以用下面的任何一种方式声明第1TIMESTAMP列:

o        DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP子句,列为默认值使用当前的时间戳,并且自动更新。

o        不使用DEFAULTON UPDATE子句,与DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP相同。

o        DEFAULT CURRENT_TIMESTAMP子句不用ON UPDATE子句,列为默认值使用当前的时间戳但是不自动更新。

o        不用DEFAULT子句但用ON UPDATE CURRENT_TIMESTAMP子句,列有默认值0并自动更新。

o        用常量DEFAULT值,列有给出的 默认值。如果列有一个ON UPDATE CURRENT_TIMESTAMP子句,它自动更新,否则不。

换句话说,你可以为初始值和自动更新的值使用当前的时间戳,或者其中一个使用,或者两个皆不使用。(例如,你可以指定ON UPDATE来启用自动更新而不让列自动初始化)

·         DEFAULTON UPDATE子句中可以使用CURRENT_TIMESTAMPCURRENT_TIMESTAMP()或者NOW()。它们均具有相同的效果。

两个属性的顺序并不重要。如果一个TIMESTAMP列同时指定了DEFAULTON UPDATE,任何一个可以在另一个的前面。

例子,下面这些语句是等效的:

CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                             DEFAULT CURRENT_TIMESTAMP);

·         要为TIMESTAMP列而不是第1列指定自动默认或更新,必须通过将第1TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如,DEFAULT 0DEFAULT'2003-01-01 00:00:00')。然后,对于其它TIMESTAMP列,规则与第1TIMESTAMP列相同,例外情况是不能忽略DEFAULTON UPDATE子句。如果这样做,则不会自动进行初始化或更新。

例如:下面这些语句是等效的:

CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

可以对每个连接设置当前的时区,相关描述参见5.10.8节,“MySQL服务器时区支持”TIMESTAMP值以UTC格式保存,存储时对当前的时区进行转换,检索时再转换回当前的时区。只要时区设定值为常量,便可以得到保存时的值。如果保存一个TIMESTAMP值,应更改时区然后检索该值,它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。

可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如:

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

如果未指定NULL属性,将列设置为NULL设置则会将它设置为当前的时间戳。请注意允许NULL值的TIMESTAMP列不会采用当前的时间戳,除非要么其 默认值定义为CURRENT_TIMESTAMP,或者NOW()CURRENT_TIMESTAMP被插入到该列内。换句话说,只有使用如下定义创建,定义为 NULLTIMESTAMP列才会自动更新:

CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)

否则-也就是说,如果使用NULL而不是DEFAULT TIMESTAMP来定义TIMESTAMP列,如下所示...

CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');

...则必须显式插入一个对应当前日期和时间的值。例如:

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);