Unfortunately, MySQL does yet not support table definitions with two different automatic timestamps. There are a few ways around this limitation.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.