在MySQL中导入CSV数据时，空整数字段的默认值 [英] Default value for empty integer fields when importing CSV data in MySQL

问题描述

我要使用 LOAD DATA INFILE 将CSV导入MySQL表。其中一个表字段存储邮政编码数据，我已在表结构中定义 contributor_zipcode INT 。

在CSV中，此字段有时为空。当我执行 LOAD 查询时，MySQL会抛出一个警告，如：

第180行的'contributor_zipcode'列中的值：''：

我尝试将该字段重新定义为 contributor_zipcode INT DEFAULT'0'，它创建相同的警告，并且 contributor_zipcode INT DEFAULT NULL ，MySQL不允许。任何建议？

解决方案

空值被解释为空字符串（''），而不是NULL，

如果要显式控制这些空字符串的处理，最好的办法是将它们加载到用户变量中，然后设置

您可以使用此值将值设置为任何您想要的值（NULL，0等）。

下面是一个例子，假设你想把它设置为0：

  LOAD DATA INFILE' ...'
 INTO TABLE your_table 
 FIELDS TERMINATED BY'，'
（column_one，...，@contributor_zipcode，...，column_n）
 SET contributor_zipcode = IF @contributor_zipcode =''，0，@ contributor_zipcode）; 
  

I'm importing a CSV into a MySQL table with LOAD DATA INFILE. One of the table's fields stores zip code data, which I've defined in the table structure contributor_zipcode INT.

In the CSV, this field is sometimes empty. When I execute the LOAD query, MySQL will throw a warning like:

Incorrect integer value: '' for column 'contributor_zipcode' at row 180

I've tried redefining the field as contributor_zipcode INT DEFAULT '0', which creates the same warning, and contributor_zipcode INT DEFAULT NULL, which MySQL won't allow. Any advice?

解决方案

The empty values are being interpreted as the empty string (''), not NULL, so the default value is not being used.

If you want to explicitly control the handling of these empty strings, the best thing to do is to load them into a user variable, and then set the column conditionally using the user variable.

You could use this to set the value to whatever you want (NULL, 0, etc.).

Here's an example, assuming you want to set it to 0:

LOAD DATA INFILE '...'
INTO TABLE your_table
FIELDS TERMINATED BY ','
(column_one,..., @contributor_zipcode,..., column_n)
SET contributor_zipcode = IF(@contributor_zipcode='',0,@contributor_zipcode);

这篇关于在MySQL中导入CSV数据时，空整数字段的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！