“日期和时间”数据类型

译者注：

• 时间值：包含日期、时分秒、“日期+时分秒”等关于“日期和时间”的值。

• 小数：从 MySQL 5.6.4 开始支持小数，精度最高至微秒（6 位小数）

概述

• “日期和时间”数据类型的句法

• DATE、DATETIME 和 TIMESTAMP 类型

• TIME 类型

• YEAR 类型

• TIMESTAMP 和 DATETIME 的自动初始化与更新

• 时间的小数

• “日期和时间”类型之间的转换

• 2 位数年份的日期

用于表示时间值的“日期和时间”数据类型有：DATE、TIME、DATETIME、TIMESTAMP、YEAR。每个时间类型均拥有各自合法的取值范围，以及一个“零”值，即当你指定一个 MySQL 无法表达的非法值时会被替换为该值。TIMESTAMP 和 DATETIME 数据类型拥有特殊的自动更新能力。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

关于时间值的存储要求，请看 Section 11.7, “Data Type Storage Requirements”。

关于对时间值进行运算的函数说明，请看 Section 12.6, “Date and Time Functions”。

使用“日期和时间”数据类型时，请记住以下一般注意事项：

• MySQL 以标准输出格式检索指定的“日期和时间”类型的值。对于提供的各种格式的输入值，MySQL 也会尝试去解析（例如，将一个值赋值给“日期和时间”类型或与“日期和时间”进行比较）。这时可能会产生不可预测的结果，所以建议你提供有效的值。关于“日期和时间”类型允许格式的说明，请看 Section 9.1.3, “Date and Time Literals”。

• 尽管 MySQL 会尝试以几种格式去解析值，但日期部分请务必以 year-month-day 的顺序提供（如 '98-09-04'），而不是别处常用的 month-day-year 或 day-month-year 顺序（如 '09-04-98'、'04-09-98'）。STR_TO_DATE() 函数能将其他顺序的字符串转化为 year-month-day 顺序。

• 2 位数年份的日期是不明确的，因为世纪未知。MySQL 会使用以下规则解析 2 位数的年份值：

  • 年份值 [70, 99] 内的会变为 [1970, 1999]。

  • 年份值 [00, 69] 内的会变为 [2000, 2069]。

  另请阅读 Section 11.2.8, “2-Digit Years in Dates”。

• 两个不同类型的时间值的转换规则，请查看 Section 11.2.7, “Conversion Between Date and Time Types”。

• 如果在数值类型的上下文中使用“日期和时间”，MySQL 会自动将“日期和时间” 转为数值，反之亦然。

• 默认情况下，MySQL 会将超出“日期和时间”类型 取值范围的值或非法值转为相应数据类型的“零”值。唯一例外是：TIME 对超出取值范围的值裁剪到 TIME 取值范围的相应端点。

• 通过将 SQL 模式设置为适当的值，可以让 MySQL 去支持你想要的日期类型。（详情请看 Section 5.1.11, “Server SQL Modes”）通过启用 SQL 的 [ALLOW_INVALID_DATES][59] 模式，可以使得 MySQL 接受某些值，如 '2009-11-31'（译者注：11 月只有 30 日）。如果要在数据库中存储用户指定（如，以 Web 表单）的，且“可能错误”的值，并在将来再对这些值进行处理时，那么这个模式就起到了作用。在这个模式下，MySQL 仅进行以下校验：month 是否在 [1, 12]，day 是否在 [1, 31]。

• MySQL 允许 DATE 或 DATETIME 列存储 day 或 month 与 day 为 0 的日期值。这对需要存储生日，但可能不知道精确日期的应用程序有用。在这种情况下，你只需将日期存储为 '2009-00-00' 或 '2009-01-00'。然而，对于这样的日期值，你并不能从诸如 DATE_SUB() 或 DATE_ADD() 之类需要完整日期参数的函数中得到正确的结果。可通过启用 NO_ZERO_IN_DATE 模式，实现禁止日期值出现 month 或 day 为 0 的情况。

• MySQL 允许你将 '0000-00-00' 这样的“零”值作为“虚拟日期”（dummy date）进行存储。在某些情况下，这比使用 NULL 值更方便，并且使用更少的数据和索引空间。可通过启用 NO_ZERO_DATE 模式来禁止 '0000-00-00'。

• 通过 Connector/ODBC 使用“日期和时间”类型的“零”值时，会自动转换为 NULL，因为 ODBC 无法处理这类的值。

