MySQL 如何更改列并更改默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11312433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to alter a column and change the default value?
提问by qazwsx
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
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的 'VARCHAR(255) NOT NULL SET DEFAULT '{}'' 附近使用的正确语法
回答by fancyPants
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 '{}';
回答by DaveJenni
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 .. SET 语法。只是不要把所有其他的东西都放在那里。如果您要将列定义的其余部分放入，请根据接受的答案使用 MODIFY 或 CHANGE 语法。
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_TIMESTAMPbut why not eh?
'literal' 也可以是一个数字（例如...SET DEFAULT 0）。我还没有尝试过，...SET DEFAULT CURRENT_TIMESTAMP但为什么不呢？
回答by Akash Dole
If you want to add default value for already created column, this works for me:
如果你想为已经创建的列添加默认值，这对我有用：
ALTER TABLE Persons
ALTER credit SET DEFAULT 0.0';
回答by Leonard Lepadatu
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 doublecolumnname declaration
请注意双列名声明
Removing DEFAULT CURRENT_TIMESTAMP:
删除 DEFAULT CURRENT_TIMESTAMP：
ALTER TABLE tablename
ALTER COLUMN columnname1 DROP DEFAULT,
ALTER COLUMN columnname2 DROPT DEFAULT;
回答by Pradeep Kumar Prabaharan
Accepted Answer works good.
接受的答案效果很好。
In case of Invalid use of NULL valueerror, on NULLvalues, update all null values to default valuein that column and then try to do the alter.
如果出现Invalid use of NULL value错误，对于NULL值，将所有空值更新为该列中的默认值，然后尝试进行更改。
UPDATE foobar_data SET col = '{}' WHERE col IS NULL;
ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
回答by Sohail Ahmad
Try this
尝试这个
ALTER TABLE `table_name` CHANGE `column_name` `column_name` data_type NULL DEFAULT '';
like this
像这样
ALTER TABLE `drivers_meta` CHANGE `driving_license` `driving_license` VARCHAR(30) NULL DEFAULT '';
回答by Milan
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;
