两个mysql默认是_mysql建表设置两个默认CURRENT_TIMESTAMP的技巧

业务场景:

例如用户表,我们需要建一个字段是创建时间, 一个字段是更新时间.

解决办法可以是指定插入时间,也可以使用数据库的默认时间.

在mysql中如果设置两个默认CURRENT_TIMESTAMP,会出现这样的错误.

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

错误的建表语句:

CREATE TABLE `db1`.`sms_queue` (

`Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

`Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error',

`CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None',

`Phone` VARCHAR(14) DEFAULT NULL,

`Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

`LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,

`TriesLeft` tinyint NOT NULL DEFAULT 3,

PRIMARY KEY (`Id`)

)

ENGINE = InnoDB;

解决办法,可以使用触发器或者其他,在此还是使用数据库的方式.

建表语句:

create table test_table(

id integer not null auto_increment primary key,

stamp_created timestamp default '0000-00-00 00:00:00',

stamp_updated timestamp default now() on update now()

);

测试:

mysql>insertintotest_table(stamp_created,stamp_updated)values(null,null);QueryOK,1row affected (0.06sec)mysql>select*fromt5;+----+---------------------+---------------------+|id |stamp_created |stamp_updated |+----+---------------------+---------------------+|2|2009-04-3009:44:35|2009-04-3009:44:35|+----+---------------------+---------------------+2rows inset(0.00sec) mysql> update test_table set id = 3 where id = 2; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test_table;+----+---------------------+---------------------+| id | stamp_created | stamp_updated | +----+---------------------+---------------------+ | 3 | 2009-04-30 09:44:35 | 2009-04-30 09:46:59 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec)

解决办法是在stackoverflow看到的,原文网址: