timestamp类型的on update属性的生效机制

TomLeng

在创建表的时候使用 `c2` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
只有当真正有数据被改变时才会激活 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)