转自:http://feihu.cd00.cn/itweb/index.php/mysql-timestamp-disable-on-update-current_timestamp
Q:don't want timestamp auto update time when record update?
我想取消timestamp列默认为设置成记录被更新时的时间戳?
A:you should disable ON UPDATE CURRENT_TIMESTAMP for timestamp column.
你应该取消timestamp列的ON UPDATE CURRENT_TIMESTAMP属性。
Q:how to disable ON UPDATE CURRENT_TIMESTAMP?
如何才能取消ON UPDATE CURRENT_TIMESTAMP的默认设置呢?
A:just define the default value for timestamp or just define it not null
只需要为timestamp列定义默认值即可或者设置为NOT NULL
example(例子):
CREATE TABLE cd00_feihu(
t1 TIMESTAMP,
t2 TIMESTAMP NOT NULL,
t3 TIMESTAMP DEFAULT 0,
t4 TIMESTAMP
)
after execute, the source of the table will be(执行后,表的SQL代码将会变成下面的样子)
CREATE TABLE cd00_feihu(
t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
t2 TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
t3 TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
t4 TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00′
) ENGINE=INNODB DEFAULT CHARSET=gbk
another solution(另一个解决方案):
update cd00_feihu set t1=t1, [other column…]
feihu it-web 2012-5-10
and if you already create the table and it have the on update current_timestamp,how to alter table to remove it,here it is
如果已经设置成了on update current_timestamp,那么如何修改表来移除这个特性呢,下面就是解决办法:
case 1:
ALTER TABLE `mydb`.`cd00_feihu` CHANGE `t1` `t1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
case 2:
ALTER TABLE `mydb`.`cd00_feihu` CHANGE `t1` `t1` TIMESTAMP DEFAULT NULL NULL;
ALTER TABLE `mydb`.`cd00_feihu` CHANGE `t1` `t1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
解决MySQL的这个问题的简单办法是:尽量使用datetime代替timestamp列,这样就能避免多个同一个表中timestamp列带来的问题了。
我的依据是:http://*.com/questions/409286/datetime-vs-timestamp
Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.
关于datetime和timestamp最重要的区别是:timestamp带有时区,而datetime没有时区,datetime只是一个固定的时间。如下例子:
mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone | Asia/Calcutta |
+------------------+---------------------+
mysql> create table datedemo(
-> mydatetime datetime,
-> mytimestamp timestamp
-> );
mysql> insert into datedemo values ((now()),(now()));
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
mysql> set time_zone="america/new_york";
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+
The above examples shows that how TIMESTAMP date type changed the values after changing the time-zone to 'america/new_york' where DATETIMEis unchanged.
I've converted my answer into article so more people can find this useful.
http://www.tech-recipes.com/rx/22599/mysql-datetime-vs-timestamp-data-type/