自动生成新增/更新时间戳

为什么需要增加时间戳字段

在数据表中, 往往会增加 createtime (或者 addtime) 表示该记录插入的时间, 增加 updatetime 表示该记录最后更新的时间. 在某些公司, 后一条准则甚至是强制, 否则根本不允许创建数据表.

强制要求增加 updatetime 字段, 并在该字段上加上索引, 大概有以下用途: 1. 查看该记录的最后更新时间; 2. 查询某个时间段更新的记录; 3. 数据增量同步, 比如在大数据平台下, 增量同步到 hive 表中, 用于进一步的数据分析和挖掘.

在业务逻辑中实现

既然时间戳, 这么有用, 那么我们看看如何实现它.

最直接的想法是, 在业务逻辑中, 当 insert 一条记录时, 同时设置 createtime 和 updatetime 为当前时刻. 当 update 一条记录时, 设置 updatetime 为当前时刻.

这样的做法, 逻辑上没有问题, 但有以下弊端

一、在业务逻辑中增加了记录时间戳的逻辑, 难度倒是没有怎么增加, 但是避免不了一些同学专注于业务逻辑, 忘了时间戳这件事了.

二、变更数据只能走相应的 Dao, Service, 如果走了别的途径, 而该途径又没有设置时间戳, 这个信息又丢失了.

三、执行 update 语句, 不一定会真的更新记录, 比如下面的例子, 原表中, id=1 的记录, name 就是 "栀子花", 再次更新, 本质是不会变化的, Changed 0

用数据库自动生成

前面分析了, 在业务逻辑中时间的弊端, 这时候, 我们会想, 如果数据库能帮我们把这个事情做了该多好!

在 MySQL 5.7 以及之后的版本, 就支持啦^^ 修改表结构如下:

alter table demo_test add column createtime datetime default current_timestamp;
alter table demo_test add column updatetime datetime default current_timestamp on update current_timestamp;

再次测试, 当插入记录时, createtime, updatetime 字段会自动填充当前时间戳; 如果没有发生实质的更新, updatetime 字段保持不变; 如果发生实质的更新, updatetime 字段会自动填充当前时间戳. 这不正是我们需要的嘛!

跳坑指南

如果在你的 insert/update 语句中, 显式指明了, createtime/updatetime 的值, MySQL 是不会自动填充的, 比如下面的例子, 毕竟建表时, 设置的是当前时间戳为默认值.

可能有的同学, 根据前面的方法, 设置了 createtime/updatetime, 可是实际中并没有生效, 可能的原因就在于此, 检查一下是不是使用了 ORM 工具包, 拼接的 SQL 语句中, 包含了 set updatetime=null 这样的语句.

如果使用 MyBatis, 这里推荐一种做法, 直接删除 xml 文件中 insert/update 相关的块里面 createtime/updatetime 语句, 把这两个字段的变更完全交给 MySQL. 查询时, 可以允许这两个字段输出.