下面表格展示了每种数据类型对应的“零”值。“零”值是特殊的，但可以用表中展示的值显式地存储或引用它们。另外，你也可以使用更容易编写的值——'0' 或 0 来实现同样的操作。对于含有日期的时间类型（DATE、DATETIME 和 TIMESTAMP），使用这些值可能会产生警告（warning）或错误（error）。这种明确的行为取决于严格模式的启用与否和 NO_ZERO_DATE 模式中的哪一个。详情请看 Section 5.1.11, “Server SQL Modes”。

Data Type	"Zero" Value
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR	0000

“日期和时间”数据类型的句法

用于表示时间值的“日期和时间”数据类型有：DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。

对于 DATE 和 DATETIME 取值范围的描述，“支持（supported）”表示尽管较早的值可能有效，但并不保证。

MySQL 允许 TIME、DATETIME 和 TIMESTAMP 拥有小数，精度最大为微秒（6 位数）。为了定义拥有小数的列，需要使用 type_name(fsp) 句法，其中 type_name 是 TIME、DATETIME 或 TIMESTAMP 中的一个，fsp 是小数的精度。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

fsp 的取值范围是 [0, 6]。0 表示没有小数部分。默认值是 0（缺省时）。（与 SQL 标准的默认值 6 不同，主要为了兼容 MySQL 旧版本。）

表中任意 TIMESTAMP 和 DATETIME 列都可具有自动初始化和更新的属性，详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

• DATE 日期。支持（supported）的取值范围是 ['1000-01-01', '9999-12-31']。MySQL 以 'YYYY-MM-DD' 的形式展示 DATE 值，但允许将字符串和数值赋值给 DATE 列。

• DATETIME[(fsp)] 日期和时间的结合体。支持（supported）的取值范围是 ['1000-01-01 00:00:00.000000', '9999-12-31 23:59:59.999999']。MySQL 以 'YYYY-MM-DD hh:mm:ss[.fraction]' 的形式展示 DATETIME 的值，但允许将字符串和数值赋值给 DATETIME 列。

  可选参数 fsp 的取值范围是 [0, 6]，它用于指定小数部分的精度。0 表示没有小数部分，若缺省，默认值为 0。

  可在 DATETIME 列定义语句中通过 DEFAULT 和 ON UPDATE 子句启用自动初始化和更新为当前日期时间值的特性。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

• TIMESTAMP[(fsp)] 时间戳。取值范围是 ['1970-01-01 00:00:01.000000' UTC, '2038-01-19 03:14:07.999999' UTC]。TIMESTAMP 可存储自纪元（epoch，'1970-01-01 00:00:00' UTC）以来的秒数。TIMESTAMP 不能表示 '1970-01-01 00:00:00'，因为它相当于自纪元开始的第 0 秒，而保留值 0 表示 TIMESTAMP 的“零”值——'0000-00-00 00:00:00'。

  可选参数 fsp 的取值范围是 [0, 6]，它用于指定小数部分的精度。0 表示没有小数部分，若缺省，默认值为 0。

  服务器处理 TIMESTAMP 定义的方式取决于 explicit_defaults_for_timestamp 系统变量（详情请看 Section 5.1.8, “Server System Variables”）。

  若启用 explicit_defaults_for_timestamp，则不会将 DEFAULT_CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 属性自动分配给任何 TIMESTAMP 列。它们必须明确包含在列定义中。另外，任何 TIMESTAMP 未明确声明为 NOT NULL，则允许 NULL 值。

  若未启用 explicit_defaults_for_timestamp，服务器会按照以下规则处理 TIMESTAMP：

  除非另有说明，否则表中的第一个 TIMESTAMP 列会被定义为：若未明确赋值，则会被自动设置为最近修改的日期和时间。这对用于记录 INSERT 或 UPDATE 操作时的时间戳非常有用。你也可以为任意 TIMESTAMP 列赋值 NULL，以取得当前 日期和时间，除非该列定义为允许为 NULL。

  可在定义时包含 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句，以获得自动初始化和更新为当前日期和时间的能力。如上一段所述，默认情况下，第一个 TIMESTAMP 列会自动拥有这些属性。当然，你也可以为表中任意 TIMESTAMP 列定义这些属性。

