A question about lnmp,mysql 5.7 sql_mode.Sometimes we use the group by statement when querying data, but it will always display error messages such as STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, etc. The specific code cannot be presented, and the solution is written by memory.

mysql 5.7 starts with the following sql mode by default: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

The two options NO_ZERO_IN_DATE and NO_ZERO_DATE prohibit dates and times such as 0000. So in the mysql configuration file, reset sql-mode and remove these two items. like:

sql-mode=”ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

If mysql cannot be started

Solution

Find the mysql configuration file my.ini, find sql_mode Modify sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’