警告| 1366 |不正确的整数值：''对于第34行的'running_time_minutes'列

I'm getting the error

我收到了错误

Warning | 1366 | Incorrect integer value: '' for column running_time_minutes' at row 34

警告| 1366 |不正确的整数值:''对于第34行的列running_time_minutes'

with the following script. The error also applies to the column footage_lenght_feet. My .csv has empty values for many cells, but this is because the answer is unknown. I don't understand why even though i've specified 'default NULL' for this column it keeps returning the error.

使用以下脚本。该错误也适用于列footage_lenght_feet。我的.csv对于许多单元格都有空值,但这是因为答案未知。我不明白为什么即使我为这个列指定了'default NULL'它仍然会返回错误。

    -- create the Films table

    DROP TABLE IF EXISTS Films;

    CREATE TABLE Films (

    title varchar (100) NOT NULL,
    alternate_title varchar (50),
    year_of_release varchar (15),   
    Country varchar (20),
    running_time_minutes int (10) default NULL,
    footage_lenght_feet int (10) default NULL,
    FilmStock varchar (10),
    FilmGauge varchar (10),
    BW_color varchar (10),
    notes varchar (255),
    print_publications varchar (255),
    existent_print varchar (255),
    URL_1 varchar (100),
    URL_2 varchar (100),
    URL_3 varchar (100),
    PRIMARY KEY(title),
    FOREIGN KEY(Country) REFERENCES Country (Country),
    FOREIGN KEY(FilmStock) REFERENCES FilmStock (FilmStock),
    FOREIGN KEY(BW_color) REFERENCES BW_color (BW_color),
    FOREIGN KEY(FilmGauge) REFERENCES FilmGauge (FilmGauge));
    ENGINE = INNODB;

    LOAD DATA LOCAL INFILE "Films.csv"
    INTO TABLE Films
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

1 个解决方案

#1

You've posted no data, so this is a little speculative, but I assume from your comments that you have an input file something like this:

你没有发布任何数据,所以这有点推测,但我从你的评论中假设你有一个这样的输入文件:

"Title", "Alternate Title", "Year of Release", "Country","Running Time (minutes)", "Footage Length (feet)", "Filmstock","FilmGauge", "BW/Colour", "Notes", "Print Publications", "Existent Print", "URL1", "URL2","URL3"
"A title","","1958","UK","102","38000","","Kodak","70mm","BW","","","","",""
"Another title","","1958","UK",,"38000","","Kodak","70mm","BW","","","","",""

(Note that in the second row of data the field corresponding to running_time_minutes is empty.)

(注意,在第二行数据中,对应于running_time_minutes的字段为空。)

When MySQL reads this file it parses each row into fields according to the instructions you give it:

当MySQL读取此文件时,它会根据您提供的指令将每行解析为字段:

LOAD DATA LOCAL INFILE "Films.csv"
    INTO TABLE Films
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES;

Then it attempts to coerce the data into the correct form for the target columns in the table. varchar columns require no coercion, so they're easy. Numeric columns need to be coerced to a number before they can be stored.

然后,它会尝试将数据强制转换为表中目标列的正确形式。 varchar列不需要强制,因此它们很容易。在存储数字列之前,需要将数字列强制转换为数字。

This works for columns with numeric data (like the string 102, for example), but when MySQL encounters an empty column it treats this as an empty string. This is different from NULL, and is not numeric and cannot be coerced into the correct form for the target column. Thus you see the warning.

这适用于具有数字数据的列(例如字符串102),但是当MySQL遇到空列时,它将其视为空字符串。这与NULL不同,并且不是数字,不能强制转换为目标列的正确形式。因此,您会看到警告。

You need to give MySQL a little help here. Add a list of columns, substituting user variables for the numeric columns you're having trouble with. Then you can give MySQL explicit instructions on what to do with the numeric fields.

你需要在这里给MySQL一些帮助。添加列列表,将用户变量替换为您遇到问题的数字列。然后,您可以向MySQL提供有关如何处理数字字段的明确说明。

LOAD DATA LOCAL INFILE "Films.csv"
    INTO TABLE Films
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    -- Put the numeric fields in user variables (names prefixed by @)
    (title, alternate_title, year_of_release, Country, @runningTime,
       @footageLength, FilmStock, Filmgauge, BW_color, notes, 
       print_publications, existent_print, URL_1, URL_2, URL_3
    ) 
    -- Now check for empty strings and set the numeric columns to null if they're found
    set running_time_minutes = if(@runningTime="", null, @runningTime),
        footage_lenght_feet = if(@footageLength="", null, @footageLength)

#1