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开发者_JS百科 ?

NULL and 'a blank entry' are not always treated the same in MySql. In the case of the datetime data type, blank entries are automatically converted to 0000-00-00 00:00:00. You can think of this as a form of typecasting, where a blank gets typecast to zero.

In your data file, try replacing all blank dates with the DEFAULT keyword.

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'

datetime means your column stores date and time in format Y-m-d H:i:s (0000-00-00 00:00:00).

date means your column stores just date in format Y-m-d (0000-00-00).

If you don't specify a value when inserting data, the default is used, but when you insert the data from the CSV file it does specify a value for the field, so the default is not used.

A blank entry is an invalid value for a datetime field, so the value 0000-00-00 00:00:00 is used instead, just as with any value that can't be parsed to a valid date (or a partial date).

MySQL has the annoying (in my opinion) behaviour that under some circumstances, if given an invalid value to store in a column, it will store a predefined "error value" instead. For example, in a date or datetime context, it will store the "zero date" you have noticed; for an ENUM column it will store '' (corresponds to 0 in a numeric context) as an error value.

Although it is inelegant, you might try doing the import and allowing the invalid zero dates to be inserted, and then issuing

UPDATE
    table_name
SET
    date_start = '1970-01-01 00:00:00'
WHERE
    date_start = '0000-00-00 00:00:00'