MySQL multiple timestamp settings (RPM)

TIMESTAMP the default value for the setting is Current_timestamp

TIMESTAMP settings automatically update as the table changes is on update current_timestamp

But because

There can be at most one field set in a table Current_timestamp

It is not possible to set the default Current_timestamp on both lines.

There's one more thing to note.

CREATE TABLE' device ' (' ID ' )INT(Ten) UNSIGNED not NULLauto_increment, ' toid 'INT(Ten) UNSIGNED not NULL DEFAULT '0'COMMENT'toid', ' Createtime 'TIMESTAMP  not NULLCOMMENT'creation Time', ' UpdateTime 'TIMESTAMP  not NULL DEFAULT Current_timestampCOMMENT'Last update Time',    PRIMARY KEY(' id '),UNIQUE INDEX' toid ' (' toid ')) COMMENT='Equipment Table'COLLATE='Utf8_general_ci'ENGINE=InnoDB;

Like this setting is also not possible.

The reason is that MySQL defaults to the first TIMESTAMP field in the table (and not NULL is set) to implicitly set Defaulat Current_timestamp. So the above example is actually equivalent to setting up two current_timestamp.

Analyze requirements

In a table, there are two fields, Createtime and UpdateTime.

1 when INSERT, SQL two fields are not set, will be set to the current time

2 When update occurs, neither of the two fields in SQL is set, and the updatetime is changed to a more current time

Such a demand is not to be done. Because you can't avoid setting current_timestamp on two fields

There are several workarounds:

1 Use a trigger.

Triggers trigger time settings when insert and update.

This method is used by someone online. Of course, the usability of this method is not doubted. But for the actual scenario, it is undoubtedly to solve the small problem, adding complexity.

2 Set the default of the first timestamp to 0

The table structure is as follows:

CREATE TABLE' device ' (' ID ' )INT(Ten) UNSIGNED not NULLauto_increment, ' toid 'INT(Ten) UNSIGNED not NULL DEFAULT '0'COMMENT'toid', ' Createtime 'TIMESTAMP  not NULL DEFAULT 0COMMENT'creation Time', ' UpdateTime 'TIMESTAMP  not NULL DEFAULT Current_timestamp  on UPDATE Current_timestampCOMMENT'Last update Time',    PRIMARY KEY(' id '),UNIQUE INDEX' toid ' (' toid ')) COMMENT='Equipment Table'COLLATE='Utf8_general_ci'ENGINE=InnoDB;

In this case, the INSERT and update operations you need become:

INSERT into device set toid=11,createtime=null;

Update device set toid=22 where id=1;

Note here that the createtime of the insert operation must be set to null!!

Although I also feel that this method is very uncomfortable, but it is only a slight modification of the insert operation to the SQL statement to offload, it feels worthwhile. This is also really a way to modify the database to be minimal and to ensure the requirements. Of course, this method can be used in conjunction with the 1 method, it can reduce the number of trigger writing effect.

3 Honestly use timestamps in SQL statements.

This is the most people and the most common choice.

There are not too many designs on the table structure:

CREATE TABLE' device ' (' ID ' )INT(Ten) UNSIGNED not NULLauto_increment, ' toid 'INT(Ten) UNSIGNED not NULL DEFAULT '0'COMMENT'toid', ' Createtime 'TIMESTAMP  not NULL DEFAULT Current_timestampCOMMENT'creation Time', ' UpdateTime 'TIMESTAMP  not NULLCOMMENT'Last update Time',    PRIMARY KEY(' id '),UNIQUE INDEX' toid ' (' toid ')) COMMENT='Equipment Table'COLLATE='Utf8_general_ci'ENGINE=InnoDB;

This will require you to write a specific timestamp at the time of the insert and update operation.

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, the advantage of doing this is also one: Current_timestamp is unique to MySQL, when the database is transferred from MySQL to other databases, the business logic code is not modified.

PS: The choice of these three methods is entirely your own consideration. By the way, finally, I chose the third method.

From: http://www.cnblogs.com/yjf512/archive/2012/11/02/2751058.html Ye Jianfeng