如何从MySQL列中删除“NOT NULL”?

A show create table command shows the following:

显示创建表命令显示以下内容:

'columnA' varchar(6) NOT NULL DEFAULT '';

How do I modify that column so that the not null is removed? I need it to be:

如何修改该列以删除非空值?我需要它是:

'columnA' varchar(6) DEFAULT NULL;

I thought the following would work, but it has no effect:

我原以为下面的方法会有用，但没有效果:

ALTER TABLE tbl_name MODIFY columnA varchar(6) DEFAULT NULL;

3 个解决方案

#1

Try this instead:

试试这个:

ALTER TABLE tbl_name MODIFY columnA varchar(6) NULL DEFAULT NULL;

#2

Normally, Eric's answer should work:

通常情况下，Eric的答案应该是:

ALTER TABLE tbl_name MODIFY columnA varchar(6) NULL DEFAULT NULL;

(Although the 'NULL DEFAULT NULL' part is optional).

(虽然“NULL DEFAULT NULL”部分是可选的)。

But like you, I had a case that just returned OK without doing anything. In my case it appears to be due to the fact that my key was part of the primary key. So I had to do the following:

但就像你一样，我有一个案子没做任何事就回来了。在我看来，这似乎是因为我的钥匙是主键的一部分。所以我必须做以下事情:

ALTER TABLE tbl_name DROP PRIMARY KEY;
ALTER TABLE tbl_name MODIFY columnA varchar(6);
ALTER TABLE tbl_name ADD PRIMARY KEY (columnA);

with that last query specifying whatever your primary key actually is.

最后一个查询指定了您的主键实际上是什么。

Also, in case anyone thinks that is too verbose, the following combined query does NOT work, even though it should be identical:

另外，如果有人觉得太啰嗦，下面的组合查询就不能工作，即使它应该是相同的:

ALTER TABLE tbl_name DROP PRIMARY KEY, MODIFY columnA varchar(6), ADD PRIMARY KEY (columnA);

I assume that mysql rewrites that last query into a different order so that the primary key still exists when the modify is performed, hence the need to break it out into three statements.

我假设mysql将最后的查询改写成不同的顺序，以便在执行修改时主键仍然存在，因此需要将它分成三条语句。

FYI, this is on mysql 5.1.47 but I haven't yet found any documentation indicating why this happens so I don't know what versions are affected.

顺便说一下，这是mysql 5.1.47，但是我还没有找到任何文档说明为什么会发生这种情况，所以我不知道会影响什么版本。

#3

Make the change (locally) in phpMyAdmin. It will show the query it used for the change. Execute this query in production and you're done.

在phpMyAdmin中更改(本地)。它将显示用于更改的查询。在产品中执行这个查询，就完成了。

You can use this strategy in any GUI tool to see the queries it performs. I personally use Sequel Pro (for Mac OS X) instead of phpMyAdmin.

您可以在任何GUI工具中使用此策略来查看它执行的查询。我个人使用Sequel Pro(用于Mac OS X)而不是phpMyAdmin。

#1