How do I alter a mysql table column defaults?

Question

I have a table with a column of type timestamp which defaults current_timestamp and updates to current_timestamp on every update.

I want to remove the "on update" feature on this column. How do I write the alter statement?

I tried the following:

ALTER TABLE mytable alter column time  set DEFAULT now();

but this didn't work.

thanks in adavance.

Answer 1

Try one of these:

/* Change datatype to datetime; no DEFAULT, no ON UPDATE */
ALTER TABLE `mytable` CHANGE `time` `time` DATETIME DEFAULT NULL NULL;

/* Don't change datatype; DEFAULT CURRENT_TIMESTAMP, no ON UPDATE */
ALTER TABLE `mytable` CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Answer 2

You can't AFAIK use functions such as NOW() as a default.

Try

ALTER TABLE `mytable` CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

(Edited to add escaping and second use of field name)

Answer 3

Pete was almost correct but used the wrong syntax for 'change':

ALTER TABLE mytable CHANGE `time` `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Notice that you must repeat the column name. Also, make sure you are using backticks instead of single quotes to escape the column name time, which prevents it from being interpreted as the mysql column type of time.

By specifying the DEFAULT of CURRENT_TIMESTAMP, MySQL will no longer automatically update the column. From the MySQL Manual:

With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.

Answer 4

Thanks that worked. I didn't have the backticks, I had commas.