mysql单表多timestamp的current_timestamp设置问题

| No Comments | No TrackBacks

一个表中出现多个timestamp并设置其中一个为current_timestamp的时候经常会遇到

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

原因是当你给一个timestamp设置为on update current_timestamp的时候,其他的timestamp字段需要显式设定default值

但是如果你有两个timestamp字段,但是只把第一个设定为current_timestamp而第二个没有设定默认值,mysql也能成功建表, 但是反过来就不行...参见这里

参考文章:

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?

MySQL的timestamp类型自动更新问题

mysqldoc - timestamp

eg:

 

CREATE TABLE `users` (
  `iUId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `szUsername` varchar(45) NOT NULL,
  `szEmail` varchar(60) DEFAULT NULL,
  `szPassword` varchar(64) NOT NULL,  
  `szInsertTime` timestamp NOT NULL DEFAULT 0,
  `szLastModTime` timestamp ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `iStatus` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`iUId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

No TrackBacks

TrackBack URL: http://blog.suchasplus.com/mt/mt-tb.cgi/304

Leave a comment

About this Entry

This page contains a single entry by suchasplus published on June 9, 2011 11:21 PM.

ubuntu cli下screenshot测试 was the previous entry in this blog.

LinuxQQ在Ubuntu 11.04 natty下安装失败 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.

Pages

OpenID accepted here Learn more about OpenID
Powered by Movable Type 5.2.7