Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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.

How about setting to NULL all the rows concerned (which have 000....000 in their datetimefield) ? Oh well missread the question - u need it before. – Falt4rm May 26, 2015 at 15:34 The preferred method to show a datetime column empty is storing it into 0000-00-00 00:00:00 format. That's how most of the developers do it, I'm not sure why you want to change it. – Imran Zahoor May 26, 2015 at 15:52 @Imran really? I've never heard of any developer that I know doing that... always stored as NULL. – Kevin Nagurski May 26, 2015 at 16:01 I know this is a little too obvious, but does the column allow NULL? Also, set the default value as NULL. Probably won't fix it, but worth a shot. – Kevin Nagurski May 26, 2015 at 16:01 Does your column have a DEFAULT NULL attribute? I think you can do something like that, but I'm not positive. – AdamMc331 May 26, 2015 at 16:50

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.

Please try with \N (null escape) instead of NULL. I'll confirm if the NULL string is considered NULL for MySQL. – lnrdo May 27, 2015 at 9:09 There's also an important option that makes MySQL stop on casting errors. This invalid date MySQL accepts is really annoying, but in fact it should fail to import on any error. – lnrdo May 27, 2015 at 9:11 It worked !! Writing \N instead of NULL did the trick! This means that NULL string was interpreted as undefined value and thus default zero value was inserted, which for datetime is 0000-00-00 00:00:00, thanks a lot :) – user1011792 May 27, 2015 at 9:34 Actually NULL is an invalid date representation MySQL is silently transforming into 0000-00-00. It should fail, but using MySQL is only a choice to be considered when dealing with legacy systems and databases. One should always prefer PostgreSQL to MySQL for new software. Your import should croak on invalid data. – lnrdo May 27, 2015 at 9:39

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 :)

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question . Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers .