5.2.3 日期和时间类型-MySQL从入门到精通(二)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(二)

5.2.3 日期和时间类型

在MySQL数据库中，日期和时间类型是非常重要的一类数据类型，它们用于存储日期、时间、年份、月份、日期加时间等信息。MySQL提供了多种日期和时间类型以满足不同的需求，从简单的年月日表示到复杂的日期时间加时区信息，每种类型都有其特定的应用场景和存储方式。本节将详细介绍MySQL中的日期和时间类型，包括每种类型的定义、使用场景、存储范围、格式化以及如何进行日期和时间的计算与比较。

5.2.3.1 日期和时间类型概览

MySQL中的日期和时间类型大致可以分为五类：DATE、TIME、DATETIME、TIMESTAMP、YEAR。每种类型都有其特定的存储格式和用途。

DATE：仅包含日期部分，格式为YYYY-MM-DD，范围从’1000-01-01’到’9999-12-31’。
TIME：仅包含时间部分，格式为HH:MM:SS，可选包含微秒部分（即HH:MM:SS[.fraction]），时间范围从’-838:59:59’到’838:59:59’。TIME类型可以用来表示时间间隔。
DATETIME：包含日期和时间部分，格式为YYYY-MM-DD HH:MM:SS，可选包含微秒部分，范围从’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。
TIMESTAMP：也是包含日期和时间的类型，但其值是基于UTC的，格式与DATETIME相同。TIMESTAMP的范围较小，从’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC（注意这里提到的2038年问题是UNIX时间戳的溢出问题，MySQL 8.0之后通过新的数据类型如DATETIME(6)扩展了时间范围）。TIMESTAMP类型常用于需要记录数据变更时间的场景，因为它会自动更新。
YEAR：仅包含年份部分，格式为YYYY，范围从1901到2155。

5.2.3.2 日期和时间类型的选择

选择合适的日期和时间类型对于数据库设计至关重要。以下是一些选择时需要考虑的因素：

存储需求：不同的类型占用的存储空间不同，例如，DATE类型占4个字节，DATETIME占8个字节，而TIMESTAMP在MySQL 5.6.4及以后版本中也是8个字节（之前版本为4字节）。
时间范围：如果你的应用需要处理的时间范围超出了某个类型的能力，就需要选择其他类型。
自动更新特性：TIMESTAMP类型具有自动更新的特性，当行的其他列（特定列）被更新时，TIMESTAMP列可以自动设置为当前的日期和时间。这对于记录数据行的最后修改时间非常有用。
时区考虑：TIMESTAMP类型在存储时会自动转换为UTC，检索时会根据会话的时区设置进行转换，这对于需要跨时区处理时间的应用来说是一个重要特性。

5.2.3.3 日期和时间类型的操作

MySQL提供了丰富的函数和操作符来处理日期和时间类型的数据，包括但不限于日期和时间的加减、格式化、比较等。

日期和时间的加减：可以使用DATE_ADD(), DATE_SUB(), INTERVAL等函数或操作符来对日期和时间进行加减操作。例如，DATE_ADD('2023-01-01', INTERVAL 1 MONTH)将返回’2023-02-01’。
格式化：DATE_FORMAT()函数允许你按照指定的格式显示日期和时间。例如，DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')将返回当前的日期和时间，格式为YYYY-MM-DD HH:MM:SS。
比较：日期和时间类型的数据可以直接使用比较操作符（如=、<、>等）进行比较。
提取特定部分：YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()等函数可以用来从日期时间值中提取特定的部分。

5.2.3.4 注意事项

时区问题：在使用TIMESTAMP类型时，要特别注意时区的影响。确保你的应用逻辑正确处理了时区转换。
2038年问题：虽然MySQL 8.0之后通过扩展DATETIME类型的时间范围缓解了这一问题，但在设计需要长期存储时间数据的应用时，仍然需要关注这一潜在风险。
存储效率：在选择日期时间类型时，要考虑存储效率。例如，如果只需要存储年份，使用YEAR类型会比使用DATE或DATETIME类型更节省空间。
默认值：MySQL允许为日期和时间类型的列设置默认值，这可以在数据插入时提供便利。

5.2.3.5 实战案例

假设我们正在设计一个订单系统，需要记录订单的创建时间、最后更新时间以及预计发货时间。我们可以这样设计数据库表：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,  -- 订单创建日期
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 最后更新时间，自动更新
    expected_delivery_datetime DATETIME NOT NULL  -- 预计发货时间
);

在这个例子中，order_date使用了DATE类型来存储订单的创建日期，因为它不需要时间部分；last_updated使用了TIMESTAMP类型，并利用了它的自动更新特性来记录订单的最后一次更新时间；expected_delivery_datetime则使用了DATETIME类型来存储预计的发货时间，因为它需要包含具体的日期和时间信息。

通过本节的介绍，你应该对MySQL中的日期和时间类型有了更深入的了解，并能够根据实际需求选择合适的类型进行数据库设计。