MySql的TIMESTAMP自动更新和初始化

MySql的TIMESTAMP\DATETIME自动更新和初始化

概述

很多时候，在设计表的过程中，考虑将行数据的创建时间和最后更新时间记录，希望数据库服务器自己管理，而不是在应用程序中控制创建时间和最后更新时间字段。在MySQL中，就可以借助DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 轻松实现，不过也需要一些注意的坑。

简单示例

-- 创建测试表
CREATE TABLE `timestamp_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 插入测试数据
INSERT INTO timestamp_test (name) VALUES ('t1'),('t2'),('t3');

-- 更新测试数据
UPDATE timestamp_test SET name = 't01' WHERE id = 1;

注意

1. 更新操作过程中，只有改变当前值，UPDATE CURRENT_TIMESTAMP才会被触发，也就是如果更新值是当前值，自动更新列不会更新时间，如下图：
   

2. Timestamp类型的默认值

   MySQL5.6.6加入系统变量explicit_defaults_for_timestamp控制Timestamp类型默认值控制，MySQL5.6.6默认explicit_defaults_for_timestamp变量被禁用，即默认Timestamp类型字段会有如下行为：

   • 如果TIMESTAMP列没有显式声明NULL属性，那么该列会被自动加上NOT NULL属性，如果往这个列中插入NULL值，会自动的设置该列的值为current timestamp值。(而其他类型的列如果没有被显式声明NOT NULL，那么是允许插入NULL值的)

   • 表中的第一个TIMESTAMP列，如果没有显式声明NULL属性或者没有指定默认值，也没有显式声明ON UPDATE。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。

   • 第一个TIMESTAMP列之后的其他TIMESTAMP类型列，如果没有显式声明NULL属性，也没有指定默认值，那么该列会被自动加上`DEFAULT ‘0000-00-00 00:00:00’属性。如果insert语句中没有为该列指定值，那么该列中插入’0000-00-00 00:00:00’，并且没有warning。

     但以上这些行为还受限于sql_mode,如果NO_ZERO_DATE启用了，也就是严格模式。由于TIMESTAMP列默认NOT NULL,那么第一个TIMESTAMP列之后的其他TIMESTAMP类型列,默认必须显式声明NULL或者设置默认值，而且默认值不能为NULL或0时间戳。

     不过explicit_defaults_for_timestamp变量如昙花一现，MySQL5.6.6版出现，也在MySQL5.6.6弃用，在MySQL8.0.2下explicit_defaults_for_timestamp默认开启。

参考

1. Automatic Initialization and Updating for TIMESTAMP and DATETIME