How to Set multiple TimeStamp in mysql

The Default value of timestamp is Default CURRENT_TIMESTAMP.
The timestamp is set to automatically update on update CURRENT_TIMESTAMP as the table changes.

However
A table can have at most one field set CURRENT_TIMESTAMP
You cannot set DEFAULT CURRENT_TIMESTAMP in the two rows.

Note that
Copy codeThe Code is as follows:
Create table 'device '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Toid' INT (10) unsigned not null default '0' COMMENT 'toid ',
'Createtime' timestamp not null comment' creation time ',
'Updatetime' timestamp not null default CURRENT_TIMESTAMP COMMENT 'last update time ',
Primary key ('id '),
Unique index 'toid' ('toid ')
)
COMMENT = 'device table'
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB;

This setting does not work.
The reason is that mysql sets DEFAULAT CURRENT_TIMESTAMP implicitly by default as the first timestamp Field in the table (and not null is set. So the above example is actually equivalent to setting two CURRENT_TIMESTAMP.

Analysis Requirements
A table has two fields: createtime and updatetime.
1. When an insert operation is performed, if neither of the SQL fields is set, it is set to the current time.
2. When update is performed, if neither of the SQL fields is set, updatetime changes to the current time.

Such requirements cannot be met. Because you cannot avoid setting CURRENT_TIMESTAMP on two fields

There are several solutions:
1. Use a trigger
Trigger trigger time setting during insert and update.
Someone on the Internet uses this method. Of course, we do not doubt the availability of this method. However, in actual scenarios, it is undoubtedly designed to solve small problems and increase complexity.
2. Set the default value of the first timestamp to 0.
The table structure is as follows:
Copy codeThe Code is as follows:
Create table 'device '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Toid' INT (10) unsigned not null default '0' COMMENT 'toid ',
'Createtime' timestamp not null default 0 comment' creation time ',
'Updatetime' timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'last UPDATE time ',
Primary key ('id '),
Unique index 'toid' ('toid ')
)
COMMENT = 'device table'
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB;

In this case, the insert and update operations you need are changed:
Insert into device set toid = 11, createtime = null;
Update device set toid = 22 where id = 1;

Note that the createtime of the insert operation must be set to null !!
Although I also think this method is quite uncomfortable, it is worthwhile to reduce the SQL statement by modifying the insert operation slightly. This is indeed the method to minimize database modification and ensure requirements. Of course, this method can be used together with method 1 to reduce the number of triggers.
3. Use timestamps in SQL statements.
This is the most common choice among the most people.
The table structure is not designed too much:
Copy codeThe Code is as follows:
Create table 'device '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Toid' INT (10) unsigned not null default '0' COMMENT 'toid ',
'Createtime' timestamp not null default CURRENT_TIMESTAMP COMMENT 'creation time ',
'Updatetime' timestamp not null comment' last update time ',
Primary key ('id '),
Unique index 'toid' ('toid ')
)
COMMENT = 'device table'
COLLATE = 'utf8 _ general_ci'
ENGINE = InnoDB;

In this way, you need to write a specific timestamp during the insert and update operations.
Insert device set toid = 11, createtime = '2017-11-2 10:10:10 ', updatetime = '2017-11-2 10:10:10'
Update device set toid = 22, updatetime = '2017-11-2 10:10:10 'where id = 1
On the other hand, there is also a benefit of doing so: current_timestamp is unique to mysql. When the database is transferred from mysql to other databases, the business logic Code does not need to be modified.

Ps: the trade-off between the three methods depends on your own consideration. By the way, I chose the third method.