MySQL :: MySQL 8.4 参考手册 :: 13.2.1 日期和时间数据类型语法

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 13 种数据类型 / 13.2 日期和时间数据类型 / 13.2.1 日期和时间数据类型语法

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 96%

13.2.1 日期和时间数据类型语法13.2.1 Date and Time Data Type Syntax

用于表示时间值的日期和时间数据类型有 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

对于 DATE 和 DATETIME 范围描述，“支持” 意味着尽管较早的值可能有效，但不能保证。For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

MySQL 允许为 TIME、DATETIME 和 TIMESTAMP 值使用小数秒，精确到微秒（6 位数字）。要定义包含小数秒部分的列，请使用语法 type_name(fsp)，其中 type_name 是 TIME、DATETIME 或 TIMESTAMP，而 fsp 是小数秒的精确度。例如：MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

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

如果提供，fsp 的值必须在 0 到 6 之间。值为 0 表示没有小数部分。如果省略，默认精度为 0。（这与标准 SQL 的默认值 6 不同，以兼容以前的 MySQL 版本。）The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

表中的任何 TIMESTAMP 或 DATETIME 列都可以具有自动初始化和更新属性；请参见第 13.2.5 节，“TIMESTAMP 和 DATETIME 的自动初始化和更新”。Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating properties; see Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

DATE DATE
日期。支持的范围是 '1000-01-01' 到 '9999-12-31'。MySQL 以 'YYYY-MM-DD' 格式显示 DATE 值，但允许使用字符串或数字将值赋给 DATE 列。A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but permits assignment of values to DATE columns using either strings or numbers.
DATETIME[(fsp)] DATETIME[(fsp)]
A date and time combination. The supported range is '1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.499999'. MySQL displays DATETIME values in 'YYYY-MM-DD hh:mm:ss[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.A date and time combination. The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999'. MySQL displays DATETIME values in 'YYYY-MM-DD hh:mm:ss[.fraction]' format, but permits assignment of values to DATETIME columns using either strings or numbers.
可以给出可选的 fsp 值，范围从 0 到 6，用于指定小数秒的精度。值为 0 表示没有小数部分。如果省略，则默认精度为 0。An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
可以使用 DEFAULT 和 ON UPDATE 列定义子句为 DATETIME 列指定自动初始化和更新为当前日期和时间，如第 13.2.5 节，“TIMESTAMP 和 DATETIME 的自动初始化和更新” 所述。Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
TIMESTAMP[(fsp)] TIMESTAMP[(fsp)]
时间戳。范围是 '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.499999' UTC。TIMESTAMP 值存储为自纪元以来的秒数（'1970-01-01 00:00:00' UTC）。TIMESTAMP 无法表示值 '1970-01-01 00:00:00'，因为这相当于纪元时间的 0 秒，而 0 的值是保留用来表示 '0000-00-00 00:00:00'，即 “零” TIMESTAMP 值。A timestamp. The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.499999' UTC. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). A TIMESTAMP cannot represent the value '1970-01-01 00:00:00' because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing '0000-00-00 00:00:00', the “zero” TIMESTAMP value.
可以给出可选的 fsp 值，范围从 0 到 6，用于指定小数秒的精度。值为 0 表示没有小数部分。如果省略，则默认精度为 0。An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
服务器处理 TIMESTAMP 定义的方式取决于 explicit_defaults_for_timestamp 系统变量的值（参见第 7.1.8 节，“服务器系统变量”）。The way the server handles TIMESTAMP definitions depends on the value of the explicit_defaults_for_timestamp system variable (see Section 7.1.8, “Server System Variables”).
如果启用了explicit_defaults_for_timestamp，则不会自动为任何TIMESTAMP列分配DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性。它们必须在列定义中明确包含。此外，任何未明确声明为NOT NULL的TIMESTAMP允许NULL值。If explicit_defaults_for_timestamp is enabled, there is no automatic assignment of the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes to any TIMESTAMP column. They must be included explicitly in the column definition. Also, any TIMESTAMP not explicitly declared as NOT NULL permits NULL values.
如果explicit_defaults_for_timestamp 被禁用，服务器将如下处理 TIMESTAMP：If explicit_defaults_for_timestamp is disabled, the server handles TIMESTAMP as follows:
除非另有说明，否则表中的第一个 TIMESTAMP 列如果没有显式赋值，将被定义为自动设置为最近修改的日期和时间。这使得 TIMESTAMP 对记录 INSERT 或 UPDATE 操作的时间戳非常有用。您还可以通过为任何 TIMESTAMP 列赋值 NULL 来设置当前日期和时间，除非它已被定义允许 NULL 值的 NULL 属性。Unless specified otherwise, the first TIMESTAMP column in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes TIMESTAMP useful for recording the timestamp of an INSERT or UPDATE operation. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.
可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP列定义子句来指定自动初始化和更新为当前日期和时间。默认情况下，第一个TIMESTAMP列具有这些属性，如前所述。然而，表中的任何TIMESTAMP列都可以定义为具有这些属性。Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.
TIME[(fsp)] TIME[(fsp)]
时间。范围是 '-838:59:59.000000' 到 '838:59:59.000000'。MySQL 以 'hh:mm:ss[.fraction]' 格式显示 TIME 值，但允许使用字符串或数字为 TIME 列赋值。A time. The range is '-838:59:59.000000' to '838:59:59.000000'. MySQL displays TIME values in 'hh:mm:ss[.fraction]' format, but permits assignment of values to TIME columns using either strings or numbers.
可以给出可选的 fsp 值，范围从 0 到 6，用于指定小数秒的精度。值为 0 表示没有小数部分。如果省略，则默认精度为 0。An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.
YEAR[(4)]
四位数字格式的年份。MySQL 以 YYYY 格式显示 YEAR 值，但允许使用字符串或数字为 YEAR 列赋值。值显示为 1901 到 2155，或者 0000。A year in 4-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, or 0000.
有关 YEAR 显示格式和输入值解释的更多信息，请参见第 13.2.4 节，“YEAR 类型”。For additional information about YEAR display format and interpretation of input values, see Section 13.2.4, “The YEAR Type”.
Note
带有显式显示宽度的 YEAR(4) 数据类型已弃用；您应该预期未来版本的 MySQL 将取消对其的支持。相反，请使用不带显示宽度的 YEAR，其意义相同。The YEAR(4) data type with an explicit display width is deprecated; you should expect support for it to be removed in a future version of MySQL. Instead, use YEAR without a display width, which has the same meaning.

SUM() 和 AVG() 聚合函数不适用于时间值。（它们会将值转换为数字，丢失第一个非数字字符之后的所有内容。）为解决此问题，请先转换为数字单位，执行聚合操作，然后再转换回时间值。示例：The SUM() and AVG() aggregate functions do not work with temporal values. (They convert the values to numbers, losing everything after the first nonnumeric character.) To work around this problem, convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples:

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

首页上一级下一节