1> CURRENT_TIMESTAMP : 当要向数据库执行 insert操作时,如果有个 timestamp字段属性设为 CURRENT_TIMESTAMP,则无论这个字段有没有set值都插入当前系统时间
2> ON UPDATE CURRENT_TIMESTAMP : 使用 ON UPDATE CURRENT_TIMESTAMP 放在 TIMESTAMP 类型的字段后面,在数据发生更新是该字段将自动更新时间
mysql> CREATE TABLE time(
-> id INT(22) PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100),
-> time1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> time2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-> time3 TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
-> time4 TIMESTAMP DEFAULT '0000-00-00 00:00:00'
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO time(name) VALUES('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM time;
+----+------+---------------------+---------------------+---------------------+---------------------+
| id | name | time1 | time2 | time3 | time4 |
+----+------+---------------------+---------------------+---------------------+---------------------+
| 1 | a | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 2 | b | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | c | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+------+---------------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)
mysql> UPDATE time SET name='a1' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM time;
+----+------+---------------------+---------------------+---------------------+---------------------+
| id | name | time1 | time2 | time3 | time4 |
+----+------+---------------------+---------------------+---------------------+---------------------+
| 1 | a1 | 2018-03-08 02:11:03 | 2018-03-08 02:10:44 | 2018-03-08 02:11:03 | 0000-00-00 00:00:00 |
| 2 | b | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
| 3 | c | 2018-03-08 02:10:44 | 2018-03-08 02:10:44 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+----+------+---------------------+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)