MySQL高级数据类型之：`MySQL`的`Temporal`类型：`DATETIME`、`TIMESTAMP`和`DATE`的存储与时区处理。

MySQL Temporal 类型：DATETIME、TIMESTAMP 和 DATE 的存储与时区处理

大家好，今天我们来深入探讨 MySQL 中重要的 Temporal 类型：DATETIME、TIMESTAMP 和 DATE。理解这些类型的存储方式以及它们如何处理时区对于构建可靠且数据一致的应用程序至关重要。我们将探讨每种类型的特性、适用场景，以及如何在实际应用中正确使用它们。

Temporal 类型概览

MySQL 提供了几种用于存储日期和时间值的 Temporal 类型，它们分别是：

• DATE: 用于存储日期，格式为 ‘YYYY-MM-DD’。
• DATETIME: 用于存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’。
• TIMESTAMP: 也用于存储日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’，但它以 UTC 时间戳的形式存储，并会根据服务器的时区设置进行转换。
• TIME: 用于存储时间，格式为 ‘HH:MM:SS’。
• YEAR: 用于存储年份，可以存储 2 位或 4 位数字。

今天我们重点关注 DATE、DATETIME 和 TIMESTAMP 这三种类型，因为它们在实际应用中最为常见，并且涉及到了时区处理这个相对复杂的问题。

DATE 类型

DATE 类型非常简单，它只存储日期，不包含时间信息。其格式固定为 ‘YYYY-MM-DD’。

存储方式:

DATE 类型占用 3 个字节的存储空间。

适用场景:

• 存储生日
• 存储发布日期
• 存储任何只需要日期的信息，而不需要时间信息的场景。

示例:

CREATE TABLE events (
    event_id INT PRIMARY KEY AUTO_INCREMENT,
    event_date DATE
);

INSERT INTO events (event_date) VALUES ('2023-10-27');

SELECT event_date FROM events;
-- 输出: 2023-10-27

DATETIME 类型

DATETIME 类型用于存储日期和时间信息，格式为 ‘YYYY-MM-DD HH:MM:SS’。

存储方式:

DATETIME 类型占用 8 个字节的存储空间。

适用场景:

• 存储创建时间
• 存储会议时间
• 存储任何需要日期和时间的精确信息的场景。

示例:

CREATE TABLE meetings (
    meeting_id INT PRIMARY KEY AUTO_INCREMENT,
    meeting_time DATETIME
);

INSERT INTO meetings (meeting_time) VALUES ('2023-10-27 10:30:00');

SELECT meeting_time FROM meetings;
-- 输出: 2023-10-27 10:30:00

TIMESTAMP 类型

TIMESTAMP 类型也用于存储日期和时间信息，格式同样为 ‘YYYY-MM-DD HH:MM:SS’。但与 DATETIME 最大的区别在于，TIMESTAMP 会将输入的时间转换为 UTC 时间存储，并在检索时根据服务器的时区设置进行转换。

存储方式:

TIMESTAMP 类型占用 4 个字节的存储空间，相比 DATETIME 更节省空间。

适用场景:

• 存储需要跟踪时间变化的历史数据
• 存储需要在不同时区之间共享的数据
• 存储对存储空间有较高要求的日志数据

时区处理:

TIMESTAMP 的核心在于其时区处理机制。 当你插入一个 TIMESTAMP 值时，MySQL 会假设这个值是基于当前服务器的时区。 然后，它会将这个值转换为 UTC 时间并存储。 当你检索这个 TIMESTAMP 值时，MySQL 会将 UTC 时间转换回当前服务器的时区。

示例:

首先，查看当前服务器的时区设置：

SELECT @@global.time_zone, @@session.time_zone;

如果输出是 SYSTEM，则表示服务器使用操作系统默认的时区。你也可以显式设置时区，例如：

SET time_zone = '+08:00';  -- 设置为东八区

然后，创建表并插入数据：

CREATE TABLE logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    log_time TIMESTAMP
);

INSERT INTO logs (log_time) VALUES ('2023-10-27 10:30:00');

SELECT log_time FROM logs;
-- 输出 (假设服务器时区为 +08:00): 2023-10-27 10:30:00

如果稍后将服务器时区更改为 UTC：

SET time_zone = '+00:00'; -- 设置为 UTC
SELECT log_time FROM logs;
-- 输出: 2023-10-27 02:30:00 (因为 10:30:00 +08:00 等于 02:30:00 UTC)

TIMESTAMP 的自动更新特性：

TIMESTAMP 类型还有一个特殊的特性： 可以自动更新。 你可以在一个表中定义多个 TIMESTAMP 列，其中一个可以设置为 ON UPDATE CURRENT_TIMESTAMP，这样每次更新该行数据时，这个 TIMESTAMP 列的值都会自动更新为当前时间。 另外，还可以将一个TIMESTAMP 列设置为 DEFAULT CURRENT_TIMESTAMP，这样在插入新行时，如果该列没有指定值，则会自动设置为当前时间。

