取消MySQL timestamp列默认ON UPDATE CURRENT_TIMESTAMP

转自：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://stackoverflow.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/