MySQLAlter 表导致 Error: NULL 值的使用无效

我现有的桌子:

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

我想像这样改变桌子:

ALTER TABLE enterprise MODIFY creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

但我得到了这个错误:

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

这个问题看起来像是从改变 Nullable (是)到 NOT NULL (不是)。我是否需要删除该列，然后再添加？

mysql

100903 次浏览

小开

You can't use this query until you have NO NULL values in the creation_date column.

Update your creation_date column with some default date and then alter the table.

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;

小开

最佳答案

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;

小开

simply,we can't change table structure if it is full with data. Solving is:

1)delete all data in it by(delete from table_name) or update it by (update table_name set new_value where condition )

2)use (alter with modify or change ) to renew the structure of table