取消MySQL timestamp列默认ON UPDATE CURRENT_TIMESTAMP

来源：互联网发布：young网络注册的域名编辑：程序博客网时间：2024/05/17 01:00

这样设个初始值就可以了

`createdAt` timestamp NOT NULL DEFAULT '2017-01-01 00:00:00',

解决办法实例

DROP TABLE IF EXISTS `cd00_feihu`;CREATE TABLE `cd00_feihu` (  `id` tinyint(4) NOT NULL DEFAULT '0',  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,/*1  如果是个要变更的字段 每次修改该字段自动刷新 ,  使用这个类线,mysql帮我们更新该字段 */  `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',  `tt` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '测试时间格式',  /*2 如果是 每次修改不需要刷新 ,比如就是个新建日期 ，以后都不要修改  使用这个类线 */  `tt9` datetime DEFAULT NULL COMMENT '测试时间格式1', /* 3  如果是个要变更的字段 每次修改改行数据，该字段我们自己来判断是否需要刷新   使用这个类线   2 3 都一样 只是默认值不同而已 */  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

参考一下：

只需要为timestamp列定义默认值即可或者设置为NOT NULL

example（例子）:
CREATE TABLE cd00_feihu(
t1 TIMESTAMP,
t2 TIMESTAMP NOT NULL,
t3 TIMESTAMP DEFAULT 0,
t4 TIMESTAMP
)

参考：http://blog.csdn.net/zht666/article/details/10373923

MySQL授权用户：

grant all on *.* to root@'%' identifized by "123456";

flush privileges;

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

0 0