TIMESTAMP 和 DATETIME 是 MySQL 中用于存储日期和时间的两种常用数据类型。尽管它们看起来相似，但在实际使用中存在一些重要的区别。以下是两者的详细比较，并结合代码示例进行说明。
1. 定义与存储范围
DATETIME：用于存储从1000-01-01 00:00:00到9999-12-31 23:59:59的日期和时间。它不受时区影响，存储的是实际的日期和时间值。TIMESTAMP：用于存储从1970-01-01 00:00:01UTC 到2038-01-19 03:14:07UTC 的日期和时间。它会受时区影响，存储的是从1970-01-01 00:00:01UTC 开始的秒数。
2. 存储大小
DATETIME：占用8个字节。TIMESTAMP：占用4个字节。
3. 时区处理
DATETIME：不随时区变化，存储和检索时的值是相同的。TIMESTAMP：随时区变化，存储时会转换为UTC时间，检索时会转换回当前会话的时区。
4. 自动初始化和更新
DATETIME：不能自动初始化或更新，但可以通过触发器实现类似功能。TIMESTAMP：可以通过CURRENT_TIMESTAMP设置为自动初始化和更新。
5. 使用场景
DATETIME：适用于需要存储不受时区影响的日期和时间的场景，例如出生日期、事件日期等。TIMESTAMP：适用于需要存储和比较与时区相关的日期和时间的场景，例如日志记录、最后修改时间等。
6. 示例代码
以下是一些示例代码，展示了如何使用 DATETIME 和 TIMESTAMP 数据类型创建表，并插入和检索数据。
创建表
CREATE DATABASE test_db;
USE test_db;
-- 创建包含 DATETIME 和 TIMESTAMP 字段的表
CREATE TABLE date_time_example (
dt_col DATETIME NOT NULL,
ts_col TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
插入数据
-- 插入数据
INSERT INTO date_time_example (dt_col, ts_col) VALUES ('2023-10-05 15:30:00', '2023-10-05 15:30:00');
检索数据
-- 检索数据
SELECT * FROM date_time_example;
更新数据
-- 更新数据
UPDATE date_time_example SET dt_col = '2023-10-06 16:45:00' WHERE dt_col = '2023-10-05 15:30:00';
检索结果
初次插入数据后的检索结果：
+---------------------+---------------------+
| dt_col | ts_col |
+---------------------+---------------------+
| 2023-10-05 15:30:00 | 2023-10-05 15:30:00 |
+---------------------+---------------------+
更新数据后的检索结果（假设更新发生在不同的时间）：
+---------------------+---------------------+
| dt_col | ts_col |
+---------------------+---------------------+
| 2023-10-06 16:45:00 | 2023-10-06 16:45:00 |
+---------------------+---------------------+
更详细的示例：用户活动日志
以下是一个更详细的示例，展示了如何在用户活动日志中使用 DATETIME 和 TIMESTAMP。
创建用户活动表
CREATE TABLE user_activity (
activity_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
activity_description VARCHAR(255),
activity_time DATETIME NOT NULL, -- 活动发生的时间，不受时区影响
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 记录创建时间，自动初始化
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 记录更新时间，自动更新
);
插入用户活动数据
-- 插入用户活动数据
INSERT INTO user_activity (user_id, activity_description, activity_time)
VALUES
(1, 'User logged in', '2023-10-05 15:30:00'),
(2, 'User updated profile', '2023-10-05 16:00:00');
检索用户活动数据
-- 检索用户活动数据
SELECT * FROM user_activity;
更新用户活动数据
-- 更新用户活动描述
UPDATE user_activity SET activity_description = 'User logged out' WHERE user_id = 1;
检索结果
初次插入数据后的检索结果：
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
| activity_id | user_id | activity_description | activity_time | create_time | update_time |
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
| 1 | 1 | User logged in | 2023-10-05 15:30:00 | 2023-10-05 15:30:00 | 2023-10-05 15:30:00 |
| 2 | 2 | User updated profile | 2023-10-05 16:00:00 | 2023-10-05 16:00:00 | 2023-10-05 16:00:00 |
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
更新数据后的检索结果（假设更新发生在不同的时间）：
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
| activity_id | user_id | activity_description | activity_time | create_time | update_time |
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
| 1 | 1 | User logged out | 2023-10-05 15:30:00 | 2023-10-05 15:30:00 | 2023-10-06 17:00:00 |
| 2 | 2 | User updated profile | 2023-10-05 16:00:00 | 2023-10-05 16:00:00 | 2023-10-05 16:00:00 |
+-------------+---------+-----------------------+---------------------+---------------------+---------------------+
小结
DATETIME：适用于存储不受时区影响的具体日期和时间，常用于表示某个特定时刻的事件，如生日、会议时间等。TIMESTAMP：适用于存储与时区相关的日期和时间，常用于记录数据的创建和更新时间，便于在不同时间区域进行时间转换和比较。
通过理解和选择合适的日期时间数据类型，可以优化数据库的性能，确保数据的准确性和一致性。上述示例展示了如何在实际应用中使用 DATETIME 和 TIMESTAMP 设计数据库表，并插入、更新和检索数据。
