自动更新时间戳

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp).

For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

mysql官方文档说明

代码示例：

1
2
3
4
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

多个timestamp

mysql中默认一张表中只能有一个timestamp类型的字段，如果有多个的话创建表的时候就会报错

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

在5.6.4之前有这个限制，在之后好像就没有这个限制了。参见https://segmentfault.com/q/1010000000488523