Good MySQL Created/Updated/Deleted defaults
Current MySQL versions (>= 5.6.5) have valid DATETIME defaults for tracking when a record was created, updated, and deleted.
Yes, you hear my saying, "FINALLY!"
For a field that has the time a record was created, use DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, it will not update when the record is changed.
For a field that should automatically update to NOW() when the record is changed, use DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
For a field that you want to manually adjust, use DATETIME, allowing it to be NULL when it wasn't modified. In this case, add WHERE deleted_at IS NULL to find records that are live, and WHERE deleted_at IS NOT NULL to find records we mark as deleted (though, clearly, they still exist in the table).
An example:
CREATE TABLE IF NOT EXISTS `collections` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `uid` INT(11) NOT NULL, `name` VARCHAR(255) NOT NULL, `description` TEXT, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `deleted_at` DATETIME, PRIMARY KEY (`id`), KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='collections';
Add new comment