Why Doesn't CURRENT_DATE/CURDATE() Work as a Default Value for MySQL DATE Columns?

Default Date Value Issues with CURRENT_DATE/CURDATE()

In the MySQL world, you may encounter a seemingly straightforward question: why doesn't CURRENT_DATE or CURDATE() work as the default value for a DATE column?

The Riddle: Why Doesn't It Work?

As you would expect, this code should work without issues:

CREATE TABLE INVOICE(
   INVOICEDATE DATE NOT NULL DEFAULT CURRENT_DATE
)

However, much to our surprise, it doesn't. What's the reason behind this conundrum?

The Answer: A MySQL Limitation

The answer lies in a limitation within MySQL. Despite being intuitive, setting the default value of a DATE column to CURRENT_DATE or CURDATE() is not supported.

According to the MySQL documentation, default values must be constants rather than functions or expressions. This eliminates the possibility of using these functions to dynamically set default dates.

The Exception: CURRENT_TIMESTAMP

The exception to this rule is CURRENT_TIMESTAMP. This function can be set as the default value for a TIMESTAMP column. However, it's important to note that it represents the current moment, not a specific date.

UPDATE: In MySQL 8.0.13, there has been a significant change. CURRENT_DATE and CURDATE() are now supported as default values for DATE columns. This update addresses the limitation discussed in this article.

The above is the detailed content of Why Doesn't CURRENT_DATE/CURDATE() Work as a Default Value for MySQL DATE Columns?. For more information, please follow other related articles on the PHP Chinese website!