blocks|key|222601|text|ALTER+TABLE+foobar_data+MODIFY+COLUMN+col+VARCHAR(255)+NOT+NULL+DEFAULT+'{}';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|222602|第二种可能性也是如此(感谢juergen_d)：|unstyled|222603|ALTER+TABLE+foobar_data+CHANGE+COLUMN+col+col+VARCHAR(255)+NOT+NULL+DEFAULT+'{}';|222604|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
</code></pre>

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

<pre><code>ALTER TABLE foobar_data CHANGE COLUMN col col VARCHAR(255) NOT NULL DEFAULT '{}';
</code></pre>

blocks|key|222625|text|作为后续，如果您只想设置默认值，您可以使用ALTER+..设置语法。别把其他东西都放进去。如果要将列定义的其余部分放入其中，请根据接受的答案使用MODIFY或CHANGE语法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|222626|无论如何，设置列默认值的ALTER语法(因为这就是我来这里时要找的内容)：|222627|ALTER+TABLE+table_name+ALTER+COLUMN+column_name+SET+DEFAULT+'literal';|code-block|syntax|javascript|222628|对于它，‘文字’也可以是一个数字(例如...SET+DEFAULT+0)。我还没有在...SET+DEFAULT+CURRENT_TIMESTAMP上尝试过，但是为什么不呢？|offset|length|style|CODE|222629|entityMap^0|0|0|0|J|G|16|W|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]|$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

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.

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

<pre><code>ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'literal';
</code></pre>

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

blocks|key|3087233|text|如果以上方法对您无效(即:您正在使用、new+SQL或Azure)，请尝试以下方法：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3087234|1)删除现有的列约束(如果有)：|3087235|ALTER+TABLE+[table_name]+DROP+CONSTRAINT+DF_my_constraint|code-block|syntax|javascript|3087236|2)新建一个：|3087237|ALTER+TABLE+[table_name]+ADD+CONSTRAINT+DF_my_constraint++DEFAULT+getdate()+FOR+column_name;|3087238|entityMap^0|I|E|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

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

1) drop existing column constraint (if any):

<pre><code>ALTER TABLE [table_name] DROP CONSTRAINT DF_my_constraint
</code></pre>

2) create a new one:

<pre><code>ALTER TABLE [table_name] ADD CONSTRAINT DF_my_constraint DEFAULT getdate() FOR column_name;
</code></pre>

blocks|key|222525|text|如果您想为已经创建的列添加一个默认值，这对我来说是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|222526|ALTER+TABLE+Persons
ALTER+credit+SET+DEFAULT+0.0;|code-block|syntax|javascript|222527|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you want to add a default value for the already created column,
this works for me:
<pre><code>ALTER TABLE Persons
ALTER credit SET DEFAULT 0.0;
</code></pre>

blocks|key|219426|text|对于默认CURRENT_TIMESTAMP：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219427|ALTER+TABLE+tablename
+CHANGE+COLUMN+columnname1+columname1+DATETIME+NOT+NULL+DEFAULT+CURRENT_TIMESTAMP,
+CHANGE+COLUMN+columnname2+columname2+DATETIME+NOT+NULL+DEFAULT+CURRENT_TIMESTAMP;|code-block|syntax|javascript|219428|请注意double列名称声明|offset|length|style|BOLD|219429|正在删除默认CURRENT_TIMESTAMP：|219430|ALTER+TABLE+tablename
+ALTER+COLUMN+columnname1+DROP+DEFAULT,
+ALTER+COLUMN+columnname2+DROPT+DEFAULT;|219431|entityMap^0|0|0|3|6|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

For DEFAULT CURRENT_TIMESTAMP:

<pre><code>ALTER TABLE tablename
 CHANGE COLUMN columnname1 columname1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CHANGE COLUMN columnname2 columname2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
</code></pre>

Please note double columnname declaration

Removing DEFAULT CURRENT_TIMESTAMP:

<pre><code>ALTER TABLE tablename
 ALTER COLUMN columnname1 DROP DEFAULT,
 ALTER COLUMN columnname2 DROPT DEFAULT;
</code></pre>

blocks|key|224656|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|224657|ALTER+TABLE+`table_name`+CHANGE+`column_name`+`column_name`+data_type++NULL+DEFAULT+'';|code-block|syntax|javascript|224658|像这样|224659|ALTER+TABLE+`drivers_meta`+CHANGE+`driving_license`+`driving_license`+VARCHAR(30)+NULL+DEFAULT+'';|224660|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Try this

<pre><code>ALTER TABLE `table_name` CHANGE `column_name` `column_name` data_type NULL DEFAULT '';
</code></pre>

like this

<pre><code>ALTER TABLE `drivers_meta` CHANGE `driving_license` `driving_license` VARCHAR(30) NULL DEFAULT '';
</code></pre>

blocks|key|219489|text|被接受的答案效果很好。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|219490|如果出现无效使用空值错误，在+NULL+值上，会将该列中所有空值更新为默认值，然后尝试执行更改操作。|offset|length|style|BOLD|219491|UPDATE+foobar_data+SET+col+=+'{}'+WHERE+col+IS+NULL;

ALTER+TABLE+foobar_data+MODIFY+COLUMN+col+VARCHAR(255)+NOT+NULL+DEFAULT+'{}';|code-block|syntax|javascript|219492|entityMap^0|0|E|6|N|F|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Accepted Answer works good.

In case of Invalid use of NULL value error, on NULL values, update all null values to default value in that column and then try to do the alter.

<pre><code>UPDATE foobar_data SET col = '{}' WHERE col IS NULL;

ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
</code></pre>

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

<pre><code>ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';
</code></pre>

<blockquote>
 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
</blockquote>

How to alter a column and change the default value?

服务器

MySQL

在尝试更改列的数据类型并设置新的默认值时，我遇到了以下错误：ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';错误1064 (42000)：您的SQL语法中有一个错误；请查看与您的MySQL服务器版本对应的手册...

问如何更改列和更改默认值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何更改列和更改默认值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何更改列和更改默认值？
EN