Mysql import transforms timestamp null values to 0

问题:

I have some text file -generated periodically- which contains sql rows to be inserted to mysql using mysqlimport. One of the columns has Null value, and this column in the database is a datetime field, with default value set to Null. However after running mysql import, I got the value 0000-00-00 00:00:00 in this column, so how can I get rid of this? I want the column value to be Null as inserted.

Edit: Notes:

1) When inserting a row directly using INSERT statement, this problem doesn't occur and the column value is set to NULL normally, so this has something to do with mysqlimport

2) I use this mysqlimport: mysqlimport -u root -p<password> -h localhost --columns='<columns_orders>' --local --fields-terminated-by=',' <db_name> <file_path>

3) Sample of one row in the data file: 5964,45,0,0,NULL,45,5,8,67869,67848,65142,1,NULL,NULL,NULL,19

4) The datetime column is rails timestamp created_at that is created via a rails migration.

回答1:

If table definition allows null and defaults to NULL and your source data also contains \N then you should get NULL as expected. MySQL is not a reliable database server in many different ways, so I'd need your sources (at least some sample) to diagnostic the actual problem. May be empty string instead of \N.

回答2:

There can be quick hack:

• For the time being change that datetime column's type to VARCHAR so that null values become empty strings.

• When import is complete change that column's type back to datetime and it'll give you the desired results :)