MySQL5.6版本之后设置DATETIME类型自动更新

时间:2024-07-11 16:06:02

在使用MySQL中datetime格式自动更新特性时,我们应该明确一点,datetime格式设置默认值为当前时间和自动更新时间是从MySQL5.6版本之后开始支持的。此前我们都是使用timestamp格式来实现自动更新时间。

一、下面是datetime与timestamp两者的简单对比

格式 时间范围 时间处理 默认值和自动更新
datetime '1000-01-01 00:00:00'~'9999-12-31 23:59:59' - 高版本支持
timestamp '1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07'UTC 从当前时区转换为UTC存储,并从UTC转回当前时区以供检索 支持

二、总结一下区别,主要是:

  • datetime的默认值为null,timestamp的默认值不为null,且为系统当前时间(current_timestatmp)。如果不做特殊处理,且update没有指定该列更新,则默认更新为当前时间。
  • datetime占用8个字节,timestamp占用4个字节。timestamp利用率更高。
  • 二者存储方式不一样,对于timestamp,在存储时从当前时区转换为UTC存储,检索时从UTC转回当前时区以供检索。但对于datetime,基本上存什么是什么。
  • 二者范围不一样。timestamp范围:‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’;datetime范围:’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。原因是,timestamp占用4字节,能表示最大的时间毫秒为2的31次方减1,也就是2147483647,换成时间刚好是2038-01-19 03:14:07.999999。

三、设置默认值和自动更新策略的方式:

DEFAULT关键字设置默认值;

ON UPDATE 关键字设置更新策略;两者互相独立。

-- 创建测试表
CREATE TABLE mytest (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
createTime DATETIME DEFAULT CURRENT_TIMESTAMP ,
updateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

如果是更新已有字段使用如下语句

 alter table tableName modify column createTime DATETIME DEFAULT CURRENT_TIMESTAMP;
alter table tableName modify column updateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

测试

-- 检测默认值,插入测试数据
-- 可以看到插入数据自动给createTime设置的默认时间的当前时间,updateTime为最后一次更新的时间
INSERT into mytest(`username`,`password`) VALUES ("root","root");

MySQL5.6版本之后设置DATETIME类型自动更新

-- 检测自动更新,更新某条数据
-- 当修改数据时,updateTime自动更新为当前时间
update mytest set username = "admin" where id = 1;

MySQL5.6版本之后设置DATETIME类型自动更新