MySQL Alter table 导致错误：无效使用 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22971586/
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
MySQL Alter table causes Error: Invalid use of NULL value
提问by Daniel Qiu
My existing table:
我现有的表：
+-----------------+---------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date | timestamp | YES | | NULL |
I wanted to alter table like this:
我想像这样改变表格：
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
But I got this error:
但我收到了这个错误：
ERROR 1138 (22004) at line 7: Invalid use of NULL value
第 7 行的 ERROR 1138 (22004)：无效使用 NULL 值
The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?
问题看起来像是将 Nullable 更改为 YES 为 NOT NULL。我是否需要删除该列然后添加？
回答by rs.
It looks like there are few rows with NULL value.Update all null values to a default date in that column and then try to do a alter.
看起来有几行具有 NULL 值。将所有空值更新为该列中的默认日期，然后尝试进行更改。
Try this
尝试这个
--update null value rows
UPDATE enterprise
SET creation_date = CURRENT_TIMESTAMP
WHERE creation_date IS NULL;
ALTER TABLE enterprise
MODIFY creation_date TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;
回答by Vignesh Kumar A
You can't use this query until you have NO
NULLvalues in thecreation_datecolumn.
NULL在creation_date列中没有值之前，您不能使用此查询。
Update your creation_datecolumn with some default date and then alter the table.
creation_date使用一些默认日期更新您的列，然后更改表格。
Like this
像这样
UPDATE enterprise SET creation_date = CURRENT_TIMESTAMP WHERE creation_date IS NULL;
ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
回答by Koyel Sharma
truncate table and alter table to column NOT NULL
截断表并将表更改为列 NOT NULL
