Mysql中默认Datatime为0时间报错

运行Sql脚本时报错：

1
[Err] 1067 - Invalid default value for 'UPDATE_TIME'

这个问题和mysql中sql_mode有关系，我们可以采用两种方法来查看它的值:

方法1:

1
SHOW VARIABLES LIKE 'sql_mode%';

返回：

1
2
3
4
5
6
+---------------+------------------------------------------------------------------------------------------+
| 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_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------+
1 row in set (0.15 sec)

方法2:

1
SELECT @@sql_mode;

返回：

1
2
3
4
5
6
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

解决方法

可以看到sql_mode中有NO_ZERO_IN_DATE,NO_ZERO_DATE两个字段，这就是罪魁祸首，我们只需要在全局设置（Global）把这两个字段给去掉就行了。

1
2
mysql> set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.03 sec)

然后重启Mysql服务，如果不想重启也可直接设置当前的sql_mode，但是一定要设置全局的sql_mode，否则关掉此终端就又恢复原样了：

1
2
mysql> set sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.05 sec)
1
2
mysql> set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.05 sec)

重新查询Sql_mode：

1
2
3
4
5
6
7
8
mysql> SHOW VARIABLES LIKE 'sql_mode%';
+---------------+------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                    |
+---------------+------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)