LOAD DATA INFILE syntax error

query: load data infile 'systemmessage.txt' ignore into table systemmessage (message) lines starting by 'a,' terminated by '\0' ignore 1 lines

gives me a syntax error near 'lines starting by'. If I remove the 'starting by' part, the error is now with 'terminated by'. If I remove that too, the error is with 'ignore 1 lines'. Where the hell is the problem?? The file exists, the table exists, if I remove all checks 开发者_Python百科it loads, but with the wrong data.

You just need to move the column list to the end of the SQL statement.

As described in the manual, the format info (lines starting by, skip 1 lines, etc) need to be specified before the (optional) column list:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Here's the fixed query:

load data infile '/tmp/systemmessage.txt' 
ignore into table systemmessage 
lines starting by 'a,' 
terminated by '\0' 
ignore 1 lines
(message)