How to deal with 0000-00-00 00:00:00 datetimes in MySQL 5.7 without changing SQL mode

Question

I've recently switched to MySQL 5.7 which causes troubles around datetimes. I have datetimes in my database with the default value of 0000-00-00 00:00:00. MySQL 5.7 does not seem to like that type of default value. I understand I can set sql_mode = '' but let's say I don't want to do that (sticking with the default strict mode), what would be the best approach?

I could first update all values:

UPDATE mycolumn SET field_datetime = NULL WHERE field_datetime='0000-00-00 00:00:00'

And then edit the datetime field to use NULL as default.

Would that be future proof? I assume MySQL 5.7 has its reasons to disallow 0000-00-00 00:00:00.

Answer 1

NULL as a value for any empty field is future proof and rather sane. Your application code will need to be tested to sure that it handles NULL correctly.

Accepting 0000-00-00 00:00:00 as valid datetime was a poor choice from MySQL developers years ago and just covered problems rather than solving them. Getting stricter on SQL seems to be the approach MySQL are slowly taking even if application developers are needing to update their structures and code to be more correct (and possibly fixing hidden bugs).