Search code examples
sqlmysqlload-data-infiledefault-constraint

mysql "datetime NOT NULL DEFAULT '1970-01-01' " gets turned to 0000-00-00 00:00:00

I have a column created as 

`date_start` datetime NOT NULL DEFAULT '1970-01-01'

However when I upload data from a CSV file with the LOAD DATA command with a blank entry for date_start the value saved is 0000-00-00 00:00:00 ?

Solution

  • The MySQL SQL mode by default allows zero dates.

    My belief is that the LOAD DATA INFILE command is reading the blank position intended to be a DATETIME, and automatically using a zero date before the insertion. This would explain why your default constraint isn't being applied - the value isn't null when being inserted.

    I think you have two options:

    1. Update all the blanks in your CSV from ", ," to ", NULL, " or ", DEFAULT, " so they'll be interpreted correctly
    2. Change the SQL mode: SET SQL_MODE='NO_ZERO_DATE'