mysql alter null_MySQL Alter表导致错误：无效使用NULL值

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

The problem looks like from changing the Nullable which was YES to NOT NULL. Do I need to drop the column and add afterwards?

解决方案

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.

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;