Share the pitfalls of MySQL's current_timestamp and their solutions

MySQL's current_timestamp pitfall

Report an error

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

The answers on the Internet are basically as follows, but there is no specific solution

The reason for the solution is that when you set a timestamp to on updatecurrent_timestamp, other timestamp fields need to explicitly set default values

But if you have two timestamp fields, but only set the first one to current_timestamp and the second one has no default value, MySQL can successfully create the table, but not the other way around...

My Solution

Uninstall the current lower version of MySQL and install MySQL version 5.6 or higher. MySQL version 5.7 is recommended as it is relatively stable.

mysql5.5.23 current_timestamp problem

Today I created a new table with two fields: creation time and update time. Both default values ​​are the current time. An error occurs when executing SQL.

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

I have used this SQL in other projects and it works fine. Why can't it be executed successfully today? After confirming that there were no syntax errors in the sql, I checked the information online. It turns out that MySQL 5.5 only supports one time field in a table using CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. But in 5.6 it supports multiple.

Original description

It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Solution

Remove the default creation time

Check the MySQL version number using the status command

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.