MySQL CURRENT_TIMESTAMP on create and on update

Question

I want to define table which will have 2 TIMESTAMP fields, someting like this:

CREATE TABLE `msgs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `msg` VARCHAR(256),
    `ts_create` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ts_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

How to do this avoiding error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Point is to keep desired behavior of ts_create and ts_update in table schema.

Answer 1

I would say you don't need to have the DEFAULT CURRENT_TIMESTAMP on your ts_update: if it is empty, then it is not updated, so your 'last update' is the ts_create.

Answer 2

This is a limitation of mySQL, you cannot have two TIMESTAMP columns with defaults that reference CURRENT_TIMESTAMP. The only way to do it would be to use a DATETIME type for ts_create which unfortunately cannot have a default value of NOW(). You can roll your own trigger to make that happen though.

Answer 3

Guess this is a old post but actually i guess mysql supports 2 TIMESTAMP in its recent editions mysql 5.6.25 thats what im using as of now.