Search code examples
mysqldefaultdate-formatmysql-8.0

Mysql8.0.22 set default value DATE_FORMAT(sysdate() ,'%Y%m%d') error

Please forgive my poor English here is my sql

create table if not exists `test` (
    `client_id` varchar(18) not null default ' ',
    `begin_date` int not null default DATE_FORMAT(sysdate() ,'%Y%m%d') ,
    `end_date` int not null default DATE_FORMAT(sysdate() ,'%Y%m%d'),
unique index `uk_key` (`client_id` asc)
) engine = InnoDB  default charset = utf8 collate = utf8_bin comment = '';
commit;

In 10.3.18-MariaDB-log, there was no error reported after execution But In Mysql 8.0.22, the error

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATE_FORMAT(sysdate() ,'%Y%m%d'),
    `end_date` int not nu' at line

was reported;

I have modifid the @global.sql_mode and @sql_mode,but it didn`t work.

+-----------------------------------------------------------------------+
| @@sql_mode                                                            |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------+
| @@global.sql_mode                                                     |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+

what should i do 😵

Solution

  • Read https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

    In particular:

    The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values.

    So in MySQL, unlike MariaDB, you need to put an expression inside parentheses when using it as a DEFAULT.

    Example:

    create table if not exists `test` (
    `client_id` varchar(18) not null default ' ',
    `begin_date` int not null default (DATE_FORMAT(sysdate() ,'%Y%m%d')),
    `end_date` int not null default (DATE_FORMAT(sysdate() ,'%Y%m%d')),
unique index `uk_key` (`client_id` asc)
) engine = InnoDB  default charset = utf8 collate = utf8_bin comment = '';