2
votes

I have table:

CREATE TABLE t1 (
    id INT(3)
  , datetime DATETIME(6)
);

I want to add default value to datetime column with fractional seconds:

ALTER TABLE 't1'
CHANGE 'datetime' 'datetime'
DATETIME DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

I get error message #1064:

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 ''t1' CHANGE 'dt' 'dt' DATETIME DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TI' at line 1

1

1 Answers

2
votes

You need to use.
Without the qoutes around the table name and column name.
I've used backticks around datetime because datetime is a keyword in MySQL.
And you have forgotten that DATETIME needs to have that fractional seconds defined like DATETIME(6)

ALTER TABLE t1
CHANGE `datetime` `datetime`
DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)