MySQL 5.7 Invalid default value for 'CREATE_TIME' 报错的解决方法

CREATE TABLE TOKEN
(
	TOKEN        VARCHAR(32) NOT NULL,
	CREATE_TIME  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
	REFRESH_TIME TIMESTAMP   NOT NULL,
)
	ENGINE = InnoDB
	DEFAULT CHARSET = utf8

在MySQL5.7版本的时候创建表报错了，Invalid default value for 'REFRESH_TIME'，这是因为MySQL 5.7timestamp设定默认值规则改变，不能为0000 00-00 00:00:00

解决办法：
修改sql_model，将其中的NO_ZERO_IN_DATE,NO_ZERO_DATE去掉即可。

1、当前回话修改，下次登录还是会恢复原状
mysql> show variables like 'sql_mode';
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+

mysql> set sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

查看全局的sql_model，发现NO_ZERO_IN_DATE,NO_ZERO_DATE还是存在的
mysql> show global variables like '%sql_mode%';
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+

2、全局修改

• 编辑my.cnf配置文件

  vi /etc/my.cnf

在后面加上想要的sql_model

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 重启mysql服务

  service mysqld start

• 查看sql_model
  mysql> show variables like 'sql_mode'
  -> ;
  ±--------------±-------------------------------------------------------------------------------------------------------------+
  | Variable_name | Value |
  ±--------------±-------------------------------------------------------------------------------------------------------------+
  | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  ±--------------±-------------------------------------------------------------------------------------------------------------+