MySQL 5.7 时间不兼容问题 详解

MySQL 中 datetime, timestamp, date 的区别

日期类型	存储空间	日志格式	日期范围
datetime	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4 bytes	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
date	3 bytes	YYYY-MM-DD	1000-01-01 ~ 9999-12-31

CREATE TABLE user (

  id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '编号',

  last_login_time TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '最后登录时间');

在执行时会出现 Error : Invalid default value for 'last_login_time' 错误，这是通常是由于 MySQl 升级到 5.7+ 而引起的默认值不兼容的问题

查看 sql_mode

mysql> select @@sql_mode;

+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode                                                                                                                                             |

+--------------------------------------------------------------------------------------------------------------------------------------------------------+

| IGNORE_SPACE,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 |

+--------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

NO_ZERO_IN_DATE, NO_ZERO_DATE 这两个参数限制时间不能为0

临时解决方法 :

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

这种方式会在下次重新连接服务器时还是会出现上面那种问题，永久方案是直接修改 my.cnf 文件

# vi /etc/my.cnf

在 [mysqld] 下面添加 :

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启 MySQL

# systemctl restart mysqld