MySQL使用JDBCLoadDataInFile导入数据注意事项_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL使用JDBCLoadDataInFile导入数据注意事项_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5440字，纯文字阅读大概需要8分钟。
内容图文
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
内容总结
以上是互联网集市为您收集整理的MySQL使用JDBCLoadDataInFile导入数据注意事项_MySQL全部内容，希望文章能够帮你解决MySQL使用JDBCLoadDataInFile导入数据注意事项_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】