如何将具有0000-00-00 00:00:00值的日期时间设置为NULL?
问题描述:
我需要在数据库上更改一些值.
I need to change a few values on my DB.
我忘记为表格设置可空值,并且默认情况下将其设置为0000-00-00 00:00:00.
I forgot to set nullable to the table and it set to 0000-00-00 00:00:00 by default.
现在我需要在NULL
中转换该值.
Now I need to convert that value in NULL
.
字段类型为Datetime.
The field type is Datetime.
我该怎么办?
我尝试使用典型的Update table set field = NULL WHERE field = '0000-00-00 00:00:00';
,但是它不起作用.
I try with the typical Update table set field = NULL WHERE field = '0000-00-00 00:00:00';
but it doesn't work.
答
您需要首先使该列可为空:
You need to first make the column nullable:
ALTER TABLE mytable MODIFY COLUMN field DATETIME NULL;
然后更新值:
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';