Solve and analyze the Incorrect datetime value error problem

This article will introduce how to solve the error problem of Incorrect datetime value: '0000-00-00 00:00:00'. I hope it will be helpful to everyone.

Cause of occurrence:

This error occurs in mysql 5.7 version and above. Problems that will occur:

The default sql configuration of mysql 5.7 version is :sql_mode="ONLY_FULL_GROUP_BY", this configuration strictly implements the "SQL92 standard".​​​

When many people upgrade from 5.6 to 5.7, for the sake of syntax compatibility, most of them will choose to adjust sql_mode to keep it consistent with 5.6, in order to be as compatible with the program as possible.

sql level

When sql is executed, this reason occurs: It's called target list, which is the field that follows select. There is also a place called group by column, which is the field that is followed by

group by. Since the ONLY_FULL_GROUP_BY setting is turned on, if a field does not appear in the target list

and the group by field at the same time, or is the value of an aggregate function, then this SQL query is considered illegal by MySQL and will Report an error.

Solution

Modify the mysql.ini fileAdd under the [mysqld] configuration

sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

Restart mysql

【Related recommendations:

mysql video tutorial

】

The above is the detailed content of Solve and analyze the Incorrect datetime value error problem. For more information, please follow other related articles on the PHP Chinese website!