CREATE TABLE audit_log (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO audit_log (data) VALUES ('Initial data');
-- created_at 和 updated_at 会自动设置为当前时间

UPDATE audit_log SET data = 'Updated data' WHERE audit_id = 1;
-- updated_at 会自动更新为当前时间

TIMESTAMP 的范围限制：

TIMESTAMP 类型有一个范围限制。 MySQL 5.6.4 之前的版本，其范围是 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC （受限于 32 位 Unix 时间戳）。 MySQL 5.6.4 及之后的版本，范围扩大到 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。 如果你需要存储超出这个范围的时间，则应该使用 DATETIME 类型。

DATETIME vs TIMESTAMP: 如何选择？

特性	DATETIME	TIMESTAMP
存储空间	8 字节	4 字节
时区转换	不进行时区转换	存储为 UTC，检索时转换为服务器时区
范围	更广阔的范围	范围有限制 (通常到 2038 年)
自动更新	不支持自动更新	支持 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP
适用场景	不需要时区转换，需要更大范围的日期时间	需要时区转换，需要跟踪时间变化的历史数据

总结:

• 如果你不需要处理时区，并且需要存储超出 TIMESTAMP 范围的日期和时间，那么 DATETIME 是一个不错的选择。
• 如果你的应用需要处理时区，或者需要跟踪数据的修改时间，并且可以接受 TIMESTAMP 的范围限制，那么 TIMESTAMP 是一个更好的选择。
• 如果你的应用对存储空间非常敏感，并且可以接受 TIMESTAMP 的范围限制，那么 TIMESTAMP 也是一个不错的选择。

时区处理的最佳实践

正确处理时区对于任何涉及日期和时间的应用都至关重要。以下是一些最佳实践：

1. 统一时区: 在整个应用程序中，尽可能使用统一的时区（例如 UTC）。 这可以避免时区转换带来的混乱和错误。
2. 存储 UTC 时间: 无论选择 DATETIME 还是 TIMESTAMP，都应该尽可能将时间存储为 UTC 时间。 如果你使用 DATETIME，则需要在应用程序层面进行时区转换。 如果你使用 TIMESTAMP，MySQL 会自动处理时区转换。
3. 在前端进行时区转换: 在前端展示时间时，应该根据用户的时区进行转换。 这样可以确保用户看到的时间是正确的。
4. 避免在数据库中进行复杂的时区转换: 尽量避免在数据库中进行复杂的时区转换操作。 这会降低数据库的性能，并且容易出错。
5. 测试时区处理: 确保对时区处理进行充分的测试。 这可以帮助你发现和修复潜在的错误。

代码示例：在应用程序中处理时区转换 (以 Python 为例):

import datetime
import pytz

def convert_to_utc(dt, timezone):
    """将时间转换为 UTC 时间"""
    tz = pytz.timezone(timezone)
    dt_aware = tz.localize(dt)
    dt_utc = dt_aware.astimezone(pytz.utc)
    return dt_utc

def convert_from_utc(dt_utc, timezone):
    """将 UTC 时间转换为指定时区的时间"""
    tz = pytz.timezone(timezone)
    dt_aware = dt_utc.replace(tzinfo=pytz.utc)
    dt_local = dt_aware.astimezone(tz)
    return dt_local

# 示例
now = datetime.datetime.now()
local_timezone = 'Asia/Shanghai'  # 东八区

utc_time = convert_to_utc(now, local_timezone)
print(f"Local time: {now}, UTC time: {utc_time}")

local_time = convert_from_utc(utc_time, local_timezone)
print(f"UTC time: {utc_time}, Local time: {local_time}")

# 存储到数据库 (假设使用 DATETIME 类型)
# 存储 utc_time.strftime('%Y-%m-%d %H:%M:%S') 到数据库中

# 从数据库读取 (假设使用 DATETIME 类型)
# 从数据库读取的时间字符串转换为 datetime 对象
# 然后使用 convert_from_utc 函数转换为用户时区的时间

更进一步：考虑 TIMESTAMP 和 DATETIME 的结合使用

在某些复杂场景下，可以考虑 TIMESTAMP 和 DATETIME 结合使用。 例如， 可以使用 TIMESTAMP 存储数据的创建时间和修改时间，以便跟踪数据的历史变化。 同时，可以使用 DATETIME 存储用户的生日或会议时间等不需要进行时区转换的日期和时间。 这样可以充分利用两种类型的优点，并避免它们的缺点。

注意事项

1. MySQL 版本差异: 不同版本的 MySQL 在 Temporal 类型的行为上可能存在差异。 建议查阅官方文档，了解特定版本的行为。
2. 字符集: 确保数据库连接使用的字符集支持日期和时间类型。
3. 数据迁移: 在进行数据库迁移时，需要特别注意 Temporal 类型的数据转换。

DATE、DATETIME、TIMESTAMP 的总结

DATE 类型存储日期，DATETIME 存储日期和时间，TIMESTAMP 存储 UTC 时间并进行时区转换。选择哪种类型取决于应用的需求，如是否需要时区支持、存储空间限制等。正确处理时区问题至关重要，应该尽量统一时区、存储 UTC 时间并在前端进行转换。