MySQL v4.1 and Timestamp updates

17.07.2005 at 00:00

While developing this page i strugled over a (at least for me) strange behavior from mysql. Everytime a row is updated the first timestamp field gets updated too. This even happens when the timestamp field doesn't occur in the query. So i googled for that and first i thought it was a bug but that is actually not true it's a feature. MySQL prior to version 4.1 does this that way.

mysql> show fields from demo;
+----------+---------------+------+-----+----------------+----------------+
| Field    | Type          | Null | Key | Default        | Extra          |
+----------+---------------+------+-----+----------------+----------------+
| id       | int(11)       |      | PRI | NULL           | auto_increment |
| text     | varchar(255)  |      |     |                |                |
| created  | timestamp(14) | YES  |     | NULL           |                |
| modified | timestamp(14) | YES  |     | 00000000000000 |                |
+----------+---------------+------+-----+----------------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM demo;
+----+------+----------------+----------------+
| id | text | created        | modified       |
+----+------+----------------+----------------+
|  1 | demo | 20050724222326 | 00000000000000 |
+----+------+----------------+----------------+
1 row in set (0.00 sec)

mysql> UPDATE demo SET modified = NOW() WHERE id = 1;


mysql> SELECT * FROM demo;
+----+------+----------------+----------------+
| id | text | created        | modified       |
+----+------+----------------+----------------+
|  1 | demo | 20050724222533 | 20050724222533 |
+----+------+----------------+----------------+
1 row in set (0.00 sec)

As you can see the first timestamp field (created) gets updated too and so they both have the same value.

Comments (0)

There are currently no comments available