[整理]MySql批量数据导入Load data infile解决方案
有時候我們需要將大量數據批量寫入數據庫，直接使用程序語言和Sql寫入往往很耗時間，其中有一種方案就是使用MySql Load data infile導入文件的形式導入數據，這樣可大大縮短數據導入時間。
假如是從MySql客戶端調用，將客戶端的文件導入，則需要使用 load local data infile.
LOAD DATA INFILE 語句以很高的速度從一個文本文件中讀取行到一個表中。文件名必須是一個文字字符串。
1，開啟load local data infile.
假如是Linux下編譯安裝，
如果使用源碼編譯的MySQL，在configure的時候，需要添加參數：--enable-local-infile 客戶端和服務器端都需要，否則不能使用local參數。
./configure --prefix=/usr/local/mysql --enable-local-infile
make install
若是其它系統，可在配置文件中配置：
在MySql 配置文件My.ini文件中下面項中加入local-infile=1：
add:
[mysqld]
local-infile=1
[mysql]
local-infile=1
客戶端和服務端度需要開啟，對于客戶端也可以在執行命中加上--local-infile=1 參數：
mysql --local-infile=1 -uroot -pyourpwd yourdbname
如:
如：/usr/local/mysql/bin/mysql -uroot -h192.168.0.2 -proot databaseName --local-infile=1 -e "LOAD DATA LOCAL INFILE 'data.txt' into table test(name,sex) "
2, 編碼格式注意：
若包含中文，請保證導入文件、連接字符串、導入表都是UTF-8編碼。
3，執行
在使用LOAD DATA到MySQL的時候，有2種情況：
（1）在遠程客戶端（需要添加選項：--local-infile=1）導入遠程客戶端文本到MySQL，需指定LOCAL（默認就是ignore）,加ignore選項會放棄數據，加replace選項會更新數據，都不會出現唯一性約束問題。
[zhuxu@xentest9-vm1 tmp]$mysql -uzhuxu -pzhuxu test -h10.254.5.151 --local-infile=1--show-warnings -v -v -v \
> -e "LOAD DATA LOCAL INFILE '/tmp/2.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ','";
（2）在本地服務器導入本地服務器文本到MySQL，不指定LOACL，出現唯一性約束沖突，會失敗回滾，數據導入不進去，這個時候就需要加ignore或者replace來導入數據。
mysql>LOAD DATA INFILE '/home/zhuxu/1.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY ',';
4，事務分析
步驟是這樣的：
1，開啟binlog，設置binlog_format=row，執行reset master；
2，load data infile xxxxx；
3，查看binlog。
可以看出，總共是一個事務，也通過mysqlbinlog查看了binary log，確認中間是被拆分成了多個insert形式。所以load data infile基本上是這樣執行的：
begin
insert into values(),(),(),()...
insert into values(),(),(),()...
insert into values(),(),(),()...
...
...
commit
當然，由于row格式的binlog的語句并不是很明顯的記錄成多值insert語句，它的格式時
insert into table
set @1=
set @2=
...
set @n=
insert into table
set @1=
set @2=
...
set @n=
insert ...
;注意這里有一個分號‘;’，其實前面這一部分就相當于前面說的多值insert形式
然后接下來就重復上面的那種格式，也就是一個load data infile 拆成了多個多值insert語句。
前面說的是row格式記錄的load data infile，那么對于statement是怎么樣的呢？statement格式的binlog，它是這樣記錄的，binlog中還是同樣的load data語句，但是在記錄load data 語句之前，它會先將你master上這個load data 使用到的csv格式的文件拆分成多個部分，然后傳到slave上（在mysql的tmpdir下），當然傳這些csv格式的文件也會記錄binlog event，然后最后真正的SQL語句形式就是load data local infile '/tmp/SQL_X_Y'這種形式（這里假設mysql的tmpdir是默認的/tmp），實際上這樣很危險，比如tmpdir空間不夠，那就會報錯。不過從效率上來說兩者可能差不多，因為statement格式的binlog也是拆分成了多個語句。
附：
（1）load data infile 和 load local data infile 在 innodb和MyISAM 同步方面的區別
對MyISAM引擎：
(1)對master服務器進行 ‘load’ 操作，
(2)在master上所操作的load.txt文件，會同步傳輸到slave上，并在tmp_dir 目錄下生成 load.txt文件
master服務器插入了多少，就傳給slave多少
(3)當master上的load操作完成后，傳給slave的文件也結束時，
即：在slave上生成完整的 load.txt文件
此時，slave才開始從 load.txt 讀取數據，并將數據插入到本地的表中
對innodb引擎：
(1)主數據庫進行 ‘Load’ 操作
(2)主數據庫操作完成后，才開始向slave傳輸 load.txt文件，
slave接受文件，并在 tmp_dir 目錄下生成 load.txt 文件
接受并生成完整的load.txt 后，才開始讀取該文件，并將數據插入到本地表中
異常情況處理：
1）對MyISAM引擎
當數據庫執行load,此時如果中斷：
Slave端將報錯，例如：
####################################################################
Query partially completed on the master (error on master: 1053) and was aborted.
There is a chance that your master is inconsistent at this point.
If you are sure that your master is ok,
run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE; . Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-2-1-3.data' IGNORE INTO TABLE `test_1`
FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`id`, `name`, `address`)'
###########################################################################################
按照提示，在slave服務器上：
(1) 使用提示的load命令，將主服務器傳輸過來的load文件，在從服務器上執行
(2)讓從服務器跳過錯誤。set global sql_slave_skip_counter=1;
(3)開啟同步
2）對Innodb引擎
由于innodb是事務型的，所以會把load文件的整個操作當作一個事務來處理，
中途中斷load操作，會導致回滾。
與此相關的一些參數：
max_binlog_cache_size----能夠使用的最大cache內存大小。
當執行多語句事務時，max_binlog_cache_size如果不夠大，
系統可能會報出“Multi-statement
transaction required more than 'max_binlog_cache_size' bytes of storage”的錯誤。
備注：以load data 來說，如果load的文件大小為512M，在執行load 的過程中，
所有產生的binlog會先寫入binlog_cache_size,直到load data 的操作結束后，
最后，再由binlog_cache_size 寫入二進制日志，如mysql-bin.0000008等。
所以此參數的大小必須大于所要load 的文件的大小，或者當前所要進行的事務操作的大小。
max_binlog_size------------Binlog最大值，一般設置為512M或1GB，但不能超過1GB。
該設置并不能嚴格控制Binlog的大小，尤其是Binlog遇到一根比較大事務時，
為了保證事務的完整性，不可能做切換日志的動作，只能將該事務的所有SQL都記錄進
當前日志，直到事務結束
備注：有時能看到，binlog生成的大小，超過了設定的1G。這就是因為innodb某個事務的操作比較大，
不能做切換日志操作，就全部寫入當前日志，直到事務結束。
（2）C# 批量插入Mysql
public void loadData(Connection connection)
{
long starTime = System.currentTimeMillis();
String sqlString = "load data local infile ? into table test";
PreparedStatement pstmt;
try {
pstmt = connection.prepareStatement(sqlString);
pstmt.setString(1, "tfacts_result");
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
long endTime = System.currentTimeMillis();
System.out.println("program runs " + (endTime - starTime) + "ms");
}
public static void mysql_batch(string sqlStr,int point)
{
string sql = "insert into test(node1, node2, weight) values(?, ?, ?)";
Connection conn = getConn("mysql");
conn.setAutoCommit(false);
//clear(conn);
try
{
PreparedStatement prest = conn.prepareStatement(sql);
//long a = System.currentTimeMillis();
for (int x = 1; x <= count; x++)
{
prest.setInt(1, x);
prest.setString(2, "張三");
prest.addBatch();
if (x % point == 0)
{
prest.executeBatch();
conn.commit();
}
}
prest.close();
//long b = System.currentTimeMillis();
//print("MySql批量插入10萬條記錄", a, b, point);
}
catch (Exception ex)
{
ex.printStackTrace();
}
finally
{
close(conn);
}
引用：
http://blog.csdn.net/zbszhangbosen/article/details/7947991
http://www.cnblogs.com/zeroone/archive/2013/05/06/3062488.html
轉載于:https://blog.51cto.com/babyhe/1310602
總結
以上是生活随笔為你收集整理的[整理]MySql批量数据导入Load data infile解决方案的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Android ContentProvi
- 下一篇： 字符串截取函数