• TIME[(fsp)] 时间。取值范围是 ['-838:59:59.000000', '838:59:59.000000']。MySQL 以 'hh:mm:ss[.fraction]' 的形式展示 TIME 值，但允许将字符串或数值赋值给 TIME 列。

  可选参数 fsp 的取值范围是 [0, 6]，它用于指定小数部分的精度。0 表示没有小数部分，若缺省，默认值为 0。

• YEAR[(4)] 4 位数的年份。MySQL 以 YYYY 的形式显示 YEAR 值，但允许将字符串或数值赋值给 YEAR 列。它的取值范围是 [1901, 2155] 或 0000。

  有关 YEAR 显示格式和输入值解析的更多信息，请看 Section 11.2.4, “The YEAR Type”。

  注意：从 MySQL 8.0.19 开始，不建议使用明确设定显示宽度的 YEAR(4) 数据类型，并将在未来的 MySQL 版本中移除它。而且，无显示宽度的 YEAR 与它的含义相同。

  MySQL 8.0 不支持旧版 MySQL 中允许的 2 位数 YEAR(2)。有关转换为 4 位数 YEAR 的说明，请看 MySQL 5.7 Reference Manual 的 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR。

聚合函数 SUM() 和 AVG() 不适用于时间值（它们将时间值转换为数值时，会丢失掉自第一个非数值字符后的所有信息）。为了解决该问题，可先将时间值转为数值单位后再执行聚合操作，最后再转回为时间值。如：

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name;
SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;

DATE、DATETIME 和 TIMESTAMP 类型

DATE、DATETIME 和 TIMESTAMP 三者是相关的。本节描述了它们的特征，包括它们的相似与不同。MySQL 能以几种形式识别 DATE、DATETIME 和 TIMESTAMP 的值，于 Section 9.1.3, “Date and Time Literals” 进行了详细描述。对于 DATE 和 TIMESTAMP 的取值范围描述，“支持（supported）”表示尽管较早的值可能有效，但并不保证。

DATE 类型适用于仅有日期但无时间的值。MySQL 以 'YYYY-MM-DD' 格式进行检索和显示。支持（supported）的取值范围是 ['1000-01-01', '9999-12-31']。

DATETIME 类型适用于同时含日期和时间的值。MySQL 以 'YYYY-MM-DD hh:mm:ss' 格式进行检索和显示。支持（supported）的取值范围是 ['1000-01-01 00:00:00', '9999-12-31 23:59:59']。

TIMESTAMP 类型适用于同时含有日期和时间的值。TIMESTAMP 的取值范围是 ['1970-01-01 00:00:01' UTC, '2038-01-19 03:14:07' UTC]。

DATETIME 和 TIMESTAMP 值能拥有小数部分，精度达到微秒（6 位数）。插入到 DATETIME 或 TIMESTAMP 列中的值的小数部分都会被存储而不是被丢弃。包括小数部分在内，这些值的格式是 'YYYY-MM-DD hh:mm:ss[.fraction]'。相应地，DATETIME 的取值范围 ['1000-01-01 00:00:00.000000', '9999-12-31 23:59:59.999999']；TIMESTAMP 的取值范围是 ['1970-01-01 00:00:01.000000', '2038-01-19 03:14:07.999999']。小数部分应始终与时间的其它部分用小数点分隔。除小数点外，任何标识符都不能作为小数的分隔符。关于 MySQL 对小数秒的支持信息，请看 see Section 11.2.6, “Fractional Seconds in Time Values”。

TIMESTAMP 和 DATETIME 数据类型提供了自动初始化和更新为当前日期和时间的特性。详情请看 Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”。

MySQL 会将 TIMESTAMP 的值从当前时区转化为 UTC 进行存储。相应地也会在检索时从 UTC 转化为当前时区。（对于其他类型，如 DATETIME，则不会发生这种情况）。默认情况下，每个连接（connection）的当前时区就是服务器的时间。可以在每个预连接（pre-connection）的基础上设置时区。只要时区设置保持不变，你就能获得与存储相同的值。如果存储了一个 TIMESTAMP 值，然后在改变时区后再检索该值，则检索的值就与存储的值不同。发生这种情况是因为存储与检索的时区不一致。当前时区可用作 time_zone 系统变量的值。详情请看 Section 5.1.14, “MySQL Server Time Zone Support”。

从 MySQL 8.0.19 开始，你可以在插入 TIMESTAMP 或 DATETIME 值时，指定时区偏移量。该偏移量追加在日期时间字面量的日期部分后，且中间没有空格。另外，同样格式也可用在 time_zone 系统变量。时区需要注意以下几点：

