mysql load 内容多行_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

And here's the LOAD DATA command:

>LOAD DATA LOCAL INFILE "TopicInfileText2.dat" INTO TABLE Topic

LINES TERMINATED BY "END-OF-THE-LINE";

The target table :

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)

);

Warnings from 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 |

解决方案

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

Should have been:

LOAD DATA LOCAL INFILE "TopicInfileText2.dat" INTO TABLE

Topic LINES TERMINATED BY "END-OF-THE-LINE\n";