从定界文件导入时，mysqlimport NULL值

问题描述

我正在运行mysqlimport来加载数据，并且有一个空值未加载为null的问题.我正在使用mysql 5.7.23

I am running mysqlimport to load data and am having an issue with empty values not being loaded as null. I am using mysql 5.7.23

可以通过创建一个名为mytable.psv的文件来重新创建问题，该文件包含:

Issue can be recreated by creating a file named mytable.psv, containing:

code|date_a|date_b
1|2018-11-27|2018-11-27
2|2018-11-27|

然后运行命令:

mysql -u root -e "CREATE DATABASE mydb"
mysql -u root -e "USE mydb; CREATE TABLE mytable (code varchar(15) NOT NULL, date_a date NOT NULL, date_b date);"
mysqlimport --ignore-lines=1 --fields-terminated-by='|' --columns="code,date_a,date_b" --local -u root mydb mytable.psv
mysql -u root -e "USE mydb; SELECT * FROM mytable;"

输出为:

mydb.mytable: Records: 2  Deleted: 0  Skipped: 0  Warnings: 1
+------+------------+------------+
| code | date_a     | date_b     |
+------+------------+------------+
| 1    | 2018-11-27 | 2018-11-27 |
| 2    | 2018-11-27 | 0000-00-00 |
+------+------------+------------+

您可以看到第二行的date_b为空，我需要将其为null，但它将转换为0000-00-00.这打破了我需要进一步发展的限制.从分隔文件中导入数据时，是否有办法确保空值为空?

You can see that date_b for the second row is empty and I need that to be a null but instead it transforms into 0000-00-00. This is breaking constraints that I need further along. Is there a way to ensure empty values are null when importing data from a delimited file?

推荐答案

我能够在这里找到答案: https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html#option_mysqlimport_lines-terminated-by https://dev.mysql.com/doc/refman/8.0/en/load-data.html 您需要字符'\ N'来导入空值，因此在上面的示例中为:

I was able to find answers here:https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html#option_mysqlimport_lines-terminated-byhttps://dev.mysql.com/doc/refman/8.0/en/load-data.htmlYou need the characters '\N' to import a null so in the above example it would be:

code|date_a|date_b
1|2018-11-27|2018-11-27
2|2018-11-27|\N

这篇关于从定界文件导入时，mysqlimport NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！