• 如果小时部分小于 10，则需要添加前置 0。

• '-00:00' 是不允许的。

• 不能使用 'EET' 和 'Asis/Shanghai' 等时区名字；这种情况下也不能使用 'SYSTEM'。

插入带有时区偏移量的日期时间到 TIMESTAMP 和 DATETIME 列时，使用不同的 time_zone 设置，会造成什么影响呢？（观察检索结果）：

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> CREATE TABLE dt (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col DATETIME NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = '+00:00';

mysql> INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql> SET @@time_zone = 'SYSTEM';

mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST                |
+--------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 10:10:10 |          1577891410 |
| 2020-01-01 04:40:10 |          1577871610 |
| 2020-01-01 18:10:10 |          1577920210 |
+---------------------+---------------------+

mysql> SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | UNIX_TIMESTAMP(col) |
+---------------------+---------------------+
| 2020-01-01 10:10:10 |          1577891410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
| 2020-01-01 05:10:10 |          1577873410 |
| 2019-12-31 23:40:10 |          1577853610 |
| 2020-01-01 13:10:10 |          1577902210 |
+---------------------+---------------------+

检索时并不会显示日期时间值的偏移量，尽管在插入时有一个值带有偏移量。

偏移量支持（supported）的取值范围是 [-14:00, +14:00]。

带有时区偏移量的日期时间字面量可通过参数化查询（prepared statements）指定参数。

非法的 DATE、DATETIME 和 TIMESTAMP 值会被转化为相应类型的“零”值（'0000-00-00' 或 '0000-00-00 00:00:00'），但前提是 SQL 模式允许此转换。这种明确的行为取决于严格模式的启用是否和 NO_ZERO_DATE 模式中的哪一个。详情请看 Section 5.1.11, “Server SQL Modes”。

注意 MySQL 对日期值的各个组成部分的解析规则：

• MySQL 对字符串类型的值采用“宽松”格式，任何标点符号均可作为日期或时间组成部分的分隔符。在某些情况下，这种语法是具有欺骗性的。例如，值 '10:11:12' 会因为冒号（:）被看作是 时间，但在日期上下文中会被解析为 '2010-11-12'。值 '10:45:15' 会被转为 '0000-00-00'，因为 '45' 是非法的月份。

  “日期和时间”与小数之间唯一能被识别的分隔符是小数点。

• 服务器要求 month 和 day 的组合值是合法的，而不仅仅局限于在各自范围 [1, 12]、[1, 31] 合法即可。当关闭严格模式，非法日期，如 '2004-04-31' 会被转为 '0000-00-00'，并产生一个警告。当启用严格模式，非法日期会报错。若要存储非法日期，需要启用 ALLOW_INVAILD_DATES。详情请看 Section 5.1.11, “Server SQL Modes”。

• MySQL 不接受 day 或 month 是 0 及其他非法日期值的 TIMESTAMP。该规则的唯一例外是特殊值——“零”值 '0000-00-00 00:00:00'，当然前提是 SQL 模式允许该特殊值。这种明确的行为取决于严格模式的启用与否和 NO_ZERO_DATE 模式中的哪一个。详情请看 Section 5.1.11, “Server SQL Modes”。

• 2 位数年份的日期是不明确的，因为世纪未知。MySQL 会使用以下规则解析 2 位数的年份值：

  • 年份值 [70, 99] 内的会变为 [1970, 1999]。

  • 年份值 [00, 69] 内的会变为 [2000, 2069]。

  详情请看 Section 11.2.8, “2-Digit Years in Dates”。

TIME 类型

MySQL 以 'hh:mm:ss'（或 hhh:mm:ss 格式，用于小时数较大的值）格式进行检索和显示。TIME 的取值范围是 ['-838:59:59', '838:59:59']。小时部分可能会很大，因为 TIME 不仅用于存储一天内的时间（这肯定小于 24 小时），而且还能存储消耗的时间或两事件之间的时间间隔（这可能远大于 24 小时，甚至是负数）。

MySQL 可识别几种格式的 TIME 值，其中包括小数部分，其精度最大达到微秒（6位数）。详情请看 Section 9.1.3, “Date and Time Literals”。有关 MySQL 对小数的支持信息，请看 Section 11.2.6, “Fractional Seconds in Time Values”。插入到 TIME 列中的值的小数部分都会被存储而不是被丢弃。当含有小数部分时，TIME 的取值范围是 ['-838:59:59.000000', '838:59:59.000000']。

将省略值赋值给 TIME 列时要小心。因为 MySQL 解析带有冒号（:）的省略 TIME 值时，会优先作为一天中的时间进行解析。也就是说，'11:12' 表示的是 '11:12:00'，而不是 '00:11:12'。MySQL 解析不带有冒号（:）的简写 TIME 值时，最右侧的两位数会被解析为秒（也就是说，会优先解析为消耗的时间，而不是一天中的时间）。例如，你可能认为 '1112' 和 1112 表示 '11:12:00'（11 时 12 分），其实 MySQL 将它们解析为 '00:11:12'（11 分 12 秒）。如此类推，'12' 和 12 会被解析为 '00:00:12'。

时间与小数之间唯一能被识别的分隔符是小数点。

默认情况下，位于 TIME 取值范围之外，但有效（译者注：指格式）的值将会被裁剪为最靠近端点的值。例如，'-850:00:00' 和 '850:00:00' 会被分别转为 '-838:59:59' 和 '838:59:59'。非法的 TIME 值会被转为 '00:00:00'。需要注意的是，因为 '00:00:00' 本身就是 TIME 的有效值，所以无法判断表中的 '00:00:00' 值是主动存储还是非法值转化而来的。

为了更严格地处理非法的 TIME 值（对此会产生一个错误），可启动 SQL 的严格模式。详情请看 Section 5.1.11, “Server SQL Modes”。

YEAR 类型

YEAR 是用来表示年份值的类型，其大小为 1 字节。可隐式声明为显示宽度为 4 字符的 YEAR，即与显式声明的 YEAR(4) 等效。

注意：从 MySQL 8.0.19 开始，不建议使用明确设定显示宽度的 YEAR(4) 数据类型，并将在未来的 MySQL 版本中移除它。而且，无显示宽度的 YEAR 与它的含义相同。

MySQL 8.0 不支持旧版 MySQL 中允许的 2 位数 YEAR(2)。有关转换为 4 位数 YEAR 的说明，请看 MySQL 5.7 Reference Manual 的 2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR。

MySQL 以 YYYY 的形式显示 YEAR 值。其取值范围是 [1901, 2155] 和 0000。

YEAR 接受以下几种格式的输入值：

• 位于 ['1901', '2155'] 的 4 位数的字符串。

• 位于 [1901, 2155] 的 4 位数的数值。

• 位于 ['0', '99'] 的 1 ~ 2 位数的字符串。MySQL 会对这些值进行转换，['0', '69'] 和 ['70', '99'] 会被分别转为 ['2000', '2069'], ['1970', '1999']。

• 位于 [0, 99] 的 1 ~ 2 位数的数值。MySQL 会对这些值进行转换，[1, 69] 和 [70, 99] 会被分别转为 [2001, 2069], [1970, 1999]。

  插入数值 0 的结果：显示值和内部值均为 0000。为了让 0 解析为 2000，需要指定为字符串 '0' 或 '00'。

• 返回值含有 YEAR 上下文的函数，如 NOW()。

若未开启 SQL 严格模式，MySQL 会将非法的 YEAR 值转为 0000。反之，插入一个非法的 YEAR 值时会报错。

另请查看 Section 11.2.8, “2-Digit Years in Dates”。

TIMESTAMP 和 DATETIME 的自动初始化与更新

TIMESTAMP 和 DATETIME 列拥有自动初始化和更新到当前日期和时间（即当前时间戳）的能力。

表中任意 TIMESTAMP 和 DATETIME 列都可以将当前时间戳作为默认值和自动更新的值（两者可同时存在）：

• 对于自动初始化的列，当插入未指定该列值的行时，会被自动设置为当前时间戳。

• 对于自动更新的列，当行的其他任意列值发生改变时，它会被自动更新为当前时间戳。当行的其他任意列设置同样的值时，它会保持不变。当你想实现其他列更新时，自动更新列保持不变，那么就需要明确地设置为现有值。当你想让自动更新列在其他列值未发生变化时而进行更新，那么就需要明确地将其设置为目标值（如，设置为 CURRENT_TIMESTAMP）。

此外，若系统变量 explicit_defaults_for_timestamp 被禁用，那么可通过为 TIMESTAMP 列（不包括 DATETIME 类型）赋值 NULL，实现初始化或更新为当前日期和时间。除非该列声明时允许 NULL 值。

要指定自动化属性，需要在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句。这两个子句的顺序无关紧要。如果定义中同时存在这两者，则任意一个都可以先出现。与 CURRENT_TIMESTAP 等价的有：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 模式 TRADITIONAL 是包含严格模式和 NO_ZERO_DATE 的。详情请看 Section 5.1.11, “Server SQL Modes”。

在 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 子句和默认值为常量的子句的列，拥有自动更新为当前时间戳的能力和默认值为指定常量。

   CREATE TABLE t1 (
     ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
     dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
   );

• 带有 ON UPDATE CURRENT_TIMESTAMP 子句，但没 DEFAULT 子句的列，拥有自动更新为当前时间戳的能力，但没有将当前时间戳作为它的默认值。

  这种情况下，默认值取决于具体类型。对于 TIMESTAMP，除非声明了 NULL 属性（此时默认值为 NULL），否则默认值为 0。

   CREATE TABLE t1 (
     ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
     ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
   );

  对于 DATETIME，除非声明了 NOT NULL 属性（此时默认值为 0），否则默认值为 NULL。

   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 系统变量，且第一个 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()。

请考虑以下表定义：

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);

