如何将值为0000-00-00 00:00:00的日期时间设置为NULL？

Question

我需要更改我的数据库中的一些值。

我忘记将表设置为可空，它现在默认设置为0000-00-00 00:00:00。

现在我需要将该值转换为NULL。

该字段的类型是日期时间。

我该如何做？

我尝试使用典型的Update table set field = NULL WHERE field = '0000-00-00 00:00:00';但它不起作用。

- Borja Pombo

Answer 1

您需要先将该列设为可空：

ALTER TABLE mytable MODIFY COLUMN field DATETIME NULL;

然后更新这些值：

UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';

- Mureinik

---

Nullable被设置在Symfony2中。 - Borja Pombo

查看 @AlterPHP 的答案，适用于 MySQL 5.7。 - Lorenz Meyer

Answer 2

从MySQL 5.7开始，如果不先禁用此限制（仅在事务期间），则SQL模式NO_ZERO_DATE会使此更新操作无法完成。

SET sql_mode=(SELECT REPLACE(@@sql_mode,"NO_ZERO_DATE", ""));
UPDATE mytable SET field = NULL WHERE field = '0000-00-00 00:00:00';

- AlterPHP

Answer 3

首先，您需要将列设置为可空：

@Mysql5.7

错误的写法:

update episodes set `ending` = NULL WHERE `ending` = '0000-00-00'

正确：

update episodes set `ending` = NULL WHERE `ending` = 0000-00-00

注意：在您的where子句中删除日期值中的引号。希望它可以帮助到某些人。

- A. Sideeq

---

你确定吗？我的意思是你确定它不是一样的（以防万一它能工作）？ :) - alexbusu

你使用的是哪个MySQL版本？ - A. Sideeq

由于数据库中的日期存储为整数，因此您可以简单地设置 UPDATE episodes SET ending = NULL WHERE ending = 0;。 - MichielB

Answer 4

对我有效的方法是：

1. 将字段从datetime更改为varchar（191）。
2. 将所有字段= '0000-00-00 00:00:00'的行更新为NULL。
3. 将字段设置回datetime。

- JuliaJ

Answer 5

很遗憾，这不会起作用。
for:

update episodes set `ending` = NULL 
WHERE `ending` = '0000-00-00'

如果在where子句中有0000-00-00，则会出现以下错误：

"1292 -列'ending'的时间值不正确：'0000-00-00'，位于第1行。"

即使在字段定义中，ending为NULL且default=null。与此相关的是编程问题。

- William Drescher

---

这是一个较旧的问题：如果您的答案是由于使用比指定版本更新的mySQL软件版本，请在您的答案中说明版本号。现在看起来有点愚蠢，因为正确/最佳答案已经给出超过2年了。 - ZF007