每日MySQL之013：TIMESTAMP和DATETIME列的自动初始化与自动更新

TIMESTAMP 和 DATETIME 列可以自动地被初始化和更新为当前的日期/时间。

对于 TIMESTAMP 和 DATETIME 列，可以把当前时间戳设置为其默认值、自动更新值或者两者都设置:
--对于auto-initialized 列，如果新插入的记录没有指定值，那么设置其值为当前时间戳。
--对于auto-updated 列，如果这条记录的其他列的值发生变化，那么这一列就会被更新为发生变化时的时间戳。如果不想被更新，需要显式地指定当前值。

如何开启：
要想设置这个属性，可以在列定义的时候，指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP ，两者出现的顺序随意。

测试1：自动初始化和自动更新
mysql> CREATE TABLE t1 (
    -> id int, 
    -> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> );

mysql> INSERT INTO t1 VALUES (1, default, default);
mysql> INSERT INTO t1(id) VALUES (2);
mysql> select * from t1;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
|    2 | 2017-08-07 02:18:41 | 2017-08-07 02:18:41 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
插入数据时，选择了默认值，被自动初始化为插入时的时间戳。

mysql> update t1 set id=id*2 where id = 2;
mysql> select * from t1;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    1 | 2017-08-07 02:18:37 | 2017-08-07 02:18:37 |
|    4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第二条记录的id字段，导致第二条记录的ts列和dt列被自动更新

mysql> update t1 set id = id * 2 where id = 1;
mysql> select * from t1;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    2 | 2017-08-07 02:19:28 | 2017-08-07 02:19:28 |
|    4 | 2017-08-07 02:19:10 | 2017-08-07 02:19:10 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新第一条记录的id字段，导致第一条记录的ts列和dt列被自动更新

测试2：只设置自动初始化
mysql> CREATE TABLE t2 (
    -> id int, 
    -> ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -> dt DATETIME DEFAULT CURRENT_TIMESTAMP
    -> );

mysql> INSERT INTO t2 VALUES (1, default, default);
mysql> INSERT INTO t2(id) VALUES (2);
mysql> select * from t2;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
|    2 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> update t2 set id=id*2 where id = 2;
mysql>   select * from t2;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    1 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
|    4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
ts列和td列只设置了自动初始化，所以当其他字段变化时，不会自动更新

mysql> update t2 set id = id * 2 where id = 1;
mysql> select * from t2;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    2 | 2017-08-07 02:19:51 | 2017-08-07 02:19:51 |
|    4 | 2017-08-07 02:19:55 | 2017-08-07 02:19:55 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)

示例3：只设置自动更新
Database changed
mysql> CREATE TABLE t3 (
    -> id int, 
    -> ts TIMESTAMP DEFAULT '1997-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP,
    -> dt DATETIME  DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.97 sec)

mysql> INSERT INTO t3 VALUES (1, default, default);
mysql> INSERT INTO t3(id) VALUES (2);
mysql> select * from t3;
+------+---------------------+------+
| id   | ts                  | dt   |
+------+---------------------+------+
|    1 | 1997-01-01 00:00:01 | NULL |
|    2 | 1997-01-01 00:00:01 | NULL |
+------+---------------------+------+
2 rows in set (0.00 sec)
ts列被初始化为指定的默认值，dt列被初始化为NULL.

mysql> update t3 set id=id*2 where id = 2;
mysql> select * from t3;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    1 | 1997-01-01 00:00:01 | NULL                |
|    4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第二条记录，第二条记录的ts列和dt列被自动更新

mysql> update t3 set id = id * 2 where id = 1;
mysql> select * from t3 ;
+------+---------------------+---------------------+
| id   | ts                  | dt                  |
+------+---------------------+---------------------+
|    2 | 2017-08-07 02:21:30 | 2017-08-07 02:21:30 |
|    4 | 2017-08-07 02:21:14 | 2017-08-07 02:21:14 |
+------+---------------------+---------------------+
2 rows in set (0.00 sec)
更新了第一条记录，第一条记录的ts列和dt列被自动更新

DB2中也有类似的功能：

db2 "create table t1(id int, UPDATETIME TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)"
db2 "insert into t1(id) values(100)"
db2 "insert into t1(id) values(200)"

db2 "select * from t1"

ID          UPDATETIME                
----------- --------------------------
        100 2017-08-07-02.47.52.576055
        200 2017-08-07-02.47.53.894663

  2 record(s) selected.

db2 "update t1 set id = id+1 where id < 200"

db2 "select * from t1"

ID          UPDATETIME                
----------- --------------------------
        101 2017-08-07-02.48.25.674785
        200 2017-08-07-02.47.53.894663

  2 record(s) selected.

注意一点，DB2里使用的是时间并非真正意义上的操作系统时间，而是 VTS时间，也就是说，如果你将时间调整到未来，再调整回来，则UPDATETIME的时间戳保持在未来，而非当下。