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

请注意,本文编写于 2434 天前,最后修改于 121 天前,其中某些信息可能已经过时。

业务场景:

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

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

在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);Query OK,1row affected(0.06sec)mysql>select*fromt5;+----+---------------------+---------------------+|id|stamp_created|stamp_updated|+----+---------------------+---------------------+|2|2009-04-3009:44:35|2009-04-3009:44:35|+----+---------------------+---------------------+2rowsinset(0.00sec)mysql>updatetest_tablesetid=3whereid=2;Query OK,1row affected(0.05sec)Rows matched:1Changed:1Warnings:0mysql>select*fromtest_table;+----+---------------------+---------------------+|id|stamp_created|stamp_updated|+----+---------------------+---------------------+|3|2009-04-3009:44:35|2009-04-3009:46:59|+----+---------------------+---------------------+2rowsinset(0.00sec)

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

http://stackoverflow.com/questions/267658/having-both-a-created-and-last-updated-timestamp-columns-in-mysql-4-0

← 上一篇 下一篇 →
Comments

添加新评论