mysql multiple method interpretations of TimeStamp Settings

The default value of the timestamp setting is Default CURRENT_TIMESTAMP
The timestamp setting, which is automatically updated as the table changes, is ON UPDATE CURRENT_TIMESTAMP

But because the
At most one field in a table can be set to CURRENT_TIMESTAMP
Two lines setting DEFAULT CURRENT_TIMESTAMP will not do.

One more thing to note

 
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 updated time ', 
PRIMARY KEY (`id`), 
UNIQUE INDEX `toid` (`toid`) 
) 
COMMENT=' Equipment list ' 
COLLATE='utf8_general_ci' 
ENGINE=InnoDB; 

This setting doesn't work either.
The reason is that mysql implicitly sets DEFAULAT CURRENT_TIMESTAMP by default to the first timestamp field in the table (with NOT NULL set). So setting that up is actually equivalent to setting two CURRENT_TIMESTAMP.

Analyze requirements
In a table, there are two fields, createtime and updatetime.
1 when insert, sql is not set to either field, but to the current time
2 when update, both fields in sql are not set, updatetime will be changed to the current time

The need is not there. Because you can't avoid setting CURRENT_TIMESTAMP on two fields

There are several solutions:
1 use triggers
Trigger time Settings are triggered when insert and update are used.
There are people on the Internet who use this method. There is certainly no doubt about the usefulness of this approach. But for real scenarios, there is no doubt that it adds complexity to solve small problems.
2 sets the default of the first timestamp to 0
The table structure 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 updated time ', 
PRIMARY KEY (`id`), 
UNIQUE INDEX `toid` (`toid`) 
) 
COMMENT=' Equipment list ' 
COLLATE='utf8_general_ci' 
ENGINE=InnoDB; 

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

Note here that the createtime for the insert operation must be set to null!!
While I also find this method annoying, it feels like it's worth modifying the insert operation a little bit to take the load off the sql statement. This is indeed the way to minimize database changes and guarantee requirements. Of course, this method can also be used with the 1 method to reduce the number of triggers written.
3 use the timestamp in the sql statement honestly.
This is the most popular and the most common choice
Table structure does not do too much design:

 
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 updated time ', 
PRIMARY KEY (`id`), 
UNIQUE INDEX `toid` (`toid`) 
) 
COMMENT=' Equipment list ' 
COLLATE='utf8_general_ci' 
ENGINE=InnoDB; 

This way you need to write the timestamp when inserting and update.
insert device set toid=11,createtime='2012-11-2 10:10:10',updatetime='2012-11-2 10:10:10'
update device set toid=22,updatetime='2012-11-2 10:10:10' where id=1
In fact, if you think about it, there is also a benefit: current_timestamp is mysql specific, and when the database is transferred from mysql to another database, the business logic code does not need to be modified.

ps: it's up to you to decide which of these three options to choose. By the way, 1. And finally, I'm going to go with the third method.