mysql5.7 datetime 默认值0000-00-00 00:00:00出错
实验环境：MySQL 5.7.17
使用wordpress的表wp_posts
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql >
CREATE
TABLE
`wp_posts` (
-> `ID`
bigint
(20) unsigned
NOT
NULL
AUTO_INCREMENT,
-> `post_author`
bigint
(20) unsigned
NOT
NULL
DEFAULT
'0'
,
-> `post_date` datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
-> `post_date_gmt` datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
-> `post_content` longtext
NOT
NULL
,
-> `post_title` text
NOT
NULL
,
-> `post_excerpt` text
NOT
NULL
,
-> `post_status`
varchar
(20)
NOT
NULL
DEFAULT
'publish'
,
-> `comment_status`
varchar
(20)
NOT
NULL
DEFAULT
'open'
,
-> `ping_status`
varchar
(20)
NOT
NULL
DEFAULT
'open'
,
-> `post_password`
varchar
(20)
NOT
NULL
DEFAULT
''
,
-> `post_name`
varchar
(200)
NOT
NULL
DEFAULT
''
,
-> `to_ping` text
NOT
NULL
,
-> `pinged` text
NOT
NULL
,
-> `post_modified` datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
-> `post_modified_gmt` datetime
NOT
NULL
DEFAULT
'0000-00-00 00:00:00'
,
-> `post_content_filtered` longtext
NOT
NULL
,
-> `post_parent`
bigint
(20) unsigned
NOT
NULL
DEFAULT
'0'
,
-> `guid`
varchar
(255)
NOT
NULL
DEFAULT
''
,
-> `menu_order`
int
(11)
NOT
NULL
DEFAULT
'0'
,
-> `post_type`
varchar
(20)
NOT
NULL
DEFAULT
'post'
,
-> `post_mime_type`
varchar
(100)
NOT
NULL
DEFAULT
''
,
-> `comment_count`
bigint
(20)
NOT
NULL
DEFAULT
'0'
,
->
PRIMARY
KEY
(`ID`),
->
KEY
`post_name` (`post_name`(191)),
->
KEY
`type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
->
KEY
`post_parent` (`post_parent`),
->
KEY
`post_author` (`post_author`)
-> ) ENGINE=innodb AUTO_INCREMENT=536
DEFAULT
CHARSET=utf8;
ERROR 1067 (42000): Invalid
default
value
for
'post_date'
|
出现以上问题：是因为mysql5.7版本后，sql_mode参数被设置了NO_ZERO_IN_DATE,NO_ZERO_DATE
|
1
2
3
4
5
6
7
|
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 |
+
---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row
in
set
(0.01 sec)
|
解决方法：
方法一：临时修改方法
|
1
2
|
mysql>
set
global
sql_mode=
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
;
mysql>
set
session sql_mode=
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
;
|
方法二：永久修改方法,需要重启mysql服务:修改mysql的配置文件my.cnf,添加以下参数
|
1
|
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION //实际是去除NO_ZERO_IN_DATE,NO_ZERO_DATE
|
方法三：修改datetime的默认值
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1913178，如需转载请自行联系原作者
