FIX: MySQL timestamp default value 0000-00-00 00:00:00 error

I. The problem arises

When we create a table with mysql5.7, if we set the default value of timestamp (timestamp) to the form of 0000-00-00 00:00:00, an error will be generated:

  timestamp  not NULL DEFAULT ' 0000-00-00 00:00:00 ' ' Money Back Date ',

The error message is as follows:

[Err] 1067 - default  for ' repaymentdate '

two. Find out why

This problem is related to MySQL in Sql_mode, we can use two methods to view its value

Method One

MySQL>Select@ @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|+---------------+-------------------------------------------------------------------------------------------- ------------------+1Rowinch Set,1Warning (0.01Sec

Method Two

MySQL>Select@ @sql_mode;

+------------------------------------------------------------------------------------------------------------ --+| @ @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| +------------------------------------------------------------------------------------------------------------- -+ 1Rowinch Set(0.00Sec

As we can see, the default is: No_zero_in_date and no_zero_date two values, the limit field cannot be zero date

Three. Resolution of the problem

There are two ways to solve this problem online.

One is to modify the configuration file

Timestamp cannot be set to zero date and Sql_mode in mysql5.7 version

Mysql database Date, datetime type set 0000-00-00 default value error problem

However, I do not know why, modify the configuration file, but does not succeed

The second is the execution of the command

Invalid default value for ' 0000-00-00 00:00:00 ' problem in MySQL

MySQL>set session Sql_mode='only_full_group_by,strict_trans_tables, Error_for_division_by_zero,no_auto_create_user,no_engine_substitution';

The pro-test is possible:)

FIX: MySQL timestamp default value 0000-00-00 00:00:00 error