Mysql 5.6: alter column to set default to current_timestamp

RamsonGomes — Mon, 28 Oct 2019 08:40:31 +0000

This should be so simple but I'm pulling my hair out trying to figure this out.

This example mimics the migrations I'll run - essentially it's add a new nullable column to the table, set the value to match another column, then default it to current_timestamp/not nullable going forward.

DROP TABLE IF EXISTS testing;
CREATE TABLE testing (id BIGINT(20), created_at DATETIME);
ALTER TABLE testing ADD COLUMN new_column DATETIME DEFAULT NULL;
UPDATE testing SET new_column=created_at;
ALTER TABLE testing ALTER new_column SET DEFAULT current_timestamp;
That very last command results in:

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

DEV Community: RamsonGomes

Mysql 5.6: alter column to set default to current_timestamp