在MySQL中导入CSV数据时空整数字段的默认值

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.

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

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

在CSV中，此字段有时为空。当我执行LOAD查询时，MySQL将发出如下警告：

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

不正确的整数值：''对于第180行的'contributor_zipcode'列

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?

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

3 个解决方案

#1

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

空值被解释为空字符串（''），而不是NULL，因此未使用默认值。

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

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

Here's an example, assuming you want to set it to 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);

#2

In Csv I replaced all the "" with "\N" before I run the script, this creates a null field in db

在Csv中，我在运行脚本之前用“\ N”替换了所有“”，这在db中创建了一个空字段

#3

For some elements (e.g. zip codes), may be better for default value to be null, not 0.

对于某些元素（例如邮政编码），默认值可能更好为null，而不是0。

Assume you have a table of people called (what else) People.

假设你有一张叫做（还有什么）人的人。

create table People (
    id int,
    first_name varchar(30),
    last_name varchar(50),
    city varchar(50),
    state varchar(50),
    zip int
);

Next, load your file.csv into the People table. Note the last line (starting with set) where zip is assigned null if the value in the file is "" (aka an empty string)

接下来，将file.csv加载到People表中。注意最后一行（以set开头）如果文件中的值为“”（也就是空字符串），则将zip指定为null

load data local infile '/path/to/file.csv'
into table People
fields 
    terminated by ','
    enclosed by '"'
lines terminated by '\n'
(id, first_name, last_name, city, state, @zip)
set zip = if(@zip='', null, @zip);

#1