Why Am I Getting \'Invalid Default Value for \'create_date\' Timestamp Field\' Error in MySQL?

Invalid Default Value for 'create_date' Timestamp Field

When creating a table with a timestamp column and specifying a default value of '0000-00-00 00:00:00', an error may occur indicating "Invalid default value for 'create_date'". This error is caused by MySQL's SQL Mode - NO_ZERO_DATE.

According to the MySQL reference manual, NO_ZERO_DATE prevents the insertion of '0000-00-00' as a valid date in strict mode. In this case, the 'create_date' column was defined as a timestamp and assigned a default value of '0000-00-00 00:00:00'. However, SQL Mode - NO_ZERO_DATE prohibits such values.

To resolve this error, consider the following options:

• Disable SQL Mode - NO_ZERO_DATE using the SET sql_mode="" command before creating the table.
• Use a different default value for the 'create_date' column that is not '0000-00-00 00:00:00', such as 'CURRENT_TIMESTAMP'.
• Insert zero dates using the IGNORE option, which allows the insertion of such values with a warning instead of an error.

The above is the detailed content of Why Am I Getting \'Invalid Default Value for \'create_date\' Timestamp Field\' Error in MySQL?. For more information, please follow other related articles on the PHP Chinese website!