加载数据 INFILE MYSQL 错误

标签: MySQL Java
发布时间: 2015/6/11 7:21:36
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想要加载使用负载数据 infile 命令的 mysql 表该阀 java 程序中的文件,但我收到 MySQL 语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Sql 语句如下:

 String sql =    "LOAD DATA  LOCAL INFILE '/Dataset/"+listOfFiles[i].getName()+".txt'" +
                            "    INTO TABLE data " +
                            "    CHARACTER SET utf8 " +
                            "    FIELDS " +
                            "        TERMINATED BY '|' " +
                            "    LINES " +
                            "    STARTING BY 'status' " +
                            "        TERMINATED BY '\n' " +
                            "    (@col_type, @col_origin, @col_text, @col_url, @col_id, @col_time, @col_retcount)"+
                            "     set userid = '1234' "
                            "      , original = @col_origin "+
                            "      , datatext = @col_text "+
                            "      , url = @col_url "+
                            "      , id = @col_id "+
                            "      , retcount = @col_retcount "+                                
                            "      , date = str_to_date(@col_time, '%m/%d/%Y') "+
                            "      , isanswer = CASE WHEN @col_origin LIKE '% abcd %' THEN 1 ELSE 0 END ";

我的问题是,我不能发现的错误。 谁可以帮我吗?

解决方法 1:

你需要在你附近的 Java 字符串中有两个反斜杠 (\)

"       Terminated By '\\n' " +
                        ^ new

然后,您的字符串,将如下所示:

String sql = "LOAD DATA "
        + " LOCAL INFILE '/Dataset/"+listOfFiles[i].getName()+".txt'"
        + "    INTO TABLE data "
        + "    CHARACTER SET utf8 "
        + "    FIELDS "
        + "        TERMINATED BY '|' "
        + "    LINES "
        + "    STARTING BY 'status' "
        + "        TERMINATED BY '\\n' "
        + "    (@col_type, @col_origin, @col_text, @col_url, @col_id, @col_time, @col_retcount)"
        + "     set userid = '1234' "
        + "      , original = @col_origin "
        + "      , datatext = @col_text "
        + "      , url = @col_url "
        + "      , id = @col_id "
        + "      , retcount = @col_retcount "
        + "      , date = str_to_date(@col_time, '%m/%d/%Y') "
        + "      , isanswer = CASE WHEN @col_origin LIKE '% abcd %' THEN 1 ELSE 0 END ";
官方微信
官方QQ群
31647020