Java不寫檔案，LOAD DATA LOCAL INFILE大大量匯入資料到MySQL的實現

標籤：spring   jdbc   mysql   db2   

Mysql loaddata的使用

 

       資料庫中，最常見的寫入資料方式是通過SQLINSERT來寫入，另外就是通過備份檔案恢複資料庫，這種備份檔案在MySQL中是SQL指令碼，實際上執行的還是在批量INSERT語句。

       在實際中，常常會遇到兩類問題：一類是資料匯入，比如從word、excel表格或者txt文檔匯入資料（這些資料一般來自於非技術人員通過OFFICE工具錄入的文檔）；一類資料交換，比如從MySQL、Oracle、DB2資料庫之間的資料交換。

       這其中就面臨一個問題：資料庫SQL指令碼有差異，SQL交換比較麻煩。但是幾乎所有的資料庫都支援文本資料匯入(LOAD)匯出(EXPORT)功能。利用這一點，就可以解決上面所提到的資料交換和匯入問題。

       MySQL的LOAD DATAINFILE語句用於高速地從一個文字檔中讀取行，並裝入一個表中。檔案名稱必須為一個文字字串。下面以MySQL5為例說明，說明如何使用MySQL的LOADDATA命令實現文本資料的匯入。

       注意：這裡所說的文本是有一定格式的文本，比如說，文本分行，每行中用相同的符號隔開文本等等。等等，擷取這樣的文本方法也非常的多，比如可以把word、excel表格儲存成文本，或者是一個csv檔案。

 

在我的項目中，使用的環境是快速上傳一個csv檔案，原系統中是使用的db2資料庫，然後調用了與mysql的loaddata相似的一個函數sysproc.db2load。但是loaddata在mysql的預存程序是不能使用的。採取的方法時在java代碼中調用此方法。

 

實現的例子：

準備測試表 

SQL如下：

use test;CREATE TABLE `test` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `a` int(11) NOT NULL,  `b` bigint(20) unsigned NOT NULL,  `c` bigint(20) unsigned NOT NULL,  `d` int(10) unsigned NOT NULL,  `e` int(10) unsigned NOT NULL,  `f` int(10) unsigned NOT NULL,  PRIMARY KEY (`id`),  KEY `a_b` (`a`,`b`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Java代碼如下：

package com.seven.dbTools.DBTools;import java.io.ByteArrayInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import org.springframework.jdbc.core.JdbcTemplate;import javax.sql.DataSource;import org.apache.log4j.Logger;/** * @author seven * @since 07.03.2013 */public class BulkLoadData2MySQL {private static final Logger logger = Logger.getLogger(BulkLoadData2MySQL.class);private JdbcTemplate jdbcTemplate;private Connection conn = null;public void setDataSource(DataSource dataSource) {this.jdbcTemplate = new JdbcTemplate(dataSource);}public static InputStream getTestDataInputStream() {StringBuilder builder = new StringBuilder();for (int i = 1; i <= 10; i++) {for (int j = 0; j <= 10000; j++) {builder.append(4);builder.append("\t");builder.append(4 + 1);builder.append("\t");builder.append(4 + 2);builder.append("\t");builder.append(4 + 3);builder.append("\t");builder.append(4 + 4);builder.append("\t");builder.append(4 + 5);builder.append("\n");}}byte[] bytes = builder.toString().getBytes();InputStream is = new ByteArrayInputStream(bytes);return is;}/** *  * load bulk data from InputStream to MySQL */public int bulkLoadFromInputStream(String loadDataSql,InputStream dataStream) throws SQLException {if(dataStream==null){logger.info("InputStream is null ,No data is imported");return 0;}conn = jdbcTemplate.getDataSource().getConnection();PreparedStatement statement = conn.prepareStatement(loadDataSql);int result = 0;if (statement.isWrapperFor(com.mysql.jdbc.Statement.class)) {com.mysql.jdbc.PreparedStatement mysqlStatement = statement.unwrap(com.mysql.jdbc.PreparedStatement.class);mysqlStatement.setLocalInfileInputStream(dataStream);result = mysqlStatement.executeUpdate();}return result;}public static void main(String[] args) {String testSql = "LOAD DATA LOCAL INFILE 'sql.csv' IGNORE INTO TABLE test.test (a,b,c,d,e,f)";InputStream dataStream = getTestDataInputStream();BulkLoadData2MySQL dao = new BulkLoadData2MySQL();try {long beginTime=System.currentTimeMillis();int rows=dao.bulkLoadFromInputStream(testSql, dataStream);long endTime=System.currentTimeMillis();logger.info("importing "+rows+" rows data into mysql and cost "+(endTime-beginTime)+" ms!");} catch (SQLException e) {e.printStackTrace();}System.exit(1);}}

提示：

例子中的代碼使用setLocalInfileInputStream方法，會直接忽略掉檔案名稱，而直接將IO流匯入到資料庫中。在實際的實現中也可以把檔案上傳到伺服器，然後讀檔案再匯入檔案，此時load data的local參數應該去掉，並且檔案名稱應該是完整的絕對路徑的名字。

最後附上LOAD DATA INFILE文法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'    [REPLACE | IGNORE]    INTO TABLE tbl_name    [FIELDS        [TERMINATED BY 'string']        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char' ]    ]    [LINES        [STARTING BY 'string']        [TERMINATED BY 'string']    ]    [IGNORE number LINES]    [(col_name_or_user_var,...)]    [SET col_name = expr,...)] SELECT文法    [ALL | DISTINCT | DISTINCTROW ]      [HIGH_PRIORITY]      [STRAIGHT_JOIN]      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]    select_expr, ...    [INTO OUTFILE 'file_name' export_options      | INTO DUMPFILE 'file_name']    [FROM table_references    [WHERE where_definition]    [GROUP BY {col_name | expr | position}      [ASC | DESC], ... [WITH ROLLUP]]    [HAVING where_definition]    [ORDER BY {col_name | expr | position}      [ASC | DESC] , ...]    [LIMIT {[offset,] row_count | row_count OFFSET offset}]    [PROCEDURE procedure_name(argument_list)]    [FOR UPDATE | LOCK IN SHARE MODE]]

總結

 

LOADDATA是一個很有用的命令，從檔案中匯入資料比insert語句要快，MySQL文檔上說要快20倍左右。但是命令的選項很多，然而大多都用不到，如果真的需要，用的時候看看官方文檔即可。

Java不寫檔案，LOAD DATA LOCAL INFILE大大量匯入資料到MySQL的實現