如何更改列并更改默认值?

I got the following error while trying to alter a column's data type and setting a new default value:

在尝试更改列的数据类型并设置新的默认值时，我得到了以下错误:

ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';

ERROR 1064 (42000): 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 'VARCHAR(255) NOT NULL SET DEFAULT '{}'' at line 1

错误1064 (42000):SQL语法中有错误;检查与MySQL服务器版本对应的手册，找到正确的语法，在第1行使用“VARCHAR(255) NOT NULL SET DEFAULT‘{}”

5 个解决方案

#1

197

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';

A second possibility which does the same (thanks to juergen_d):

第二种可能也是如此(感谢juergen_d):

ALTER TABLE foobar_data CHANGE COLUMN col col VARCHAR(255) NOT NULL DEFAULT '{}';

#2

As a follow up, if you just want to set a default, pretty sure you can use the ALTER .. SET syntax. Just don't put all the other stuff in there. If you're gonna put the rest of the column definition in, use the MODIFY or CHANGE syntax as per the accepted answer.

作为后续，如果您只想设置一个默认值，那么一定可以使用ALTER。集语法。别把其他东西都放进去。如果要将列定义的其余部分放入，请按照所接受的答案使用修改或更改语法。

Anyway, the ALTER syntax for setting a column default, (since that's what I was looking for when I came here):

无论如何，设置列默认值的ALTER语法(因为这是我来这里时所寻找的):

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'literal';

For which 'literal' could also be a number (e.g. ...SET DEFAULT 0). I haven't tried it with ...SET DEFAULT CURRENT_TIMESTAMP but why not eh?

“文字”也可以是一个数字(例如……)设置默认值0).我还没有尝试…设置默认的CURRENT_TIMESTAMP，为什么不设置eh?

#3

If you want to add default value for already created column, this works for me:

如果你想为已经创建的列添加默认值，这对我来说是可行的:

ALTER TABLE Persons
ALTER credit SET DEFAULT 0.0';

#4

In case the above does not work for you (i.e.: you are working with new SQL or Azure) try the following:

如以上不适用于你(即您正在使用新的SQL或Azure)尝试以下操作:

1) drop existing column constraint (if any):

1)删除现有列约束(如有):

ALTER TABLE [table_name] DROP CONSTRAINT DF_my_constraint

2) create a new one:

2)创建一个新的:

ALTER TABLE [table_name] ADD CONSTRAINT DF_my_constraint  DEFAULT getdate() FOR column_name;

#5

For DEFAULT CURRENT_TIMESTAMP:

为默认CURRENT_TIMESTAMP:

ALTER TABLE tablename
 CHANGE COLUMN columnname1 columname1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CHANGE COLUMN columnname2 columname2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

Please note double columnname declaration

请注意双列名称声明

Removing DEFAULT CURRENT_TIMESTAMP:

删除默认CURRENT_TIMESTAMP:

ALTER TABLE tablename
 ALTER COLUMN columnname1 DROP DEFAULT,
 ALTER COLUMN columnname2 DROPT DEFAULT;

#1

197

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';