mysql的timestamp自动更新和初始化

mysql的timestamp自动更新和初始化

概述

在我们设计表的时候,考虑将行数据的创建时间和最后更新时间记录下来是很好的实践。尤其是可能需要做数据同步或者对数据新鲜度有要求的表。举些应用场景,更新距上次更新超过2小时的行数据,或者是将一个月前的订单数据归档等等。我们想把这个的需求丢给数据库服务器管理,而不是在应用程序中对每一条语句设置创建时间和最后更新时间字段。在mysql中,这实现起来很容易。我们需要借助于DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP

简单示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--创建测试表
CREATE TABLE `timestampTest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
--检测默认值,插入测试数据
INSERT INTO timestampTest (name) VALUES ('aa'),('bb'),('cc');
--检测自动更新,更新某条数据
UPDATE timestampTest SET name = 'ab' WHERE id = 1;


例子非常简单,结果也很明显,我就不加赘述了。

思考

执行update语句,并未改变列值(或者说设置值为当前值),on update current_timestamp列是否会更新?

不会,大家可以看一下执行完update后出现的提示——Rows matched: 1 Changed: 0 Warnings: 0。官方文档的解释是

An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have

   

CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(),LOCALTIME,LOCALTIME(),LOCALTIMESTAMP,LOCALTIMESTAMP(),NOW()的关系?

这七个是同义词关系

Timestamp类型的默认值

我们讨论默认情况(严格模式)下mysql对timestamp类型的处理:

  1. mysql不会给timestamp设置默认值,除非显式设置default约束或者可空null。特例:mysql会给表第一个timestamp类型的字段同时添加default current_timestamp和on update timestamp
  2. 禁止mysql的特例处理有两个办法
    • 设置explicit_defaults_for_timestamp为enable
    • 显式设置该字段default或者null
  3. timestamp列默认not null。没有显式指定nullable,那么default null不合法
  4. 其他情况均会引起不合法报错

举一些例子,帮助理解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#语句不合法,出现了两个未显示设置default或null的timestamp
CREATE TABLE `tt1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`t1` timestamp ,
`t2` timestamp ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#语句合法,t1字段 not null default current_timestamp on update current_timestamp,t2可空
CREATE TABLE `tt2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`t1` timestamp ,
`t2` timestamp null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#语句不合法 t2字段没有设置default或null,也非表的第一个timestamp字段
CREATE TABLE `tt3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`t1` timestamp null,
`t2` timestamp ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#语句不合法,这个看起来貌似合法,套用我们的规则,可以发现t2字段没有显示指定null/default,尽管指定了not null也不行
CREATE TABLE `tt4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`t1` timestamp null,
`t2` timestamp not null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#语句不合法 t1,t2均合法,问题出在t3上,timestamp 默认not null,在没有显式指定null的时候,default null是不合法的
CREATE TABLE `tt5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20),
`t1` timestamp not null,
`t2` timestamp null,
`t3` timestamp DEFAULT null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

小技巧:可以使用show create table tablename 来查看mysql处理后的表定义,下面是tt2这张表的定义,验证了我们的结论

Timestamp和datetime的异同

说明 timestamp datetime
可自动更新和初始化,默认显示格式相同YYYY-MM-dd HH:mm:ss
‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
自动时区转化,实际存储毫秒数,4字节存储 不支持时区,8字节存储

参考

  1. Automatic Initialization and Updating for TIMESTAMP and DATETIME
  2. Date and Time Functions

作者: wuzhaoyang(John)
出处: http://wuzhaoyang.me/
因为作者水平有限,无法保证每句话都是对的,但能保证不复制粘贴,每句话经过推敲。希望能表达自己对于技术的态度,做一名优秀的软件工程师。