java 是否有任何 JDBC 驱动程序支持 LOAD DATA INFILE sql 命令？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17313921/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does any JDBC driver supports LOAD DATA INFILE sql command?
提问by Umesh K
Hi I would like to create table through JDBC on multiple databases like DB2, Sybase, MySQL etc. Now I need to create this table using text file say data.txt which contains data space separated values. For e.g.
嗨，我想通过 JDBC 在多个数据库（如 DB2、Sybase、MySQL 等）上创建表。现在我需要使用包含数据空间分隔值的文本文件说 data.txt 创建此表。例如
CustName OrderNo PhoneNo
XYZ 230 123456789
ABC 450 879641238
Now this data.txt contains thousands of records space separated values. I need to parse this file line by line using java io and execute sql insert queries for each records.
现在这个 data.txt 包含数以千计的记录空间分隔值。我需要使用 java io 逐行解析这个文件，并为每条记录执行 sql 插入查询。
I found there is LOAD DATA INFILE sql command. Does any JDBC driver supports this command? If not what should be the best efficient fast approach to solve this problem.
我发现有 LOAD DATA INFILE sql 命令。是否有任何 JDBC 驱动程序支持此命令？如果不是什么应该是解决这个问题的最有效的快速方法。
Please guide. Thanks in advance.
请指导。提前致谢。
回答by a_horse_with_no_name
The following will work through JDBC. Note that to use LOAD DATA INFILEyou need superuser privilege. Which you don't need for LOAD DATA LOCAL INFILE
以下将通过 JDBC 工作。请注意，要使用LOAD DATA INFILE您需要超级用户权限。你不需要的LOAD DATA LOCAL INFILE
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/foobar", "root", "password");
Statement stmt = con.createStatement();
String sql =
"load data infile 'c:/temp/some_data.txt' \n" +
" replace \n" +
" into table prd \n" +
" columns terminated by '\t' \n" +
" ignore 1 lines";
stmt.execute(sql);
If you use LOAD DATA INFILEthe file location is based on the server's filesystem! If you use a local file, then obviously it's based on the client's filesystem.
如果使用LOAD DATA INFILE的文件位置是基于服务器的文件系统！如果您使用本地文件，那么显然它基于客户端的文件系统。
回答by DaveH
I think LOAD DATA INFILEis specific to mySql, and I doubt whether a JDBC driver would support it. Other databases will have similar ( but different ) utilities
我认为LOAD DATA INFILE特定于 mySql，我怀疑 JDBC 驱动程序是否会支持它。其他数据库将具有类似（但不同）的实用程序
If you want to do this is a database independent way I think you have two choices
如果你想这样做是一种独立于数据库的方式我认为你有两种选择
- Parse up the input file and use SQL INSERT statements over a JDBC connection
- Write a number of different, database dependent scripts, determine which dbms you are using and execute the correct one using Runtime.exec
- 解析输入文件并通过 JDBC 连接使用 SQL INSERT 语句
- 编写许多不同的数据库相关脚本，确定您正在使用的 dbms 并使用 Runtime.exec 执行正确的脚本
Unless you have compelling performance reasons not to, I'd go for option 1.
除非您有令人信服的性能原因不这样做，否则我会选择选项 1。
回答by NINCOMPOOP
I believe LOAD DATA INFILEis faster than parsing the file and inserting the records using Java . You can execute the query for load data infilethrough JDBC. As per this Oracle docand MySql doc:
我相信LOAD DATA INFILE比使用 Java 解析文件和插入记录更快。您可以load data infile通过 JDBC执行查询。根据此Oracle 文档和MySql 文档：
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.
LOAD DATA INFILE 语句以非常高的速度将文本文件中的行读取到表中。
The file should be in server . You can try both the approaches, log the time each one of them consume.
该文件应该在 server 中。您可以尝试这两种方法，记录每种方法消耗的时间。
回答by Blind TeamKiller
"Load data local infile" does work with MySQL's JDBC driver, there are some issues with this.
“加载本地数据文件”确实适用于 MySQL 的 JDBC 驱动程序，但存在一些问题。
When using "load data infile" or "load data local infile" the inserted records WILL NOTbe added to the bin log, this means that if you are using replication the records inserted by "load data infile" will not be transferred to the slave server(s), the inserted records will not have any transactions record, and this is why load data infile is so much quicker than a standard insert and due to no validation on the inserted data.
当使用“ load data infile”或“ load data local infile”时，插入的记录不会被添加到bin log中，这意味着如果你使用复制，“load data infile”插入的记录不会被传输到slave服务器，插入的记录将没有任何事务记录，这就是为什么加载数据 infile 比标准插入快得多，并且由于没有对插入的数据进行验证。
