MySQL Workbench: how do I set "ON UPDATE" and CURRENT_TIMESTAMP?

Question

CM: Refresh timestamp column in application or database?

I am trying to simulate something like this in a Workbench, but I don’t know where to set the “ON UPDATE” part. The best I can get is the following:

-- ----------------------------------------------------- -- Table `foo`.`test` -- ----------------------------------------------------- DROP TABLE IF EXISTS `foo`.`test` ; CREATE TABLE IF NOT EXISTS `foo`.`test` ( `test_id` INT NOT NULL , `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `date_updated` TIMESTAMP NOT NULL DEFAULT 0 , PRIMARY KEY (`test_id`) ) ENGINE = InnoDB; 

Where can I go to Workbench to configure this part of ON UPDATE?

In addition, I have a rule that all timestamps stored in the database must be UTC. How to do CURRENT_TIMESTAMP, NOW, etc. UTC?

Answer 1

I am using MySQL Workbench 5.2.35. Open the panel for creating / modifying the table, go to the column tab, right-click on the timestamp field; there you can see the possible options of default and on update .

Important Note. You can use CURRENT_TIMESTAMP as the default or updated value for only one column in the table!

Regarding the UTC question, you can see this question . There is a decision made.

I would advise you to read the MySQL reference manuals, as well as for timestamp data type and NOW() .

Answer 2

Here is what I do with MySQL Workbench

Datatype: TIMESTAMP Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Answer 3

By default, MySQL sets the first timestamp field to CURRENT_TIMESTAMP for each UPDATE or INSERT . I'm starting to design my schema so that updated_at first timestamp , so I don't need to explicitly specify it.