MySQL 8.4 Release Notes
- 13.2.1 日期和时间数据类型语法13.2.1 Date and Time Data Type Syntax
- 13.2.2 DATE、DATETIME 和 TIMESTAMP 类型13.2.2 The DATE, DATETIME, and TIMESTAMP Types
- 13.2.3 TIME 类型13.2.3 The TIME Type
- 13.2.4 YEAR 类型13.2.4 The YEAR Type
- 13.2.5 TIMESTAMP 和 DATETIME 的自动初始化和更新13.2.5 Automatic Initialization and Updating for TIMESTAMP and DATETIME
- 13.2.6 时间值中的小数秒13.2.6 Fractional Seconds in Time Values
- 13.2.7 MySQL 使用的历法13.2.7 What Calendar Is Used By MySQL?
- 13.2.8 日期和时间类型之间的转换13.2.8 Conversion Between Date and Time Types
- 13.2.9 日期中的两位数年份13.2.9 2-Digit Years in Dates
用于表示时间值的日期和时间数据类型有 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。每种时间类型都有有效值范围，以及在你指定 MySQL 无法表示的无效值时可以使用的“零”值。TIMESTAMP和DATETIME类型具有特殊的自动更新行为，详见第 13.2.5 节，“TIMESTAMP 和 DATETIME 的自动初始化和更新”。The date and time data types for representing temporal values are
DATE,
TIME,
DATETIME,
TIMESTAMP, and
YEAR. Each temporal type has a
range of valid values, as well as a “zero” value that
may be used when you specify an invalid value that MySQL cannot
represent. The TIMESTAMP and
DATETIME types have special
automatic updating behavior, described in
Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
有关时间数据类型存储要求的信息，请参见 第 13.7 节，“数据类型存储要求”。For information about storage requirements of the temporal data types, see Section 13.7, “Data Type Storage Requirements”.
有关操作时间值的函数的说明，请参见 第 14.7 节，“日期和时间函数”。For descriptions of functions that operate on temporal values, see Section 14.7, “Date and Time Functions”.
在处理日期和时间类型时，请记住以下一般注意事项：Keep in mind these general considerations when working with date and time types:
MySQL 会以标准输出格式检索给定日期或时间类型的值，但它会尝试解释你提供的各种输入值格式（例如，当你指定一个值要赋给或与日期或时间类型比较时）。有关日期和时间类型允许的格式说明，请参见 第 11.1.3 节，“日期和时间字面量”。你应当提供有效的值。如果使用其他格式的值，可能会产生不可预测的结果。MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). For a description of the permitted formats for date and time types, see Section 11.1.3, “Date and Time Literals”. It is expected that you supply valid values. Unpredictable results may occur if you use values in other formats.
虽然 MySQL 尝试以多种格式解析数值，但日期部分必须始终以年-月-日的顺序提供（例如，
'98-09-04'），而不是在其他地方常用的月-日-年或日-月-年的顺序（例如，'09-04-98'，'04-09-98'）。要将其他顺序的字符串转换为年-月-日顺序，STR_TO_DATE()函数可能会很有用。Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order (for example,'98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example,'09-04-98','04-09-98'). To convert strings in other orders to year-month-day order, theSTR_TO_DATE()function may be useful.包含两位数年份的日期是模糊的，因为世纪未知。MySQL 使用以下规则解释两位数年份：Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:
年份值在
70-99范围内时，将变为1970-1999。Year values in the range70-99become1970-1999.年份值在
00-69范围内时，将变为2000-2069。Year values in the range00-69become2000-2069.
另见 第 13.2.9 节，“日期中的两位数年份”。See also Section 13.2.9, “2-Digit Years in Dates”.
值从一种时间类型转换为另一种时间类型是根据第 13.2.8 节，“日期和时间类型之间的转换”中的规则进行的。Conversion of values from one temporal type to another occurs according to the rules in Section 13.2.8, “Conversion Between Date and Time Types”.
如果在数值上下文中使用日期或时间值，MySQL 会自动将其转换为数字，反之亦然。MySQL automatically converts a date or time value to a number if the value is used in numeric context and vice versa.
默认情况下，当 MySQL 遇到日期或时间类型的值超出范围或对该类型无效时，它会将该值转换为该类型的“零”值。例外情况是，超出范围的
TIME值会被限制到TIME范围的适当端点。By default, when MySQL encounters a value for a date or time type that is out of range or otherwise invalid for the type, it converts the value to the “zero” value for that type. The exception is that out-of-rangeTIMEvalues are clipped to the appropriate endpoint of theTIMErange.通过将 SQL 模式设置为适当的值，您可以更准确地指定希望 MySQL 支持的日期类型。 (见 第 7.1.11 节，“服务器 SQL 模式”。) 您可以通过启用
ALLOW_INVALID_DATESSQL 模式，使 MySQL 接受某些日期，例如'2009-11-31'。当您希望将用户指定的 “可能错误的” 值（例如在网页表单中）存储在数据库中以便将来处理时，这非常有用。在此模式下，MySQL 仅验证月份是否在 1 到 12 的范围内，以及日期是否在 1 到 31 的范围内。By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 7.1.11, “Server SQL Modes”.) You can get MySQL to accept certain dates, such as'2009-11-31', by enabling theALLOW_INVALID_DATESSQL mode. This is useful when you want to store a “possibly wrong” value which the user has specified (for example, in a web form) in the database for future processing. Under this mode, MySQL verifies only that the month is in the range from 1 to 12 and that the day is in the range from 1 to 31.MySQL 允许你在
DATE或DATETIME列中存储日期，其中日或月和日为零。这对于需要存储生日但可能不知道确切日期的应用程序非常有用。在这种情况下，你只需将日期存储为'2009-00-00'或'2009-01-00'。然而，对于这样的日期，你不应期望函数如DATE_SUB()或DATE_ADD()等需要完整日期的函数得到正确结果。要禁止日期中月或日为零，请启用NO_ZERO_IN_DATE模式。MySQL permits you to store dates where the day or month and day are zero in aDATEorDATETIMEcolumn. This is useful for applications that need to store birthdates for which you may not know the exact date. In this case, you simply store the date as'2009-00-00'or'2009-01-00'. However, with dates such as these, you should not expect to get correct results for functions such asDATE_SUB()orDATE_ADD()that require complete dates. To disallow zero month or day parts in dates, enable theNO_ZERO_IN_DATEmode.MySQL 允许您将
'0000-00-00'存储为 “零” 值，作为 “占位日期”。在某些情况下，这比使用NULL值更方便，并且占用更少的数据和索引空间。要禁止使用'0000-00-00'，请启用NO_ZERO_DATE模式。MySQL permits you to store a “zero” value of'0000-00-00'as a “dummy date.” In some cases, this is more convenient than usingNULLvalues, and uses less data and index space. To disallow'0000-00-00', enable theNO_ZERO_DATEmode.通过 Connector/ODBC 使用的 “零” 日期或时间值会自动转换为
NULL，因为 ODBC 无法处理此类值。“Zero” date or time values used through Connector/ODBC are converted automatically toNULLbecause ODBC cannot handle such values.
下表显示了每种类型的“零”值的格式。“零”值是特殊的，但你可以使用表中显示的值显式地存储或引用它们。你也可以使用值'0'或0，它们更容易书写。对于包含日期部分的时间类型（DATE、DATETIME和TIMESTAMP），使用这些值可能会产生警告或错误。具体行为取决于启用了哪些严格模式及NO_ZERO_DATE SQL 模式；详见第 7.1.11 节，“服务器 SQL 模式”。The following table shows the format of the “zero”
value for each type. The “zero” values are special,
but you can store or refer to them explicitly using the values
shown in the table. You can also do this using the values
'0' or 0, which are easier
to write. For temporal types that include a date part
(DATE,
DATETIME, and
TIMESTAMP), use of these values may
produce warning or errors. The precise behavior depends on which,
if any, of the strict and
NO_ZERO_DATE SQL modes are
enabled; see Section 7.1.11, “Server SQL Modes”.