当前位置: 动力学知识库 > 问答 > 编程问答 >

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

问题描述:

I did a dump of a mysql 5.5 database and loaded it into a 5.6 server.

The dump added ON UPDATE CURRENT_TIMESTAMP to a bunch of columns that didn't have it previously.

I'm searching for an ALTER TABLE statement that will remove the ON UPDATE CURRENT_TIMESTAMP rule without making any other changes. In my imagination it should be something like ON UPDATE NOOP or ON UPDATE NO_CURRENT_TIMESTAMP.

ON UPDATE JUST_BE_A_NORMAL_COLUMN?

I tried using the "Clear default" option in mysql workbench and it did the opposite of what it should have done - it gave the column a default!

I was able to get rid of the default with ALTER TABLE t ALTER COLUMN c DROP DEFAULT, so the column is mandatory in INSERTs (just like it was before the dump/reload, as I wanted it) but the unwanted behavior on UPDATEs remains.

I have not enabled the explicit_defaults_for_timestamp option. If I was starting fresh I'd definitely use that option since it seems a lot more sane. But since I already had the columns configured the way I wanted them in 5.5, I expected them to keep the same semantics when transferred to 5.6. Apparently mysqldump just wasn't smart enough.

At this point I'm not sure I understand what effects would result from enabling explicit_defaults_for_timestamp. Would that option change the behavior of existing tables, or does it only change the interpretation of future CREATE TABLE commands? Would turning it on somehow help me fix the broken columns?

UPDATE:

A similar question is here but that one is about creating a new table, not altering an existing column. In fact that question is the one I used as a guide when creating the tables on the 5.5 server. I used the 2-step procedure: create with default 0 to suppress ON UPDATE CURRENT_TIMESTAMP, then drop default.

The 2-step procedure definitely doesn't produce the correct result on the 5.6 server without explicit_defaults_for_timestamp; this is a sign that either 5.6 doesn't perfectly imitate the old behavior in this mode, or the old server never did what I thought it was doing. I can't be sure which.

网友答案:
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.

网友答案:

Try enabling the explicit_defaults_for_timestamp system variable and then redefine the columns with:

ALTER TABLE `table` CHANGE COLUMN `col` `col` TIMESTAMP NOT NULL;

If I understand the documentation correctly enabling explicit_defaults_for_timestamp is mandatory to be able to define TIMESTAMP columns declared as NOT NULL and without an explicit DEFAULT.

网友答案:

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'?

分享给朋友:
您可能感兴趣的文章:
随机阅读: