How to automatically set registered and modified dates for MySQL tables

How to automatically set registered and modified dates for MySQL tables

In MySQL 5.6.5 or later, you can automatically set the registration date and time on insert and the update date and time on update.

Values can be set to the DATETIME and TIMESTAMP types.

CREATE TABLE datesample (
  name VARCHAR(45),
  times TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  datet DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

By setting DEFAULT CURRENT_TIMESTAMP, a timestamp is set on insert.

By setting DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, a timestamp will be set on insert and the update date will also be set on update.

insert into datesample values ()

Inserting creates a record. When inserted, the date and time of registration and the date and time of update are the same.

Update name.

update datesample
set name = 'a'

You can see that only columns with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP are updated.

高橋