0

My environment

Mysql 8.0.25

What I want to do

I want to use alter column clause to set default value for my datetime column

Mysql 8 document says that after set default comes literal or expression https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

...
| ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }

What I tried

so I did below things but none of them was successful.

alter table my_table alter column my_datetime_column set default CURRENT_TIMESTAMP();
alter table my_table alter column my_datetime_column set default NOW();
alter table my_table alter column my_datetime_column set default CURRENT_TIMESTAMP;
alter table my_table alter column my_datetime_column set default NOW;

The question

I know I can set default by change column clause. but why not with alter column? Is there something I got wrong about the documentation?

med benzekri
  • 490
  • 6
  • 19
Jerry
  • 399
  • 1
  • 2
  • 17

1 Answers1

2

This appears to be a strange gap in the MySQL parser.

For years, MySQL column defaults were required to be either a literal scalar value, or else NULL. Expressions were not supported.

MySQL 8.0 additionally supports the option of using an expression for the default, but it is required syntax that the expression is in parentheses.

CREATE TABLE MyTable (
  a INT,
  b INT,
  c INT DEFAULT a+b,  -- ERROR
  d INT DEFAULT (a+b) -- CORRECT
);

MySQL timestamp/datetime columns have a special syntax that has been supported since early versions, allowing the keyword CURRENT_TIMESTAMP instead of a literal value. No parentheses required. See https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

But this does not appear to be supported by the ALTER TABLE...ALTER COLUMN statement:

alter table mytable alter column dt set default current_timestamp;

ERROR 1064 (42000): 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 'current_timestamp' at line 1

However if you use MySQL 8.0, you can use CURRENT_TIMESTAMP if you follow the newer expression-default syntax, and put it in parentheses:

alter table mytable alter column dt set default (current_timestamp);

Query OK, 0 rows affected (0.01 sec)

You can also use the traditional syntax without parentheses, but not with ALTER COLUMN. You have to use the syntax of MODIFY COLUMN, which requires you to specify the column's data type and nullability as well.

alter table mytable modify column dt datetime not null default current_timestamp;

Query OK, 0 rows affected (0.01 sec)

This is just an odd inconsistency in the SQL parser. The special support for CURRENT_TIMESTAMP as a default was not extended to the ALTER COLUMN clause. This has been reported as a bug, but it was rejected: https://bugs.mysql.com/bug.php?id=31452

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828