如何更改 mysql 表列的默认值? [英] How do I alter a mysql table column defaults?

问题描述

我有一个表，其中有一列类型为 timestamp，默认为 current_timestamp 并在每次更新时更新为 current_timestamp.

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

我想删除此列的更新时"功能.如何编写 alter 语句?

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

我尝试了以下方法:

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

但这不起作用.

推荐答案

Pete 几乎是正确的，但 'change' 使用了错误的语法:

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

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

请注意，您必须重复列名.此外，请确保您使用反引号而不是单引号来转义列名时间，这样可以防止将其解释为 mysql 列类型的时间.

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.

通过指定 CURRENT_TIMESTAMP 的 DEFAULT，MySQL 将不再自动更新该列.来自 MySQL 手册:

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

使用 DEFAULT CURRENT_TIMESTAMP 子句且没有 ON UPDATE 子句时，该列的默认值具有当前时间戳，但不会自动更新.

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.

这篇关于如何更改 mysql 表列的默认值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！