MySQL Alter表导致错误:无效使用NULL值 [英] MySQL Alter table causes Error: Invalid use of NULL value

问题描述

我现有的表格:

+-----------------+---------------+------+-----+---------+-------------------+
| Field           | Type          | Null | Key | Default | Extra             |
+-----------------+---------------+------+-----+---------+-------------------+
| creation_date   | timestamp     | YES  |     | NULL                        |

我想这样修改表格:

ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

但是我得到了这个错误:

But I got this error:

第7行出现错误1138(22004):无效使用NULL值

ERROR 1138 (22004) at line 7: Invalid use of NULL value

问题看起来就像是将Nullable从YES更改为NOT 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?

推荐答案

看起来有几行具有NULL值.将所有null值更新为该列中的默认日期，然后尝试进行更改.

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.

尝试

--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;

这篇关于MySQL Alter表导致错误:无效使用NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！