Mysql multiple TimeStamp settings

Set TimeStamp for multiple timestamp in mysql. The Default value is DEFAULT CURRENT_TIMESTAMP. The value of timestamp is automatically updated as the table changes. The value is on update CURRENT_TIMESTAMP. However, because a table ON www.2cto.com has only one field set CURRENT_TIMESTAMP no. Also note that 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 requirement: A table has two fields: createtime and updatetime. 1. When the insert operation is performed, neither of the SQL fields is set, and the current time is set. 2. When the update operation is performed, neither of the SQL fields is set, updatetime cannot be changed to the current time. Because you cannot avoid setting CURRENT_TIMESTAMP on two fields. There are several solutions: 1. Use the 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: 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: 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 commonly selected TABLE structure and is NOT designed too much: 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; then you need to insert and update The specific timestamp is written during the operation. 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 in fact, 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.

Source http://www.cnblogs.com/yjf512/archive/2012/11/02/2751058.html