MySQL 中有多种数据类型可以用于日期和时间的表示，以下为 MySQL5.7 之后的日期和时间类型。

日期和时间类型字节零值最小值最大值
DATE40000-00-001000-01-019999-12-31
DATETIME80000-00-00 00:00:00:001000-01-01 00:00:009999-12-31 23:59:59
TIMESTAMP400000000000000197001010800012038 年的某个时刻
TIME300:00:00-838:59:59838:59:59
YEAR1000019012155
  • 如果要用来表示年月日，通常用 DATE 来表示
  • 如果要表示年月日时分秒，通常用 DATETIME 或 TIMESTAMP 来表。
  • 如果只用来表示时分秒则用 TIME 来表示
  • 如果只表示年份则用 YEAR

DATE,TIME,DATETIME

1
2
3
4
5
# 创建新的数据表
create table t1(d date,t time,dt datetime);
# 插入当前时间
insert into t1(now(),now(),now());
# 查看查询结果我们可以发现DATETIME就是DATE+TIME

TIMESTAMP

按照《深入浅出 MySQL 第三版》上的描述，创建字段时系统会为 TIMESTAMP 类型的字段添加NOT NULL和ON UPDATE CURRENT_TIMSTAMP 约束。但经过我个人测试(MySQL8.0.21)并没有自动添加，于是我手动加了on update CURRENT_TIMESTAMP。

刚刚查得 mysql8.0.2 开始，属性explicit_defaults_for_timestamp默认为on ，这个配置不会自动设置默认值，not null,ON UPDATE CURRENT_TIMSTAMP,使用set explicit_defaults_for_timestamp = off开启配置

MySQL5.6 之前每个表中只能有一列字段的默认值为current_timestamp。

1
alter table t1 modify ts timestamp on update CURRENT_TIMESTAMP;

下面做一个实验

1
2
3
4
5
6
7
8
9
# 查看默认时区设置,默认应为SYSTEM,既是同步系统时区,如果在中国应该为东八区
show variables like 'time_zone';
# 创建一个表
create table t(t1 timestamp,t2 datatime);
# 插入当前时间
insert into t values (now(),now());
# 修改当前时区为东九区
set time_zone = '+9:00';
# 再次查询刚刚插入的时间会发现id1的值比id2的值快了一小时，如果不是则应该检查服务器时间配置。

因此可以得出结论：TIMESTAMP 会在插入日期时将其转换为本地时区，从数据库查询时再将日期转换为本地时区后显示，因此，不同地区的用户看到的同一个日期可能是不一样的。
另外,超出可储存的上限值(溢出)会被重置为 0 值，这个例子不进行演示了。

YEAR

YEAR 类型主要用来表示年份。

  • 00 到 69 会被自动转化为 2000~2069 范围的 YEAR 值
  • 70 到 99 会被自动转化为 1970~1999 范围的 YEAR 值
  • 日期格式允许不严格语法，任意标点都允许做日期或时间的分隔符
  • YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的数字，嘉定数字对于日期类型是有意义的。例如 19830905132800 和 830905132800 都被解释为 1983-09-05 13:28:00。数字值为 6、8、12、14 位长。如果一个值为 8 或 14 位长，则假定为 YYYYMMDD 或 YYYYMMDDHHMMSS 格式，前四位数表示年。如果数字是 6 或 12 位长，则假定为 YYMMDD 或 YYMMDDHHMMSS 格式，前两位表示年。其他数字被解释为仿佛用零填充到了最近的长度。
  • 函数范围得结果，适合 DATETIME，DATE，TIME 或 TIMESTAMP 上下文，例如 NOW()或 CURRENT_DATE。