timestamp類型的on update屬性的生效機(jī)制

TomLeng

在創(chuàng)建表的時候使用 `c2` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
只有當(dāng)真正有數(shù)據(jù)被改變時才會激活 ON UPDATE CURRENT_TIMESTAMP
也就是，如果新值等于舊值，MySQL是不會激活on update事件的，所以c2列也不會被更新
myisam和innodb都是如此

root:[test]> desc a;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| c1 | int(11) | YES | | NULL | |
| c2 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

root:[test]> insert a (c1) values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

root:[test]> select * from a;
+------+---------------------+
| c1 | c2 |
+------+---------------------+
| 1 | 2010-07-19 16:00:00 |
| 2 | 2010-07-19 16:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

root:[test]> update a set c1=3 where c1=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

root:[test]> select * from a;
+------+---------------------+
| c1 | c2 |
+------+---------------------+
| 3 | 2010-07-19 16:00:44 |
| 2 | 2010-07-19 16:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

root:[test]> update a set c1=2 where c1=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

root:[test]> select * from a;
+------+---------------------+
| c1 | c2 |
+------+---------------------+
| 3 | 2010-07-19 16:00:44 |
| 2 | 2010-07-19 16:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)