MySQL使用JDBC Load Data InFile导入数据注意事项_MySQL

bitsCN.com

今天测式JDBC Connection使用Load Data InFile往数据表中导入数据，Java程序如下：

public class LoadDataTest {    @Test    public void test_loadData() throws Exception {        Connection conn = null;        Statement stmt = null;        try {            conn = DBUtils.fetchConnection();            stmt = conn.createStatement();            String sql = "load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by '/'' lines terminated by '/r/n'";            boolean result = stmt.execute(sql);            System.out.println("Load执行结果：" + result);        } finally {            DBUtils.freeConnection();            DBUtils.closeQuietly(stmt);            DBUtils.closeDataSource();        }    }}

可是一直报MySQL语法异常：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 2	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)	at com.mysql.jdbc.Util.getInstance(Util.java:386)	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:813)	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:656)	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)	at com.alipay.mbill.loaddata.LoadDataTest.test_loadData(LoadDataTest.java:31)	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)	at java.lang.reflect.Method.invoke(Method.java:597)	at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)	at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)	at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)	at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)	at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)	at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)	at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)	at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)	at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)	at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)	at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)	at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

可是把这条语句拿到MySQL命令行中，却是对的，在网上找了一大圈，也没有找到结果，于是自己Debug调试一下，发现SQL的内容如下：

load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by ''' lines terminated by ''

我X，竟然SQL内容都断开了，那当然不行了，这完全跟在命令行的SQL内容不一样啊，顿时豁然开朗，原来在Java的SQL中少加了转义字符，把SQL改成如下，便可测试通过：

String sql = "load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by '//'' lines terminated by '//r//n'";

这样，最终的SQL内容才为：load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by '/'' lines terminated by '/r/n'

 

附上数据表结构和测试文件内容：

CREATE TABLE `test_key_value` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `key` varchar(32) CHARACTER SET latin1 DEFAULT NULL,  `value` varchar(128) CHARACTER SET latin1 DEFAULT NULL,  `gmt_create` timestamp NULL DEFAULT NULL,  `gmt_modify` timestamp NULL DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk

测试文件内容：

'1','KEY01','Value01','2012-06-08 15:50:30','2012-06-08 16:50:30''2','KEY02','Value02','2012-06-08 15:50:30','2012-06-08 16:50:30''3','KEY03','Value03','2012-06-08 15:50:30','2012-06-08 16:50:30''4','KEY04','Value04','2012-06-08 15:50:30','2012-06-08 16:50:30''5','KEY05','Value05','2012-06-08 15:50:30','2012-06-08 16:50:30'

bitsCN.com