MySQL 日期格式与类型的转换

1. 概述

日期和时间在计算机应用中是非常常见的数据类型。MySQL作为一种常用的关系型数据库，提供了丰富的日期和时间函数，以及多种日期类型和日期格式。本文将详细介绍MySQL中日期格式与类型的转换，包括日期和时间的内部存储方式、常用日期类型、日期格式化函数以及日期类型之间的转换方法等。

2. 日期和时间的内部存储方式

在MySQL中，日期和时间的内部存储方式是采用整数的形式。MySQL使用一个称为UNIX时间戳的概念，表示从1970年1月1日00:00:00开始的秒数。这种内部存储方式使得对日期和时间的计算非常方便。

3. 常用日期类型

MySQL提供了多种日期类型，包括DATE、TIME、YEAR、DATETIME和TIMESTAMP等。下面简要介绍每一种日期类型的特点和适用场景。

3.1 DATE

DATE类型表示日期，格式为YYYY-MM-DD。这种类型适合存储年、月、日的信息，例如生日、会议日期等。

3.2 TIME

TIME类型表示时间，格式为HH:MM:SS。这种类型适合存储时、分、秒的信息，例如开会时间、上班时间等。

3.3 YEAR

YEAR类型表示年份，格式为YYYY。这种类型主要用于存储年份信息。

3.4 DATETIME

DATETIME类型表示日期和时间，格式为YYYY-MM-DD HH:MM:SS。这种类型适合存储同时包含日期和时间的信息，例如订单创建时间、记录更新时间等。

3.5 TIMESTAMP

TIMESTAMP类型也表示日期和时间，格式为YYYY-MM-DD HH:MM:SS。与DATETIME类型不同的是，TIMESTAMP类型还可以自动更新为当前时间。

4. 日期格式化函数

MySQL提供了丰富的日期格式化函数，用于将日期类型转换为指定的格式。

4.1 DATE_FORMAT

DATE_FORMAT函数可以将日期类型转换为指定的格式，常用的格式有：

• %Y 年份，四位数
• %m 月份，两位数
• %d 日期，两位数
• %H 时间，24小时制，两位数
• %i 分钟，两位数
• %s 秒钟，两位数

下面是一个示例，将日期类型转换为指定的格式：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

运行结果：

2022-01-01 12:34:56

4.2 TIME_FORMAT

TIME_FORMAT函数可以将时间类型转换为指定的格式，常用的格式和上述类似。

4.3 UNIX_TIMESTAMP

UNIX_TIMESTAMP函数可以将日期类型转换为UNIX时间戳。

4.4 FROM_UNIXTIME

FROM_UNIXTIME函数可以将UNIX时间戳转换为日期类型。

5. 日期类型之间的转换

在MySQL中，可以使用CAST函数或者将日期类型直接赋值给其他日期类型的变量，来实现不同日期类型之间的转换。

5.1 DATE与DATETIME的转换

DATE类型和DATETIME类型之间的转换可以使用CAST函数，或者直接赋值给其他日期类型的变量。

-- 使用CAST函数将DATE转换为DATETIME
SELECT CAST(DATE '2022-01-01' AS DATETIME);

-- 直接赋值给DATETIME变量
SET @dt = DATE '2022-01-01';

5.2 DATE与TIMESTAMP的转换

DATE类型和TIMESTAMP类型之间的转换可以使用CAST函数，或者直接赋值给其他日期类型的变量。

-- 使用CAST函数将DATE转换为TIMESTAMP
SELECT CAST(DATE '2022-01-01' AS TIMESTAMP);

-- 直接赋值给TIMESTAMP变量
SET @ts = DATE '2022-01-01';

5.3 TIMESTAMP与DATETIME的转换

TIMESTAMP类型和DATETIME类型之间的转换可以使用CAST函数，或者直接赋值给其他日期类型的变量。

-- 使用CAST函数将TIMESTAMP转换为DATETIME
SELECT CAST(TIMESTAMP '2022-01-01 12:34:56' AS DATETIME);

-- 直接赋值给DATETIME变量
SET @dt = TIMESTAMP '2022-01-01 12:34:56';

5.4 TIMESTAMP与UNIX时间戳的转换

TIMESTAMP类型和UNIX时间戳之间的转换可以使用UNIX_TIMESTAMP函数和FROM_UNIXTIME函数。

-- 将TIMESTAMP转换为UNIX时间戳
SELECT UNIX_TIMESTAMP(TIMESTAMP '2022-01-01 12:34:56');

-- 将UNIX时间戳转换为TIMESTAMP
SELECT FROM_UNIXTIME(1640994896);

6. 总结

本文介绍了MySQL中日期格式与类型的转换，包括日期和时间的内部存储方式、常用日期类型、日期格式化函数以及日期类型之间的转换方法等。掌握这些内容可以方便地处理日期和时间类型的数据，在实际应用中发挥更大的作用。