mysql中时间类型有哪些

发布时间：2022-03-18 14:28:36 作者：小新
来源：亿速云阅读：178

# MySQL中时间类型有哪些

## 引言

在数据库设计中，时间类型的选择直接影响数据存储的精确性、查询效率和业务逻辑实现。MySQL作为最流行的关系型数据库之一，提供了丰富的时间数据类型以满足不同场景需求。本文将全面解析MySQL支持的5种时间类型，包括它们的特性、存储格式、取值范围以及使用场景。

## 一、时间类型概述

MySQL 5.7及以上版本主要支持以下时间类型：

| 类型        | 格式                | 存储空间 | 取值范围                     | 零值              |
|-------------|---------------------|----------|------------------------------|-------------------|
| DATE        | YYYY-MM-DD          | 3字节    | 1000-01-01 ~ 9999-12-31      | 0000-00-00        |
| TIME        | HH:MM:SS[.fraction] | 3字节+   | -838:59:59 ~ 838:59:59        | 00:00:00          |
| DATETIME    | YYYY-MM-DD HH:MM:SS | 5字节+   | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | 0000-00-00 00:00:00 |
| TIMESTAMP   | YYYY-MM-DD HH:MM:SS | 4字节    | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC | 0000-00-00 00:00:00 |
| YEAR        | YYYY                | 1字节    | 1901 ~ 2155                  | 0000              |

## 二、各类型详解

### 1. DATE类型
**存储格式**：`YYYY-MM-DD`  
**典型场景**：生日、订单日期等不需要时间精度的场景

```sql
CREATE TABLE events (
    event_date DATE NOT NULL
);
INSERT INTO events VALUES ('2023-08-15');

特点： - 不包含时间部分 - 支持CURDATE()函数自动插入当前日期 - 可用DATE_FORMAT()函数格式化输出

2. TIME类型

存储格式：HH:MM:SS[.fraction]（最高支持6位微秒精度）

CREATE TABLE schedules (
    duration TIME(3)  -- 毫秒精度
);
INSERT INTO schedules VALUES ('12:30:15.123');

特殊特性： - 可以表示时间间隔（超过24小时的值） - 支持负数表示时间差 - CURTIME()函数获取当前时间

3. DATETIME vs TIMESTAMP

DATETIME

特点： - 不依赖时区设置 - 存储范围更大（1000-9999年） - 需要手动更新（除非设置DEFAULT）

CREATE TABLE orders (
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

TIMESTAMP

特点： - 自动转换为UTC存储，返回时转为连接时区 - 受限于2038年问题（4字节存储） - 自动更新特性（需显式设置）

CREATE TABLE logs (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

时区影响演示：

SET time_zone = '+08:00';
INSERT INTO logs VALUES (NULL);
SET time_zone = '+00:00';
SELECT * FROM logs;  -- 显示时间将不同

4. YEAR类型

优化场景：仅需存储年份时（如毕业年份）

CREATE TABLE students (
    grad_year YEAR(4)  -- 4位年份格式
);

注意事项： - YEAR(2)在MySQL 5.7+已废弃 - 输入’0’会被转换为2000

三、高级特性

1. 自动初始化与更新

MySQL 5.6.5+支持自动填充时间戳：

CREATE TABLE articles (
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 微秒精度支持

MySQL 5.7+支持最高6位微秒：

CREATE TABLE experiments (
    start_time TIMESTAMP(6)
);

3. 时间函数计算

常用时间计算示例：

-- 增加时间间隔
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- 计算差值
SELECT TIMESTAMPDIFF(HOUR, '2023-08-01 10:00', '2023-08-02 12:00');

-- 提取部分日期
SELECT EXTRACT(YEAR_MONTH FROM '2023-08-15');

四、选择建议

优先TIMESTAMP的情况：
- 需要自动时区转换
- 数据量极大需要节省空间
- 确定不会超过2038年
选择DATETIME的情况：
- 需要存储历史或未来日期（超出2038年）
- 需要稳定存储不受时区影响
存储优化技巧：
- 仅需日期时使用DATE
- 仅需年份时使用YEAR
- 超过24小时的时间间隔使用TIME

五、常见问题

Q1：TIMESTAMP的2038年问题如何解决？

A：MySQL 8.0已开始支持更长的时间范围，或可改用DATETIME

Q2：存储时区信息的最佳实践？

A：建议统一用UTC存储，应用层处理时区转换

Q3：如何选择时间精度？

A：根据业务需求，非必要不使用微秒精度（会增加存储空间）

结语

正确选择时间类型需要综合考虑存储范围、精度需求、时区处理和存储效率等因素。随着MySQL版本更新，时间类型的功能也在不断增强，建议使用较新版本以获得更完善的时间处理能力。在实际开发中，应结合业务场景进行基准测试，选择最合适的类型方案。 “`

注：本文实际约1500字，包含代码示例、对比表格和实用建议。可根据需要增减具体示例或添加性能测试数据。