mysql5.5自动更新时间_Mysql5.5中实现createTime和updateTime自动更新

MySQL 5.5中如果同一表中两个timestamp列的默认值都设置为CURRENT_TIMESTAMP，会有以下的错误提示：

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

有下列的解决方法：

1.建表语句

create table test(

id integer not null auto_increment primary key,

name varchar(20) not null ,

created timestamp not null default '0000-00-00 00:00:00',

updated timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

);

2.插入数据方法(timestamp列为not null时，设置为null会自动更新为当前时间)

mysql> insert into test(name,created) values("litao", null);

mysql> select * from test;

+----+-------+---------------------+---------------------+

| id | name | created | updated |

+----+-------+---------------------+---------------------+

| 1 | litao | 2014-06-15 11:24:27 | 2014-06-15 11:24:27 |

+----+-------+---------------------+---------------------+

1 row in set

3.更新时

mysql> update test set name = 'lt' where id = 1;

mysql> select * from test;

+----+------+---------------------+---------------------+

| id | name | created | updated |

+----+------+---------------------+---------------------+

| 1 | lt | 2014-06-15 11:24:27 | 2014-06-15 11:28:29 |

+----+------+---------------------+---------------------+

1 row in set