MySQL LOAD DATA与多行数据 [英] MySQL LOAD DATA with multiline data

问题描述

我在使用Mysql的LOAD DATA命令时遇到问题.

I'm having problems with Mysql's LOAD DATA command.

使用简单数据，效果很好.

With simple data, it works fine.

当我尝试加载长且具有跨越多行的字段的数据时，它将失败.

When I try to load in data which is long, and has a field which extends over many lines, it fails.

这是我的输入数据.字段以制表符分隔.

Here's my input data. Fields are tab-delimited.

1   2008-06-27 12:00:00 Type-Safe Enumerations  title Fr    5   
...line 1..
...line 2....   
    \N  2002-10-01 12:00:00 END-OF-THE-LINE
2   2008-06-27 12:00:00 Class for constants Classe pour constantes  1   
line 1...
..line 2..
    \N  2002-10-01 12:00:00 END-OF-THE-LINE
4   2008-06-27 12:00:00 Another Énumérations    5   
line 1
line 2
    \N  2002-10-01 12:00:00 END-OF-THE-LINE
5   2008-06-27 12:00:00 And Another Énumérations    5   
line 1
line 2
    \N  2002-10-01 12:00:00 END-OF-THE-LINE

这是LOAD DATA命令:

And here's the LOAD DATA command:

>LOAD DATA LOCAL INFILE "TopicInfileText2.dat" INTO TABLE Topic 
     LINES TERMINATED BY "END-OF-THE-LINE";

目标表:

CREATE TABLE Topic (    
    Id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    LastEdit TIMESTAMP NOT NULL,             
    TitleEnglish VARCHAR(75) NOT NULL,
    TitleFrench VARCHAR(75),
    ChapterId SMALLINT NOT NULL References Chapter,             
    BodyEnglish TEXT NOT NULL,             
    BodyFrench TEXT,             
    CreationDate DATETIME NOT NULL,
    PRIMARY KEY (Id)
);

MySQL的警告

| Incorrect integer value: '
2' for column 'Id' at row 2 |
| Incorrect integer value: '
4' for column 'Id' at row 3 |
| Warning | 1366 | Incorrect integer value: '
5' for column 'Id' at row 4 |
| Warning | 1366 | Incorrect integer value: '
' for column 'Id' at row 5  |

推荐答案

错误是在LOAD DATA命令中.我缺少换行符.

The error was in the LOAD DATA command. I was missing a newline character.

应该是:

LOAD DATA LOCAL INFILE "TopicInfileText2.dat" INTO TABLE 
    Topic LINES TERMINATED BY "END-OF-THE-LINE\n";

这篇关于MySQL LOAD DATA与多行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！