mysql不同种类时间字段的区别

MySQL所有的时间字段

date
time
datetime
timestamp
year

我们一个一个说

测试SQL表

CREATE TABLE `mysql_time_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date_test` date DEFAULT NULL,
  `time_test` time DEFAULT NULL,
  `datetime_test` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `timestamp_test` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `year_test` year DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin;

date

MySQL的date类型的默认格式是yyyy-mm-dd

insert into mysql_time_test(date_test) VALUES(curdate());

在 MySQL 8 中，时间类型字段用于存储日期和时间信息，主要包括 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。以下是它们的详细区别及原理：

1. DATE

• 格式: YYYY-MM-DD
• 范围: 1000-01-01 到 9999-12-31
• 存储空间: 3 字节
• 用途: 仅存储日期，不包含时间部分。
• 原理: 存储从 1000-01-01 到 9999-12-31 的日期，精确到天。

2. TIME

• 格式: HH:MM:SS 或 HHH:MM:SS
• 范围: -838:59:59 到 838:59:59
• 存储空间: 3 字节
• 用途: 存储时间或时间间隔，可以超过 24 小时。
• 原理: 存储时间值，支持负值表示时间间隔。

3. DATETIME

• 格式: YYYY-MM-DD HH:MM:SS
• 范围: 1000-01-01 00:00:00 到 9999-12-31 23:59:59
• 存储空间: 5 字节（MySQL 5.6.4 之前为 8 字节）
• 用途: 存储日期和时间，不依赖时区。
• 原理: 存储从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 的日期和时间，精确到秒。

4. TIMESTAMP

• 格式: YYYY-MM-DD HH:MM:SS
• 范围: 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
• 存储空间: 4 字节
• 用途: 存储日期和时间，依赖时区，通常用于记录时间戳。
• 原理: 存储从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 的时间戳，精确到秒。存储时转换为 UTC，检索时再转换回当前时区。

5. YEAR

• 格式: YYYY
• 范围: 1901 到 2155，或 0000（表示无效年份）
• 存储空间: 1 字节
• 用途: 存储年份。
• 原理: 存储 2 位或 4 位年份，4 位年份范围为 1901 到 2155，2 位年份范围为 70 到 69（表示 1970 到 2069）。

区别总结

• 范围: DATE 和 DATETIME 的范围较大，TIMESTAMP 的范围较小。
• 时区: TIMESTAMP 依赖时区，DATETIME 不依赖。
• 存储空间: TIMESTAMP 占用空间最小，DATETIME 占用空间较大。
• 用途: DATE 用于日期，TIME 用于时间或间隔，DATETIME 和 TIMESTAMP 用于日期和时间，YEAR 用于年份。

选择建议

• 如果需要存储时间戳且依赖时区，选择 TIMESTAMP。
• 如果需要存储日期和时间且不依赖时区，选择 DATETIME。
• 如果仅需存储日期，选择 DATE。
• 如果仅需存储时间或时间间隔，选择 TIME。
• 如果仅需存储年份，选择 YEAR。

这些时间类型的选择应根据具体需求来决定，以确保数据的准确性和存储效率。