这些表具有以下属性：

• 在每个表的定义中，第一个 TIMESTAMP 列没有自动初始化和更新的能力。

• 这些表在 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 列来说，NULL 是非法值）。如果 TIMESTAMP 列允许 NULL 值，那么赋值 NULL 就是 NULL，而不会转为当前时间戳。

以下表包含多个允许为 NULL 的 TIMESTAMP 列：

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()）。

换句话说，dui'yu允许为 NULL 的 TIMESTAMP 列仅在其定时中包含 DEFAULT CURRENT_TIMESTAMP 时才会被自动初始化：

CREATE TABLE t (ts TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);

如果 TIMESTAMP 列允许为 NULL，但定义时不包含 DEFAULT CURRENT_TIMESTAMP，那么在插入时必须明确为当前日期和时间。假设表 t1 和 t2 的定义如下：

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

要让任一表中的 TIMESTAMP 列设置为插入时的当前时间戳，需要明确赋值。如：

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

如果启用 explicit_defaults_for_timestamp 系统变量，那么只有在声明时包含 NULL 属性，TIMESTAMP 列才允许为 NULL。此外，无论声明 NULL 或 NOT NULL 属性，TIMESTAMP 列均不会因赋值为 NULL 而被转为当前时间戳。只有赋值为 CURRTENT_TIMESTAMP 或其代名词（如 NOW()）时，该列才为当前时间戳。

