Set the default value of the date type to the current day

translator_bot · June 23, 2024, 2:00am

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: date类型默认值设置为当天

| username: freywan

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.2.3
[Encountered Problem] The upstream business sets a default value for the DATE type to the current day. In MySQL, this can be achieved by configuring the curdate() function to automatically get the current day’s value. The field SQL is:
create_date date DEFAULT curdate() COMMENT ‘Creation Date’
[Reproduction Path] Attempted to change the SQL syntax configuration for the DATE type default value in TiDB, but all attempts were unsuccessful.
[Problem Phenomenon and Impact]
The data table cannot add a DATE type field with a default value of the current day.
[Attachment]

translator_bot · June 23, 2024, 2:00am

| username: ealam_小羽 | Original post link

Is it a question like this?

According to the documentation, only timestamp and datetime support CURRENT_TIMESTAMP

translator_bot · June 23, 2024, 2:00am

| username: ealam_小羽 | Original post link

I tried it with MySQL, and it didn’t work either.

The function is supported by TiDB.

translator_bot · June 23, 2024, 2:00am

| username: freywan | Original post link

MySQL is fine
The version is 8.0.x

It might be a new feature of MySQL 8, it’s a headache

translator_bot · June 23, 2024, 2:00am

| username: ealam_小羽 | Original post link

It should be. I just checked and it is still at MySQL 5.7.
In this scenario, if the upstream is synchronizing to TiDB, and there are default values, can TiDB consider non-null as well? However, it might be a bit troublesome every time the table structure changes during synchronization.
TiDB is currently mainly compatible with MySQL 5.7.

translator_bot · June 23, 2024, 2:00am

| username: db_user | Original post link

Indeed, it is a new feature of 8.0. According to the official website, it should be supported starting from 8.0.13.
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

In 5.7, expressions can only be used for datetime or timestamp (excluding auto-increment).
https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html