Website move MySQL 5.7 date type default value cannot set ' 0000-00-00 ' issue

Website moved, MySQL version upgraded from 5.6 to 5.7, encountered problems:

After MySQL 5.7 version datetime defaults set ' 0000-00-00 ', an exception occurred:Invalid default value for ' Create_time '

Read the Manual

The date type is used for values with a, a date part, and no time part. MySQL retrieves and displays DATE values in ' yyyy-mm-dd ' format. The supported range is ' 1000-01-01 ' to ' 9999-12-31 '.

MySQL 5.7 uses no_zero_in_date, no_zero_date two options, prohibits 0000 Such date and time, the ideal solution:

Change the date and time format from ' 1000-01-01 ' to ' 9999-12-31 '

And I was using the import and export of SQL files to move, batch replacement a little trouble, a not recommended method, in the MySQL configuration file , reset Sql_mode, remove these two.

Linux:

Modify the my.cnf file and restart MySQL.

Mac:

There is no my.cnf file by default in MacOS, and if you need to customize MySQL, copy any. cnf file in the/usr/local/mysql/support-files/directory. put it in a different directory, as modified above, renamed to My.cnf, and then copied to the/etc directory and then restarted the MySQL will be finished.

Windows:

Restart MySQL after modifying the My.ini file.

Add SQLto [mysqld] -Mode=only_full_group_by,strict_trans_ Tables,error_for_division_by_zero,no_auto_create_user,no

Ways to view Sql-mode:

The MySQL database has an environment variable sql_mode, which defines the SQL syntax that MySQL should support, data validation, etc.

Mysql> Select @ @sql_mode; +----------------------------------------------------------------+      | @ @sql_mode                                                     |      +----------------------------------------------------------------+      |Strict_trans_tables,no_auto_create_user,no_engine_substitution|      +----------------------------------------------------------------+  

viewing system Variables

    MySQL>like'sql_mode%'\g;         *************************** 1 ***************************         Variable_name:sql_mode        value:strict_trans_tables,no_auto_create_user,no_engine_substitution    

Website move MySQL 5.7 date type default value cannot set ' 0000-00-00 ' issue