菜单 学习猿地 - LMONKEY

VIP

开通学习猿地VIP

尊享10项VIP特权 持续新增

知识通关挑战

打卡带练!告别无效练习

接私单赚外块

VIP优先接,累计金额超百万

学习猿地私房课免费学

大厂实战课仅对VIP开放

你的一对一导师

每月可免费咨询大牛30次

领取更多软件工程师实用特权

入驻
1967
5

MySQL 的 timestamp 和 datetime 类型比较

原创
05/13 14:22
阅读数 3063

前言

最近做项目的时候,发现对mysqltimestampdatetime 的区别认识不清,所以建了一个测试表,测试了一下区别。另外,我在社区搜索相关文章时,没有发现针对性的文章,所以写出来。

测试环境

mysql 5.7 + phpmyadmin

测试步骤

  • 插入数据时,timestampdatetime的表现
  • 更新数据时,timestampdatetime的表现
  • 更改mysql 的time_zone 值,timestampdatetime的表现

测试数据表

MySQL 的 timestamp 和 datetime 类型比较

其中字段datetime1 是datetime类型, 字段timestamp1, timestamp2, timestamp3 是timestamp类型。

  • 有以下区别:
    1. datetime类型 可以设置默认值,也可以不设置
    2. 第一个timestamp类型的默认值自动变为 CURRENT_TIMESTAMP, 约束为 ON UPDATE CURRENT_TIMESTAMPCURRENT_TIMESTAMP 是mysql 的变量,值是当前时间。
    3. 其他timestamp类型的默认值自动变为0000-00-00 00:00:00

字段默认值调整

为了有差异,我把字段timestamp3 的默认值更改为 CURRENT_TIMESTAMP。这样, 字段timestamp1和timestamp3的区别变成了ON UPDATE CURRENT_TIMESTAMPCURRENT_TIMESTAMP的区别

MySQL 的 timestamp 和 datetime 类型比较

插入数据

timestamp 类型

INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (1, now(), null, null, null);
INSERT INTO `datetest`(`id`, `datetime1`) VALUES (2, now());

结果:

MySQL 的 timestamp 和 datetime 类型比较

  • 小结:

    插入数据, timestamp类型的传值为null时, 会自动变为当前时间

更新数据

UPDATE `datetest` SET `datetime1`=now(),`timestamp1`= null ,`timestamp2`= null,`timestamp3`= null WHERE id = 1;
UPDATE `datetest` SET `datetime1`=now() WHERE id = 2;

结果:
MySQL 的 timestamp 和 datetime 类型比较

  • 小结:

    更新数据, timestamp类型没传值时,默认值为ON UPDATE CURRENT_TIMESTAMP的timestamp, 会更新为当前时间。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 为默认值的timestamp不会更新。

更改time_zone 值

set time_zone = "+9:00";
INSERT INTO `datetest`(`id`, `datetime1`, `timestamp1`, `timestamp2`, `timestamp3`) VALUES (4, now(), null, null, null);
show VARIABLES like "time_zone";

mysql 的timestamp和datetime 类型比较

MySQL 的 timestamp 和 datetime 类型比较

  • 小结:

    更改时区后, 改为+9:00时区后, datetime类型的小时 数值+1, timestamp类型的值不变。说明:timestamp存储的是UTC时间,然后显示的是根据当前时区的转换时间;datetime类型 存储的是根据操作数据当时的时区的时间。

结论

  • datetimetimestamp 类型的表现形式都为:yyyy-mm-dd hh:mm:ss ,其中datetime 占据8字节,timestamp占据4字节
  • datetime 的表示范围 '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'; timestamp的范围为'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'。
  • 插入数据时, timestamp类型的传值为null, 会自动变为当前时间
  • 更新数据时,timestamp类型没传值时,默认值为ON UPDATE CURRENT_TIMESTAMP的timestamp, 会更新为当前时间。以 CURRENT_TIMESTAMP 或者0000-00-00 00:00:00 为默认值的timestamp不会更新。
  • 更改时区后, 改为+9:00时区后, datetime类型的小时 数值+1, timestamp类型的值不变。说明:timestamp存储的是UTC时间,然后显示的是根据当前时区的转换时间;datetime类型 存储的是根据操作数据当时的时区的时间。

发表评论

0/200
1967 点赞
5 评论
收藏
为你推荐 换一批