时间的小数

MySQL 的 TIME、DATETIME 和 TIMESTAMP 均支持小数，精度最高可达 6 位数（微秒）：

• 使用 type_name(fsp) 句法定义带有小数的列，其中 type_name 可以为 TIME、DATETIME 或 TIMESTAMP。另外，fsp 表示小数的精度。如：

   CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

  fsp 的取值范围为 [0, 6]。0 表示没小数。若缺省，默认值为 0（与 SQL 标准的默认值 6 不同，主要为了兼容 MySQL 旧版本）。

• 向 TIME、DATE 和 TIMESTAMP 列插入对应类型且带有小数部分的值时，实际可能会得到四舍五入后更少位数的值。考虑如下创建和填充表：

   CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
   INSERT INTO fractest VALUES
   ('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');

  时间值被四舍五入后插入表中：

   mysql> SELECT * FROM fractest;
   +-------------+------------------------+------------------------+
   | c1          | c2                     | c3                     |
   +-------------+------------------------+------------------------+
   | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 |
   +-------------+------------------------+------------------------+

  四舍五入并没有产生警告或错误。因为这个行为符合 SQL 标准。

  当你想要被裁剪而不是四舍五入时，请启用 SQL 模式的 TIME_TRUNCATE_FRACTIONAL：

   SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');

  当启用该 SQL 模式后，将以裁剪的方式插入时间值：

   mysql> SELECT * FROM fractest;
   +-------------+------------------------+------------------------+
   | c1          | c2                     | c3                     |
   +-------------+------------------------+------------------------+
   | 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 |
   +-------------+------------------------+------------------------+

• 对于拥有时间参数的函数来说，它接收带有小数的时间值时，返回值也会包括适当的小数。例如，不带参数的 NOW() 会返回不带有小数的日期和时间，当指定可选参数为 0 到 6 之间 的值时，返回值也会含有指定位数的小数。

