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
