mysql 1293错误建表两个timestamp

超、凢脫俗 2022-08-10 08:59 203阅读 0赞

原文地址：http://blog.163.com/user\_zhaopeng/blog/static/166022708201252323942430/

另请参考：http://lavasoft.blog.51cto.com/62575/280284/

业务场景:

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

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

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

<table style="color:rgb(0,0,0); font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; line-height:22.4px"> 
 <tbody> 
 <tr> 
 <td>ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.</td> 
 </tr> 
 </tbody> 
</table>

错误的建表语句:

<table style="color:rgb(0,0,0); font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; line-height:22.4px"> 
 <tbody> 
 <tr> 
 <td>CREATE TABLE `db1`.`sms_queue` ( &nbsp; `Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, &nbsp; `Message` VARCHAR(160) NOT NULL DEFAULT 'Unknown Message Error', &nbsp; `CurrentState` VARCHAR(10) NOT NULL DEFAULT 'None', &nbsp; `Phone` VARCHAR(14) DEFAULT NULL, &nbsp; `Created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, &nbsp; `LastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP, &nbsp; `TriesLeft` tinyint NOT NULL DEFAULT 3, &nbsp; PRIMARY KEY (`Id`) ) ENGINE = InnoDB;</td> 
 </tr> 
 </tbody> 
</table>

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

建表语句:

<table style="color:rgb(0,0,0); font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; line-height:22.4px"> 
 <tbody> 
 <tr> 
 <td>create table test_table( &nbsp; id integer not null auto_increment primary key, &nbsp; stamp_created timestamp default '0000-00-00 00:00:00', &nbsp; stamp_updated timestamp default now() on update now() );</td> 
 </tr> 
 </tbody> 
</table>

测试:

<table style="color:rgb(0,0,0); font-family:'Hiragino Sans GB W3','Hiragino Sans GB',Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; line-height:22.4px"> 
 <tbody> 
 <tr> 
 <td> <pre><code> <code style="border-width:0px; font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif; vertical-align:baseline; padding:0px; margin:0px; background-color:rgb(238,238,238)">mysql&gt; insert into test_table(stamp_created, stamp_updated) values(null, null); Query OK, 1 row affected (0.06 sec) mysql&gt; select * from t5; +----+---------------------+---------------------+ | id | stamp_created | stamp_updated | +----+---------------------+---------------------+ | 2 | 2009-04-30 09:44:35 | 2009-04-30 09:44:35 | +----+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql&gt; 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&gt; 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) </code> </code></pre> </td> 
 </tr> 
 </tbody> 
</table>

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

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

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