[mysql] timestamp自动更新和初始化
1.概述
在我们设计表的时候,考虑将行数据的创建时间和最后更新时间记录下来是很好的实践。尤其是可能需要做数据同步或者对数据新鲜度有要求的表。举些应用场景,更新距上次更新超过2小时的行数据,或者是将一个月前的订单数据归档等等。我们想把这个的需求丢给数据库服务器管理,而不是在应用程序中对每一条语句设置创建时间和最后更新时间字段。在mysql中,这实现起来很容易。我们需要借助于DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP
2.简单示例
--创建测试表
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;
例子非常简单,结果也很明显,我就不加赘述了。
3.思考
执行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类型的处理:
- mysql不会给timestamp设置默认值,除非显式设置default约束或者可空null。特例:mysql会给表第一个timestamp类型的字段同时添加default current_timestamp和on update timestamp
- 禁止mysql的特例处理有两个办法
- 设置
explicit_defaults_for_timestamp
为enable - 显式设置该字段default或者null
- timestamp列默认not null。没有显式指定nullable,那么default null不合法
- 其他情况均会引起不合法报错
举一些例子,帮助理解
#语句不合法,出现了两个未显示设置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 | |
异 |
自动时区转化,实际存储毫秒数,4字节存储 |
不支持时区,8字节存储 |
4.参考
1.Automatic Initialization and Updating for TIMESTAMP and DATETIME
最新文章
- [NOI2006] 最大获利
- JS原生第五篇 (帅哥)
- Android Bitmap 全面解析(四)图片处理效果对比 ...
- POJ 3411 Paid Roads(DFS)
- Windows phone 8 学习笔记(2) 数据文件操作(转)
- R统计图
- lcd ram/半反穿技术解析【转】
- JXL读取写入excel表格数据
- 记工作中的git遇到的问题
- Duanxx的STM32学习: STM32的存储映射
- Python成长之路第一篇(4)_if,for,while条件语句
- poj1207
- UVA 565 565 Pizza Anyone? (深搜 +位运算)
- java-并发之高性能对象
- [Gitlab运维系列]Gitlab 403 forbidden 并发引起IP被封
- JavaScript利用数组原型,添加方法实现遍历多维数组每一个元素
- 程序设计语言——实践之路 笔记:Beginning
- C# 获取 串口 设备名称 与 串口号 ManagementObjectSearcher类
- 行为型---中介者模式(Mediator Pattern)
- redis 学习笔记1(安装以及控制台命令)
热门文章
- 爬网页?--Chrome帮你计算XPath
- 判断JavaScript对象为null或者属性为空
- Win7上Git安装及配置过程
- [原]Android Native Debug
- 基于MSP430F413水果电池供电的低功耗时钟
- 一步一步实战扩展 ASP.NET Route,实现小写 URL、个性化 URL
- Maven进价:eclipse中集成maven
- [作业向]tinyhttp web服务器设计及完整代码
- No Dialect mapping for JDBC type: -9
- AVL树(二)之 C++的实现