mysql创建一个存储时间的表

11.3. 日期和时间类型
11.3.1. DATETIME、DATE和TIMESTAMP类型
11.3.2. TIME类型
11.3.3. YEAR类型
11.3.4. Y2K事宜和日期类型
表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。
假如试图插入一个不合法的日期，MySQL将给出警告或错误。可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如””1999-11-31″”。当你想要保存一个“可能错误的”用户已经在数据库中指定(例如，以web形式)用于将来处理的值时很有用。在这种模式下，MySQL只验证月范围为从0到12，日范围为从0到31。这些范围可以包括零，原因是MySQL允许在DATE或DATETIME列保存日/月和日是零的日期。这在应用程序需要保存一个你不知道确切日期的生日时非常有用。在这种情况下，只需要将日期保存为””1999-00-00″”或””1999-01-00″”。假如保存此类日期，DATE_SUB()或DATE_ADD等需要完整日期的函数不会得到正确的结果。(假如你不想在日期中出现零，可以使用NO_ZERO_IN_DATE SQL模式)。
MySQL还允许将””0000-00-00″”保存为“伪日期”(假如不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。
将sql_mode系统变量设置为相应模式值，可以更确切你想让MySQL支持哪种日期。参见5.3.2节，“SQL服务器模式”。
当使用日期和时间类型时应记住以下几点：
·         MySQL以标准输出格式检索给定日期或时间类型的值，但它尽力解释你指定的各种输入值格式(例如，当你指定一个分配给或与日期或时间类型进行比较的值时)。只支持下面章节中描述的格式。期望你能提供有效值。假如你使用其它格式的值会发生意想不到的结果。
·         包含两位年值的日期会令人模糊，原因是世纪不知道。MySQL使用以下规则解释两位年值：
o        70-99范围的年值转换为1970-1999。
o        00-69范围的年值转换为2000-2069。
·         尽管MySQL尝试解释几种格式的值，日期总是以年-月-日顺序(例如，””98-09-04″”)，而不是其它地方常用的月-日-年或日-月-年顺序(例如，””09-04-98″”，””04-09-98″”)。
·         假如值用于数值上下文中，MySQL自动将日期或时间类型的值转换为数字，反之亦然。
·         当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时(如本节开始所描述)，它将该值转换为该类的“零”值。一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点。
下面的表显示了各类“零”值的格式。请注意假如启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。
列类型
“零”值

DATETIME
“”0000-00-00 00:00:00″”

DATE
“”0000-00-00″”

TIMESTAMP
00000000000000

TIME
“”00:00:00″”

YEAR
0000

·         “零”值是特殊值，但你可以使用表内显示的值显式保存或引用它们。你也可以使用值””0″”或0来保存或引用，写起来更容易。
·         MyODBC中使用的“零”日期或时间值在MyODBC 2.50.12和以上版本中被自动转换为NULL，原因是ODBC不能处理此类值。
11.3.1. DATETIME、DATE和TIMESTAMP类型
11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性
DATETIME、DATE和TIMESTAMP类型是相关的。该节描述了它们的特征，它们的相似点和不同点。
当你需要同时包含日期和时间信息的值时则使用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模式。这些属性将在本节后面描述。
可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP值：
·         “”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″”。
·         YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为 “”1983-09-05 13:28:00″”。
·         YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，19830905和830905被解释为””1983-09-05″”。
·         函数返回的结果，其值适合DATETIME、DATE或TIMESTAMP上下文，例如NOW()或CURRENT_DATE。
无效DATETIME、DATE或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″”相同。
数字值应为6、8、12或14位长。假如一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。假如数字 是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。
指定为非限定符字符串的值使用给定的长度进行解释。假如字符串为8或14字符长，前4位数表示年。否则，前2位数表示年。从左向右解释字符串内出现的各部分，以发现年、月、日、小时、分和秒值。这说明不应使用少于6字符的字符串。例如，假如你指定””9903″”，认为它表示1999年3月，MySQL将在你的表内插入一个“零”日期值。这是原因是年和月值是99和03，但日部分完全丢失，因此该值不是一个合法的日期。但是，可以明显指定一个零值来代表缺少的月或日部分。例如，可以使用””990300″”来插入值””1999-03-00″”。
在一定程度上，可以将一个日期类型的值分配给一个不同的日期类型。但是，值可能会更改或丢失一些信息：
·         假如你为一个DATETIME或TIMESTAMP对象分配一个DATE值，结果值的时间部分被设置为””00:00:00″”，原因是DATE值未包含时间信息。
·         假如你为一个DATE对象分配一个DATETIME或TIMESTAMP值，结果值的时间部分被删除，原因是DATE值未包含时间信息。
·         记住尽管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值，不同类型的值的范围却不同。例如，TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如””1968-01-01″”，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，假如分配给这样一个对象将被转换为0。
当指定日期值时请注意某些缺陷：
·         指定为字符串的值允许的非严格格式可能会欺骗。例如，值””10:11:12″”由于‘:’间割符看上去可能象时间值，但假如用于日期上下文值则被解释为年””2010-11-12″”。值””10:45:15″”被转换为””0000-00-00″”原因是””45″”不是合法月。
·         在非严格模式，MySQL服务器只对日期的合法性进行基本检查：年、月和日的范围分别是1000到9999、00到12和00到31。任何包含超出这些范围的部分的日期被转换成””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模式运行。当服务器以该模式运行时，TIMESTAMP与DATETIME相等。也就是说，假如创建表时服务器以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列自动初始化或更新为当前的日期和时间。不再需要为第1个TIMESTAMP列。
请注意下面讨论所信息只适用于创建时未启用MAXDB模式的表的TIMESTAMP列。(如上所述，MAXDB模式使列创建为DATETIME列）。控制TIMESTAMP列的初始化和更新的规则如下所示：
·         假如一个表内的第1个TIMESTAMP列指定为一个DEFAULT值，则不能忽略。 默认值可以为CURRENT_TIMESTAMP或常量日期和时间值。
·         DEFAULT NULL与第1个TIMESTAMP 列的DEFAULT CURRENT_TIMESTAMP相同。对于其它TIMESTAMP列，DEFAULT NULL被视为DEFAULT 0。
·         表内的任何一个TIMESTAMP列可以设置为自动初始化为当前时间戳和/或更新。
·         在CREATE TABLE语句中，可以用下面的任何一种方式声明第1个TIMESTAMP列：
o        用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句，列为默认值使用当前的时间戳，并且自动更新。
o        不使用DEFAULT或ON 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来启用自动更新而不让列自动初始化）。
·         在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或NOW()。它们均具有相同的效果。
两个属性的顺序并不重要。假如一个TIMESTAMP列同时指定了DEFAULT和ON 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列指定自动默认或更新，必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如，DEFAULT 0或DEFAULT””2003-01-01 00:00:00″”)。然后，对于其它TIMESTAMP列，规则与第1个TIMESTAMP列相同，例外情况是不能忽略DEFAULT和ON UPDATE子句。假如这样做，则不会自动进行初始化或更新。
例如：下面这些语句是等