MySQL中应使用datetime还是timestamp数据类型？

技术背景

在MySQL数据库中，datetime和timestamp都可用于存储日期和时间信息，但它们有着不同的特点和适用场景。了解这些差异有助于开发者根据具体需求选择合适的数据类型，避免因选择不当导致数据处理和存储上的问题。

实现步骤

1. 了解`datetime`和`timestamp`的基本特性

datetime：用于存储包含日期和时间信息的值，MySQL以YYYY-MM-DD HH:MM:SS格式检索和显示datetime值，支持的范围是1000-01-01 00:00:00到9999-12-31 23:59:59。
timestamp：通常用于跟踪记录的更改，每次记录更改时会自动更新。它的范围是1970-01-01 00:00:01 UTC到2038-01-09 03:14:07 UTC。在MySQL 5及以上版本中，timestamp值在存储时会从当前时区转换为UTC，检索时再从UTC转换回当前时区。

2. 考虑时区的影响

datetime不受时区设置的影响，而timestamp会受time_zone设置的影响。例如：

-- 查看当前时区设置
mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

-- 创建表
mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

-- 插入数据
mysql> insert into datedemo values ((now()),(now()));

-- 查询数据
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

-- 修改时区
mysql> set time_zone="america/new_york";

-- 再次查询数据
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

3. 存储空间和性能考虑

timestamp占用4个字节，datetime占用8个字节。
timestamp在数据库中占用的资源较少，索引速度更快。

4. 特殊情况的选择

仅需日期：如果只关心日期，且明确该日期适用的时区（或不关心时区），可使用DATE列类型。
记录插入时间：如果记录数据库中行的插入日期/时间，且不介意应用程序在未来可能出现问题，可使用TIMESTAMP并将默认值设置为CURRENT_TIMESTAMP()。

核心代码

使用`BIGINT`存储时间戳

-- 定义表
CREATE TEMPORARY TABLE good_times (
    a_time BIGINT
);

-- 插入特定值
INSERT INTO good_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

-- 插入默认值
ALTER TABLE good_times MODIFY a_time BIGINT DEFAULT (UNIX_TIMESTAMP());

-- 选择数据
SELECT a_time FROM good_times;

PHP中使用`BIGINT`插入数据

1 2	`$statement = $myDB->prepare('INSERT INTO good_times VALUES (?)'); $statement->execute([$someTime->getTimestamp()]);`

设置`timestamp`字段自动更新

1 2	`ALTER TABLE your_table MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;`

最佳实践

根据业务需求选择：如果需要存储特定的日期和时间值，且不受时区影响，建议使用datetime；如果需要跟踪记录的更改，或数据与系统时间相关，可使用timestamp。
考虑时区一致性：如果应用程序涉及多个时区，可将MySQL时区设置为UTC，并使用datetime，前端框架可将UTC时间转换为本地时间。
避免使用有局限性的数据类型：由于timestamp存在范围限制（到2038年），如果数据需要长期存储，建议使用BIGINT存储UNIX时间戳。

常见问题

1. `timestamp`的范围限制

timestamp的范围是1970-01-01 00:00:01 UTC到2038-01-09 03:14:07 UTC，如果需要存储更早或更晚的时间，应避免使用timestamp。

2. `timestamp`受时区影响

在不同时区的服务器或客户端访问timestamp数据时，可能会出现时间显示不一致的问题。可通过设置统一的时区或使用BIGINT存储时间戳来解决。

3. `UPDATE`语句导致`timestamp`更新

在执行UPDATE语句时，如果表中有timestamp字段，且未明确指定该字段的值，该字段会自动更新为当前时间。可通过修改表结构或在UPDATE语句中明确指定timestamp字段的值来避免。