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

熬死你的

　　今天测试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'

　　
　　
　　根据MySQL的官方文档，继续完善测试用例，可以Load部分数据：



public class LoadDataTest {
/**
* 完全导入，包括自增字段
*/
@Test
public void test_loadData_ALL() 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' replace into table test_key_value character set GBK 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();
}
}
/**
* 部分导入，自增字段自动加1
*/
@Test
public void test_loadData_PART() throws Exception {
Connection conn = null;
Statement stmt = null;
try {
conn = DBUtils.fetchConnection();
stmt = conn.createStatement();
String sql = "load data infile 'c:/test_key_value_02.txt' replace into table test_key_value character set GBK fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n' (`key`,`value`,`gmt_create`,`gmt_modify`)";
boolean result = stmt.execute(sql);
System.out.println("Load执行结果：" + result);
} finally {
DBUtils.freeConnection();
DBUtils.closeQuietly(stmt);
DBUtils.closeDataSource();
}
}
}