MySQL导入将时间戳记的空值转换为0000-00-00 00:00:00 [英] Mysql import transforms timestamp null values to 0000-00-00 00:00:00

问题描述

我有一些文本文件-定期生成-包含要使用mysqlimport插入mysql的sql行.其中一列具有Null值，数据库中的该列是datetime字段，默认值设置为Null.但是，在运行mysql import之后，我在此列中获得了0000-00-00 00:00:00值，那么如何摆脱这个呢?我希望列值在插入时为Null.

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.

修改: 注意:

1)使用INSERT语句直接插入行时，不会发生此问题，并且列值通常设置为NULL，因此这与mysqlimport

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)我使用以下mysqlimport:mysqlimport -u root -p<password> -h localhost --columns='<columns_orders>' --local --fields-terminated-by=',' <db_name> <file_path>

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

3)数据文件中一行的示例:5964,45,0,0,NULL,45,5,8,67869,67848,65142,1,NULL,NULL,NULL,19

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)datetime列是通过rails迁移创建的rails timestamp created_at.

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

推荐答案

如果表定义允许null，并且默认值为NULL，并且您的源数据也包含\ N，那么您应该按预期获得NULL. MySQL在许多方面都不是可靠的数据库服务器，因此我需要您的资料(至少是一些示例)来诊断实际问题.可能是空字符串，而不是\ N.

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.

这篇关于MySQL导入将时间戳记的空值转换为0000-00-00 00:00:00的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！