• 通过时间字面量句法得到时间值：DATE 'str'、TIME 'str'、TIMESTAMP 'str' 和 ODBD 语法的等效项。结果值的小数可通过指定得到。在以前，时间类型关键字会被忽略，从而这些构造体会变成字符串值。详情请看 Standard SQL and ODBC Date and Time Literals。

“日期和时间”类型之间的转换

在某种程度上，你能将值从一种时间类型转换为另一种时间类型。但是，值可能会有所变化或丢失信息。在所有情况下，时间类型之间的转换都取决于目标类型的有效值范围。例如，尽管 DATE、DATETIME 和 TIMESTAMP 的值可以使用相同格式的值指定，但这些类型的取值范围并不相同。TIMESTAMP 的值不能早于 1970 UTC 或晚于 '2038-01-19 03:14:07' UTC。这意味着日期 '1968-01-01' 对 DATE 和 DATETIME 来说是合法的，但对 TIMESTAMP 来说是不合法的，并会转换为 0。

转换 DATE 值：

• 转换为 DATETIME 或 TIMESTAMP 值时，会补充 '00:00:00'。因为 DATE 值不包含时间信息。

• 转换为 TIME 是无用的，结果为 '00:00:00'。

转换 DATETIME 和 TIMESTAMP 值：

• 转换为 DATE 值时，会对时间小数进行四舍五入。例如，'1999-12-31 23:59:59.499' 转换得到 '1999-12-31'，而 '1999-12-31 23:59:59.500' 转换得到 '2000-01-01'。

• 转换为 TIME 值时，会丢掉日期部分，因为 TIME 类型不包含日期信息。

转换 TIME 值到其他时间类型时，会将 CURRENT_DATE() 的值作为日期部分。TIME 类型作为消耗时间（而不是一天中的时间）进行解析（译者注：即不局限于 24 小时内，且有可能是负数），然后与日期相加。这意味着，如果时间不在 ['00:00:00', '23:59:59'] 范围内时，则转换结果的日期与当前时间的日期是不同的。

假设，此时日期为 '2012-01-01'。TIME 类型的值分别是 '12:00:00'、'24:00:00' 和 '-12:00:00'，当它们转换为 DATETIME 或 TIMESTAMP 值时，得到结果分别是：'2012-01-01 12:00:00'、'2012-01-02 00:00:00' 和 '2011-12-13 12:00:00'。

从 TIME 转换为 DATE 时也与之类似，但会丢掉时间部分：'2012-01-01'、'2012-01-02' 和 '2011-12-31'。

可用显式转换替代隐式转换。例如，对于 DATE 和 DATETIME，DATE 值可通过添加时间部分——'00:00:00' 强行得到 DATETIME 类型。相反地，可通过忽略 DATETIME 的时间部分得到 DATE 类型，这可通过 CAST() 函数实现：

date_col = CAST(datetime_col AS DATE)

将 TIME 和 DATETIME 转换为数值形式（如通过加 +0）时，得到的结果类型取决于值是否含有小数部分。对于 TIME(N) 或 DATETIME(N)，当 N 为 0（或缺省）时，会转换为整型；当 N 大于 0 时，则转换为 DECIMAL 型：

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+

2 位数年份的日期

2 位数年份的日期是不明确的，因为世纪未知。由于 MySQL 内部使用 4 位数字存储年份，因此必须将这些解析为 4 位数字格式。

对于 DATETIME、DATE 和 TIMESTAMP，MySQL 会使用以下规则解析 2 位数的年份值：

• 年份值 [70, 99] 内的会变为 [1970, 1999]。

• 年份值 [00, 69] 内的会变为 [2000, 2069]。

对于 YEAR 类型，规则与上面一致，但有一个例外：当将数值 00 插入到 YEAR 时，会得到 0000 而不是 2000。要想 YEAR 将 0 解析为 2000，则需要指定为字符串类型的 '0' 或 '00' 。

请记住，这些规则只是对你想要的值进行合理猜测。如果 MySQL 使用这些规则未能产生你所需的值，那么请你提供明确的 4 位数年份值。

ORDER BY 可以正确排序 2 位数年份的 YEAR 值。

诸如 MIN() 和 MAX() 这些会将 YEAR 转为数值的函数，它们不能正确处理 2 位数年份。在这种情况下，需要将 YEAR 转为 4 位数的形式。