MySQL日期类型

Bin大约 3 分钟

MySQL日期类型

1. 不要用字符串存储日期

1.1 为什么不能用字符串存储日期？

数据校验问题：
- 使用字符串存储无法保证数据格式的正确性。
- 示例：
```
INSERT INTO orders (order_date) VALUES ('2023-13-01'); -- 不合法的日期
```
存储效率低：
- 日期类型比字符串类型占用更少的存储空间。
  - DATETIME：8 字节。
  - 字符串（CHAR(19)）：19 字节。
查询效率低：
- 字符串存储无法利用日期函数进行高效操作（如排序、范围查询）。
不易操作：
- 日期字段可以直接使用 DATE、TIME 函数，而字符串需要先解析后操作。

2. DATETIME 和 TIMESTAMP 的抉择

2.1 TIMESTAMP

特点：
1. 包含时区信息：
  - 数据在存储时会根据当前时区自动转换为 UTC。
  - 数据在查询时会根据时区转换为本地时间。
2. 占用空间小：占用 4 字节。
3. 表示范围小：
  - 范围：1970-01-01 00:00:01 到 2038-01-19 03:14:07。
  - 超过范围会报错。
适用场景：
- 需要存储与时区相关的时间（如记录用户行为日志）。
- 数据量大，存储空间有限。

2.2 DATETIME

特点：
1. 不包含时区信息：
  - 存储的是绝对时间，无时区转换。
2. 占用空间大：占用 8 字节。
3. 表示范围大：
  - 范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59。
4. 易于理解：
  - 数据直接存储，不受时区影响。
适用场景：
- 不需要考虑时区的场景（如事件的绝对时间）。
- 需要存储超出 TIMESTAMP 范围的时间（如历史数据）。

2.3 二者对比

特性	TIMESTAMP	DATETIME
时区支持	自动转换	无时区转换
存储空间	4 字节	8 字节
表示范围	1970 ~ 2038	1000 ~ 9999
查询性能	较快	较慢
适用场景	时区敏感场景	时区无关或超大范围数据

3. 数值时间戳是更好的选择吗？

数值时间戳指使用整数类型（如 BIGINT）存储时间的 UNIX 时间戳（从 1970-01-01 00:00:00 开始的秒数或毫秒数）。

3.1 优点

存储效率高：
- UNIX 时间戳使用 4 或 8 字节存储，适合存储大批量数据。
无需时区转换：
- 纯粹的绝对时间，无需考虑时区问题。
计算方便：
- 可以直接进行加减运算（如加一天 +86400）。

3.2 缺点

可读性差：
- UNIX 时间戳不直观，需要通过函数解析。
- 示例：
```
SELECT FROM_UNIXTIME(1672531200); -- 转换为 '2023-01-01 00:00:00'
```
日期范围有限：
- 与 TIMESTAMP 一样，范围有限（1970 ~ 2038）。
缺少内置日期操作支持：
- 无法直接使用 MySQL 的日期函数。

3.3 是否选择数值时间戳？

适用场景：
- 数据量非常大，对存储和查询效率有极高要求。
- 仅需简单的时间计算，无需复杂的日期操作。
不适用场景：
- 数据需要频繁进行日期计算或操作。
- 数据的可读性要求较高。

4. 总结

4.1 使用 DATETIME 的场景

需要存储超出 2038 年的时间。
时间与时区无关。
数据量适中，对存储效率要求不高。

4.2 使用 TIMESTAMP 的场景

数据需要考虑时区转换。
数据量大，存储空间有限。
时间范围在 1970-2038 之间。

4.3 使用数值时间戳的场景

数据量巨大，对存储和性能要求极高。
时间仅需简单的加减计算。

推荐做法

绝大多数场景：DATETIME + TIMESTAMP 搭配使用
- 使用 DATETIME 表示不随时区变化的时间（如事件发生的绝对时间）。
- 使用 TIMESTAMP 表示随时区变化的时间（如记录日志时间）。
性能优化场景：数值时间戳
- 在需要极致性能和简化存储时使用。
数据库设计原则
- 根据业务需求选择合适的时间类型，避免滥用字符串存储时间。
- 合理设置时区，确保时间的一致性。