mysql 去掉on update_取消MySQL timestamp列默认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列带来的问题了。

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/