can be NULL same as 0000-00-00 in a nullable DATETIME column while selecting rows?

Question

I am working on some PHP code optimization and while I am going through I find some SELECT statements with the following conditions:

WHERE `date_exported` = '0000-00-00 00:00:00' OR `date_exported` IS NULL

This makes me wonder whether the same statement will work the same with only one condition: the comparison to NULL or the comparison against an "invalid" datetime.

I will add on top of that: using MySQL Workbench and Datagrip (or the equivalent from tools like PhpStorm) I am not able to see the column with NULL but 0000-00-00 00:00:00. I am not sure if this is a configuration thing or if it is supposed to be like that.

This is MySQL 5.7 and I do not have permission to check on config stuff or insights on how this instance is configured.

I have tried to set a playground here but it won't allow me to add 0000-00-00 00:00:00 in a NULL datetime column

The SQL_MODE is set as follows:

NO_ZERO,IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Note: is it 0000-00-00 00:00:00 invalid at all?

Answer 1

Please provide SHOW VARIABLES LIKE "SQL_MODE"; And look for NO_ZERO_DATE.

It would be more efficient to avoid OR.

Consider "fixing" the data during INSERTs. This avoids the need to hassle it during SELECTs.

Since the default is NO_ZERO_DATE, I recommend moving that direction. cf https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_show_old_temporals

I don't have the details on this shift away from "zero_date", so I can't say where 5.7 fits in to the spectrum.

I don't know whether you can do (1 column at a time).

UPDATE t
    SET mydate = NULL
    WHERE mydate = '0000-00-00'

and similarly for any datetimes.

I tried using something like your WHERE .. OR .., but I got ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'. But then I have NO_ZERO_DATE set.

I hope I have pointed you in the right direction; sorry to not have the full Answer.