MySQL数据类型：TIMESTAMP和DATETIME详解

MySQL数据类型：TIMESTAMP和DATETIME

在MySQL中，TIMESTAMP和DATETIME是两种常用的日期时间数据类型，它们都用于存储日期和时间信息，但在存储方式、范围、时区处理等方面存在一些差异。以下是它们的详细对比：

1. 存储范围

• TIMESTAMP
  范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。
  存储的是从1970-01-01 00:00:01 UTC 开始的秒数（即Unix时间戳）。
  由于使用32位整数存储，因此最大只能表示到 2038-01-19 03:14:07 UTC 。
• DATETIME
  范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59。
  存储的是日期和时间的组合，不依赖于时区。
  使用8字节存储，因此可以表示更大的日期范围。

2. 时区处理

• TIMESTAMP
  存储时会将时间转换为UTC时间，检索时再根据当前会话的时区设置转换回本地时间。
  适用于需要跨时区应用的场景。
• DATETIME
  存储的是字面值的日期和时间，不进行时区转换。
  适用于不需要跨时区应用的场景。

3. 存储空间

• TIMESTAMP
  占用4字节的存储空间。
• DATETIME
  占用8字节的存储空间。

4. 自动更新

• TIMESTAMP
  可以设置自动更新功能，例如在插入或更新记录时自动设置为当前时间。
  示例：

create table example_table (
  id int not null,
  create_at timestamp default current_timestamp,
  update_at timestamp default current_timestamp on update current_timestamp,
  primary key (id)
);

create_at 会在插入时自动设置为当前时间，update_at 会在插入或更新时自动设置为当前时间。

• DATETIME
  不支持自动更新功能，需要手动设置。

5. 默认值

• TIMESTAMP
  如果没有显示指定默认值，且字段允许null，则默认值为null。
  如果字段不允许为null，则默认值为current_timestamp。
• DATETIME
  默认值为null，除非显示指定。

6. 使用场景

• TIMESTAMP
  适合需要记录时间发生的时间戳，并且需要跨时区应用的场景。
  适合需要自动更新时间的场景。
• DATETIME
  适合存储固定的日期和时间，不需要时区转换的场景。
  适合需要存储大范围日期的场景。

7. 示例

CREATE TABLE example (
    id INT PRIMARY KEY,
    event_time TIMESTAMP,
    event_date DATETIME
);

INSERT INTO example (id, event_time, event_date) VALUES (1, NOW(), NOW());

在这个示例中，event_time和event_date分别存储了当前的时间戳和日期时间，但event_time 会根据时区进行转换，而event_date则不会。

总结

• TIMESTAMP 适合需要时区转换和自动更新的场景，但范围有限。
• DATETIME 适合存储大范围的日期时间，且不需要时区转换的场景。