关于TIMESTAMP和MySQL版本升级的一些坑

坑一:Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

解:在MySQL5.5发现这个问题,一个表里只能有一个TIMESTAMP的字段使用当前时间作为默认值或者作为更新值。在5.6开始接触了这个限制,官方原文如下:

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, seeAutomatic Initialization and Updating for TIMESTAMP and DATETIME.

坑二:在升级MySQL到5.7时,如果表定义中TIMESTAMP的默认值为0,那么也会报错

解:5.7的默认SQL Mode 中NO_ZERO_DATE,NO_ZERO_IN_DATE对此产生了影响,一个是禁止0,另一个禁止全0的date,形如’0000-00-00‘而我们需要做的就是在my.cnf中去除这2个mode或者新增一行不含这2个mode的sql_mode行,然后重启服务即可。

参考

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-setting

 

发表回复

您的电子邮箱地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据