MySQL下，如何同时使用两个timestamp类型的字段？

Question

现在：
ctime记录创建的时间戳, defaul null
utime记录更新的时间戳, default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

使用的插入语句
insert on duplicate key update
它会同时修改ctime和utime

Answer 1

我想你说的是这个:

mysql 5.6.4 doc:
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time.
This restriction has been lifted. Any TIMESTAMP column definition can
have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE
CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used
with DATETIME column definitions. For more information, see Automatic
Initialization and Updating for TIMESTAMP and DATETIME.

这是一个mysql 5.6.4之前的limitation. 我在5.5.24上试了,

mysql> create table mytime (id int, ctime timestamp default current_timestamp, u
time timestamp default current_timestamp on update current_timestamp);

会报:

RROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

5.6.12上没有问题.

如果想在mysql 5.6.4之前用这个功能, 自己去弄一个trigger吧.

Answer 2

工作中碰到了这么个问题。

我们目前的解决方式是created_ts 设置为default(固定值)，updated_ts 设置为current（每次自动更新） 。在插入的时候，created_ts 显式设置而不使用默认值。