Today I noticed another confusing feature of MySQL. It all started like this: I have a table with 3 columns of type TIMESTAMP. Two of them are system: dateCreated and lastModified. I created 2 triggers which fill the data with current time when the rows are inserted and updated respectively. As for the 3-rd column it is set manually. Then I noticed a funny thing - when I update a row in that table the 3-rd column will change to current time. I have managed to create a simple example of how to reproduce this:
create table test (
id integer primary key auto_increment
, value integer
, expires timestamp
);
insert into test (value,expires) values (1, '2010-10-20 10:00:00.0');
update test set value = 2 where id = 1;The expires will be updated with current datetime, but I never asked it to change. This can be cured by changing datatype to DATETIME. Perhaps I am missing something about MySQL documentation? But I am sure there is nothing in it about TIMESTAMP being automatically changed to current timestamp.
PS: MySQL version 5.0
First published on the old anydoby.com blog on 2008-04-16.