How do I remove ON UPDATE CURRENT_TIMESTAMP from an existing column?

For your use case I think you would be better served with DATETIME, eg:

ALTER TABLE `my_table`
    CHANGE `my_col` `my_col` DATETIME NOT NULL DEFAULT NOW();

This will default to NOW() on insert, but remain unaffected on update.

See this question for a good explanation of the difference: Should I use field 'datetime' or 'timestamp'?


ALTER TABLE mytable
     CHANGE mycolumn
            mycolumn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

I believe this will reset and void the ON UPDATE. This would effectively make this definition:

CREATE TABLE mytable (
  # Other Columns
  mycolumn timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
)

Change into this one:

CREATE TABLE mytable (
  # Other Columns
  mycolumn timestamp NOT NULL default CURRENT_TIMESTAMP
)

If you wanted to reset the column entirely, you should be able to simply redefine it like:

ALTER TABLE mytable
     CHANGE mycolumn
            mycolumn TIMESTAMP NOT NULL;

Using the ideas from the other answers, and a couple of freshly installed mysql server instances, I have done a comparison of the behavior of several different CREATE and ALTER commands on 3 different server configurations:

  • mysql 5.5.45
  • mysql 5.6.26 without explicit_defaults_for_timestamp
  • mysql 5.6.26 with explicit_defaults_for_timestamp

The easiest one to explain is 5.6 with explicit_defaults_for_timestamp. Everything is sane. The timestamp type is not noticeably different from any other type. Columns created before the explicit_defaults_for_timestamp flag was turned on retain their old defaults and magic update.

In 5.5, the implicit defaults happen when a timestamp column is created (if it is the first timestamp column in the table). These are well documented already. The magic update behavior can be avoided by setting an explicit default, and then the default can be removed, leaving the column with the 3 desired attributes: non-nullable, no default, no magic update. This is the result of CREATE TABLE t (TIMESTAMP c NOT NULL DEFAULT 0) and ALTER TABLE t ALTER COLUMN c DROP DEFAULT.

This state can't be recreated with a single CREATE TABLE command, and it doesn't survive a mysqldump.

5.6 without explicit_defaults_for_timestamp is the most interesting case. It's almost the same as 5.5, but the DROP DEFAULT command is different. If you try the "create with default 0 then drop default" sequence, the magic update attribute appears as a side effect of the drop. But if you make the default CURRENT_TIMESTAMP instead of 0, then the DROP DEFAULT works without the side effect. (Must be a bug. I can't imagine any reason it would intentionally behave this way.)

Therefore this pair of commands will have the same result on all of the server configurations I tested:

ALTER TABLE t CHANGE COLUMN c c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE t ALTER COLUMN c DROP DEFAULT;

The column now has no default and no magic update.

Tags:

Mysql

Timestamp

Recent Posts