Unfortunately, MySQL does yet not support table definitions with two different automatic timestamps. There are
a few ways around this limitation.
One solution is to forego attempts at automatic timestamps, and explicitly set the time in the data manipulation statements, using the NOW() or CURRENT_DATE() functions:
INSERT INTO table1 (someData, create_time, update_time) VALUES ('someValue', NOW(), NOW()); UPDATE table1 SET someData = 'someOtherValue', update_time = NOW();
A closer step to automation is to define only the update_time as a timestamp, and explicitly set the time during creation:
INSERT INTO table1 (someData, create_time) VALUES ('someValue', NOW()); UPDATE table1 SET someData = 'someOtherValue';
In versions 4.0 and older, the first timestamp field in a table (and only that one!) would automatically update unless given some other value. Starting in version 4.1, you have a great deal more control over which field automatically updates and when.
The closest we can get with MySQL is to build a trigger on the table to automatically populate the fields:
CREATE TRIGGER table1_BI BEFORE INSERT ON table1 FOR EACH ROW NEW.create_time := NOW(), NEW.update_time := NOW(); INSERT INTO table1 (someData) VALUES ('someValue'); UPDATE table1 SET someData = 'someOtherValue';
Dig deeper on Open source databases
Related Q&A from Scott Noyes
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes.continue reading
A MySQL expert describes two ways that the multi-master support can be used.continue reading
An expert describes where to find information on detection deadlock algorithms in MySQL.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.