5.2.3 日期和时间类型-MySQL从入门到精通(二)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(二)

### 5.2.3 日期和时间类型

在MySQL数据库中，日期和时间类型是非常重要的一类数据类型，它们用于存储日期、时间、年份、月份、日期加时间等信息。MySQL提供了多种日期和时间类型以满足不同的需求，从简单的年月日表示到复杂的日期时间加时区信息，每种类型都有其特定的应用场景和存储方式。本节将详细介绍MySQL中的日期和时间类型，包括每种类型的定义、使用场景、存储范围、格式化以及如何进行日期和时间的计算与比较。

#### 5.2.3.1 日期和时间类型概览

MySQL中的日期和时间类型大致可以分为五类：DATE、TIME、DATETIME、TIMESTAMP、YEAR。每种类型都有其特定的存储格式和用途。

1. **DATE**：仅包含日期部分，格式为YYYY-MM-DD，范围从'1000-01-01'到'9999-12-31'。
2. **TIME**：仅包含时间部分，格式为HH:MM:SS，可选包含微秒部分（即HH:MM:SS[.fraction]），时间范围从'-838:59:59'到'838:59:59'。TIME类型可以用来表示时间间隔。
3. **DATETIME**：包含日期和时间部分，格式为YYYY-MM-DD HH:MM:SS，可选包含微秒部分，范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
4. **TIMESTAMP**：也是包含日期和时间的类型，但其值是基于UTC的，格式与DATETIME相同。TIMESTAMP的范围较小，从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC（注意这里提到的2038年问题是UNIX时间戳的溢出问题，MySQL 8.0之后通过新的数据类型如DATETIME(6)扩展了时间范围）。TIMESTAMP类型常用于需要记录数据变更时间的场景，因为它会自动更新。
5. **YEAR**：仅包含年份部分，格式为YYYY，范围从1901到2155。

#### 5.2.3.2 日期和时间类型的选择

选择合适的日期和时间类型对于数据库设计至关重要。以下是一些选择时需要考虑的因素：

- **存储需求**：不同的类型占用的存储空间不同，例如，DATE类型占4个字节，DATETIME占8个字节，而TIMESTAMP在MySQL 5.6.4及以后版本中也是8个字节（之前版本为4字节）。
- **时间范围**：如果你的应用需要处理的时间范围超出了某个类型的能力，就需要选择其他类型。
- **自动更新特性**：TIMESTAMP类型具有自动更新的特性，当行的其他列（特定列）被更新时，TIMESTAMP列可以自动设置为当前的日期和时间。这对于记录数据行的最后修改时间非常有用。
- **时区考虑**：TIMESTAMP类型在存储时会自动转换为UTC，检索时会根据会话的时区设置进行转换，这对于需要跨时区处理时间的应用来说是一个重要特性。

#### 5.2.3.3 日期和时间类型的操作

MySQL提供了丰富的函数和操作符来处理日期和时间类型的数据，包括但不限于日期和时间的加减、格式化、比较等。

- **日期和时间的加减**：可以使用`DATE_ADD()`, `DATE_SUB()`, `INTERVAL`等函数或操作符来对日期和时间进行加减操作。例如，`DATE_ADD('2023-01-01', INTERVAL 1 MONTH)`将返回'2023-02-01'。
- **格式化**：`DATE_FORMAT()`函数允许你按照指定的格式显示日期和时间。例如，`DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')`将返回当前的日期和时间，格式为YYYY-MM-DD HH:MM:SS。
- **比较**：日期和时间类型的数据可以直接使用比较操作符（如=、<、>等）进行比较。
- **提取特定部分**：`YEAR()`, `MONTH()`, `DAY()`, `HOUR()`, `MINUTE()`, `SECOND()`等函数可以用来从日期时间值中提取特定的部分。

#### 5.2.3.4 注意事项

- **时区问题**：在使用TIMESTAMP类型时，要特别注意时区的影响。确保你的应用逻辑正确处理了时区转换。
- **2038年问题**：虽然MySQL 8.0之后通过扩展DATETIME类型的时间范围缓解了这一问题，但在设计需要长期存储时间数据的应用时，仍然需要关注这一潜在风险。
- **存储效率**：在选择日期时间类型时，要考虑存储效率。例如，如果只需要存储年份，使用YEAR类型会比使用DATE或DATETIME类型更节省空间。
- **默认值**：MySQL允许为日期和时间类型的列设置默认值，这可以在数据插入时提供便利。

#### 5.2.3.5 实战案例

假设我们正在设计一个订单系统，需要记录订单的创建时间、最后更新时间以及预计发货时间。我们可以这样设计数据库表：

```sql
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,  -- 订单创建日期
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 最后更新时间，自动更新
    expected_delivery_datetime DATETIME NOT NULL  -- 预计发货时间
);
```

在这个例子中，`order_date`使用了DATE类型来存储订单的创建日期，因为它不需要时间部分；`last_updated`使用了TIMESTAMP类型，并利用了它的自动更新特性来记录订单的最后一次更新时间；`expected_delivery_datetime`则使用了DATETIME类型来存储预计的发货时间，因为它需要包含具体的日期和时间信息。

通过本节的介绍，你应该对MySQL中的日期和时间类型有了更深入的了解，并能够根据实际需求选择合适的类型进行数据库设计。

该分类下的相关小册推荐：

MySQL从入门到精通(三)

MySQL从入门到精通(五)

SQL零基础到熟练应用(增删改查)

细说MySQL(零基础到高级应用)

MySQL必会核心问题

MySQL 实战 45 讲

MySQL从入门到精通(一)

MySQL8.0入门与实践

MySQL从入门到精通(四)