MySQL 日期和时间类型：深入解析与实战应用

本文链接：https://blog.youkuaiyun.com/xycxycooo/article/details/143096993

MySQL 日期和时间类型：深入解析与实战应用

在数据库管理中，日期和时间类型的处理是不可或缺的一部分。无论是记录用户注册时间、订单创建时间，还是跟踪系统日志，正确处理日期和时间数据都是至关重要的。MySQL 提供了多种日期和时间类型，每种类型都有其特定的用途和优势。本文将深入探讨 MySQL 中的日期和时间类型，帮助你更好地理解和应用这些数据类型。

1. 前置知识：为什么需要日期和时间类型？

在开始之前，我们先简单了解一下为什么需要专门的日期和时间类型。

数据完整性：日期和时间类型可以确保数据的完整性和一致性。例如，你不能将一个无效的日期（如2月30日）插入到数据库中。
性能优化：日期和时间类型在存储和检索时比字符串类型更高效。MySQL 内部对这些类型有专门的优化。
功能丰富：MySQL 提供了丰富的日期和时间函数，可以方便地进行日期计算、格式化等操作。

2. MySQL 日期和时间类型概览

MySQL 提供了以下几种主要的日期和时间类型：

DATE：仅存储日期（年-月-日）。
TIME：仅存储时间（小时:分钟:秒）。
DATETIME：存储日期和时间（年-月-日小时:分钟:秒）。
TIMESTAMP：类似于 DATETIME，但存储范围更小，且在插入或更新时自动更新为当前时间。
YEAR：仅存储年份。

接下来，我们将逐一详细介绍这些类型。

3. DATE 类型

DATE 类型用于存储日期，格式为 YYYY-MM-DD。它的存储范围是从 1000-01-01 到 9999-12-31。

示例：

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE
);

INSERT INTO events (id, event_name, event_date) VALUES (1, 'New Year Party', '2023-01-01');

代码解释：

event_date 列使用了 DATE 类型，存储事件的日期。
插入数据时，日期格式必须为 YYYY-MM-DD，否则会报错。

4. TIME 类型

TIME 类型用于存储时间，格式为 HH:MM:SS。它的存储范围是从 -838:59:59 到 838:59:59。

示例：

CREATE TABLE meetings (
    id INT PRIMARY KEY,
    meeting_name VARCHAR(100),
    start_time TIME
);

INSERT INTO meetings (id, meeting_name, start_time) VALUES (1, 'Daily Standup', '09:00:00');

代码解释：

start_time 列使用了 TIME 类型，存储会议的开始时间。
时间格式必须为 HH:MM:SS，否则会报错。

5. DATETIME 类型

DATETIME 类型用于存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。它的存储范围是从 1000-01-01 00:00:00 到 9999-12-31 23:59:59。

示例：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_number VARCHAR(50),
    order_date DATETIME
);

INSERT INTO orders (id, order_number, order_date) VALUES (1, 'ORD-001', '2023-10-01 14:30:00');

代码解释：

order_date 列使用了 DATETIME 类型，存储订单的日期和时间。
日期时间格式必须为 YYYY-MM-DD HH:MM:SS，否则会报错。

6. TIMESTAMP 类型

TIMESTAMP 类型类似于 DATETIME，但它存储的范围更小（从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC），并且在插入或更新记录时，会自动更新为当前时间。

示例：

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (id, username) VALUES (1, 'john_doe');

代码解释：

created_at 列使用了 TIMESTAMP 类型，并设置了默认值为当前时间。
当插入新用户时，created_at 会自动填充为当前时间。

7. YEAR 类型

YEAR 类型用于存储年份，格式为 YYYY。它的存储范围是从 1901 到 2155。

示例：

CREATE TABLE movies (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR
);

INSERT INTO movies (id, title, release_year) VALUES (1, 'Inception', 2010);

代码解释：

release_year 列使用了 YEAR 类型，存储电影的发行年份。
年份格式必须为 YYYY，否则会报错。

8. 日期和时间函数

MySQL 提供了丰富的日期和时间函数，帮助你进行各种日期计算和格式化操作。以下是一些常用的函数：

CURDATE()：返回当前日期。
CURTIME()：返回当前时间。
NOW()：返回当前日期和时间。
DATE_ADD(date, INTERVAL expr unit)：在日期上增加指定的时间间隔。
DATE_SUB(date, INTERVAL expr unit)：在日期上减去指定的时间间隔。
DATE_FORMAT(date, format)：将日期格式化为指定的格式。

示例：

SELECT 
    CURDATE() AS today,
    CURTIME() AS current_time,
    NOW() AS current_datetime,
    DATE_ADD(CURDATE(), INTERVAL 7 DAY) AS next_week,
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted_datetime;