一、背景介绍

在MySQL数据库中，日期和时间的管理是至关重要的，不同的应用场景对时间精度和存储格式有不同的需求，MySQL提供了多种时间类型来满足各种需求，本文将详细介绍MySQL中常用的时间类型及其特点、用法和转换方式。

二、DATE类型

1、简介

定义：DATE类型用于存储没有时间的日期，它只包含年、月、日三个部分。

存储空间：每个DATE类型的数据需要3个字节的存储空间。

取值范围：从'1000-01-01'到'9999-12-31'。

2、特性

格式：标准格式为'YYYY-MM-DD'，但MySQL允许使用一些非标准的分隔符，如'YYYY/MM/DD'、'YYYY.MM.DD'等。

零值：当插入一个不合法的日期时，系统会自动转换为'0000-00-00'。

3、典型用法

   -- 创建表并插入DATE类型数据
   CREATE TABLE example (
       id INT,
       my_date DATE
   );
   INSERT INTO example VALUES (1, '2023-10-05');
   INSERT INTO example VALUES (2, '2023全10月5日'); -- 非标准格式

三、TIME类型

1、简介

定义：TIME类型用于存储一天中的时间，不包含日期部分，它可以精确到秒，甚至小数秒。

存储空间：每个TIME类型的数据需要3个字节的存储空间。

取值范围：从'-838:59:59'到'838:59:59'。

2、特性

格式：标准格式为'HH:MM:SS'，其中HH表示小时，MM表示分钟，SS表示秒，可以包含负时间，用于表示当天的前一段时间。

零值：当插入一个不合法的时间时，系统会自动转换为'00:00:00'。

3、典型用法

   -- 创建表并插入TIME类型数据
   CREATE TABLE example (
       id INT,
       my_time TIME
   );
   INSERT INTO example VALUES (1, '12:34:56');
   INSERT INTO example VALUES (2, '-12:34:56'); -- 负时间

四、DATETIME类型

1、简介

定义：DATETIME类型用于同时存储日期和时间，它包含年、月、日、时、分、秒六个部分。

存储空间：每个DATETIME类型的数据需要8个字节的存储空间。

取值范围：从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

2、特性

格式：标准格式为'YYYY-MM-DD HH:MM:SS'。

零值：当插入一个不合法的日期时间时，系统会自动转换为'0000-00-00 00:00:00'。

3、典型用法

   -- 创建表并插入DATETIME类型数据
   CREATE TABLE example (
       id INT,
       my_datetime DATETIME
   );
   INSERT INTO example VALUES (1, '2023-10-05 12:34:56');

五、TIMESTAMP类型

1、简介

定义：TIMESTAMP类型用于存储带有时区信息的日期和时间，它与UNIX时间戳（从1970年1月1日00:00:01开始的秒数）相关联。

存储空间：每个TIMESTAMP类型的数据需要4个字节的存储空间。

取值范围：从'1970-01-01 00:00:01'UTC到'2038-01-19 03:14:07'UTC。

2、特性

格式：标准格式为'YYYY-MM-DD HH:MM:SS'。

时区：TIMESTAMP值依赖于服务器的时区设置，插入和查询时会根据当前时区进行转换。

零值：当插入一个不合法的日期时间时，系统会自动转换为'0000-00-00 00:00:00'。

自动初始化：当插入NULL时，TIMESTAMP字段会自动设置为当前时间和日期。

3、典型用法

   -- 创建表并插入TIMESTAMP类型数据
   CREATE TABLE example (
       id INT,
       my_timestamp TIMESTAMP
   );
   INSERT INTO example (id, my_timestamp) VALUES (1, NOW()); -- 插入当前时间和日期
   INSERT INTO example (id, my_timestamp) VALUES (2, '2023-10-05 12:34:56'); -- 指定时间和日期

六、YEAR类型

1、简介

定义：YEAR类型专门用于存储年份。

存储空间：每个YEAR类型的数据需要1个字节的存储空间。

取值范围：从'1901'到'2155'。

2、特性

格式：可以使用4位或2位数字表示年份，4位数字直接表示年份，如'2023'；2位数字表示两位年份，如'23'表示2023年。

零值：当插入一个不合法的年份时，系统会自动转换为'0000'。

3、典型用法

   -- 创建表并插入YEAR类型数据
   CREATE TABLE example (
       id INT,
       my_year YEAR
   );
   INSERT INTO example VALUES (1, 2023); -- 4位年份
   INSERT INTO example VALUES (2, 23); -- 2位年份，表示2023年

MySQL提供了丰富的时间类型以应对不同的应用需求，无论是简单的日期存储还是复杂的带有时区的日期时间计算，MySQL都能提供相应的支持，选择合适的时间类型不仅可以提高存储效率，还能确保数据的精确性和一致性。