freshsauce web development

MySQL/MariaDB auto update multiple timestamp fields

Prior to MySQL < 5.6.5 only one DATETIME or TIMESTAMP field in a table could be updated automatically on INSERT or UPDATE.

From MySQL >=5.6.5 this restriction went away, so it’s now possible to have both a created_at and modified_at field for example (you can have more) and have MySQL default both on INSERT to the current date time and on UPDATE set the modified_at equally to the current date and time.


// MySQL >=5.6.5

ALTER TABLE the_table ADD modified_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE the_table ADD created_at datetime DEFAULT CURRENT_TIMESTAMP;

The only way to automate this functionality with MySQL < 5.6.5 was to use triggers.


// MySQL < 5.6.5

ALTER TABLE the_table ADD modified_at TIMESTAMP;
ALTER TABLE the_table ADD created_at datetime NOT NULL;


DELIMITER |
DROP TRIGGER IF EXISTS the_table_insert_trigger//
CREATE TRIGGER the_table_insert_trigger
BEFORE INSERT ON the_table
FOR EACH ROW
BEGIN
IF NEW.created_at = '0000-00-00 00:00:00' THEN
SET NEW.created_at = NOW();
END IF;
END;
END;
|
DELIMITER ;