Mysql:设置0000-00-00为null

I already found How to set to NULL a datetime with 0000-00-00 00:00:00 value? which is exactly my question - however, it does not work.

我已经找到如何将一个datetime设为NULL，值为000000 -00 00:00 ?这正是我的问题——然而，它不起作用。

The colum 'bezahltDatum' is a date field which is nullable

colum 'bezahltDatum'是一个可以为空的日期字段

The query

查询

SELECT id,bezahltDatum FROM `officeBuch` WHERE  bezahltDatum = '0000-00-00'

gives 25 results and is working.

给出25个结果并且正在工作。

However, when I try to convert '0000-00-00' to null with

但是，当我试图将“0000-00”转换为null时

UPDATE `officeBuch` SET bezahltDatum = null WHERE bezahltDatum = '0000-00-00'

Then I get an error message

然后我得到一个错误消息

#1292 - Incorrect date value: '0000-00-00' for column 'bezahltDatum' at row 1

#1292 -不正确的日期值:第1行“bezahltDatum”列的“0000-00-00”

Why is this not working and how can I fix it? I am using phpMyAdmin Version 4.7.2 and Mysql 5.7.18-0ubuntu0.16.04.1

为什么它不能工作，我如何修复它?我正在使用phpMyAdmin版本4.7.2和Mysql 5.7.18-0ubuntu0.16.04.1

2 个解决方案

The only way i can think of is changing the SQL mode temporarily with something like this

我能想到的唯一方法是用类似的东西暂时更改SQL模式

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

before the actual update statement. This will disable the strict mode temporarily and the error should not appear.

在实际更新语句之前。这将暂时禁用严格模式，不应出现错误。