使用DBI(perl)實現文本文件的導入導出mysql

DBI 是perl腳本連接數據庫的一個模塊。
perl腳本相對shell更靈活，功能更強大，跨平臺能力強。相對可執行jar包要簡單很多。

?1、下載安裝包
DBI-1.631.tar.gz
perl腳本下載的網站http://www.cpan.org/
很多perl的組件都可以在這個網站上下載

2、解壓
tar -xzvf DBI-1.631.tar.gz
cd DBI-1.631
perl Makefile.PL
make
make test

3、DBD安裝
下載DBD-mysql-4.032.tar.gz （地址同1）
cd DBD-mysql-4.032
perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
make
make test
make install

4、其它可能發生的狀況
如果運行某個調用MYSQL數據庫的程序時出現如下系統提示：
install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.6.1/i386-linux/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib/perl5/site_perl/5.6.1/i386-linux/auto/DBD/mysql/mysql.so: undefined symbol: uncompress at /usr/lib/perl5/5.6.1/i386-linux/DynaLoader.pm line 206. at (eval 2) line 3
那么說明DBD::MYSQL未安裝成功。
此時將/usr/local/mysql/include/mysql/*
復制到/usr/include/
同時將/usr/local/mysql/lib/mysql/*
復制到/usr/lib/(64位系統/usr/lib64/)
然后使用perl Makefile.PL設置環境，再進行后續的正常安裝，就能正確連接到MYSQL數據庫。

/** 32位系統的解決辦法
cp -rp /usr/local/mysql/include/mysql* /usr/include/
cp -rp /usr/local/mysql/lib/* /usr/lib/
**/
5、導入導出腳本實例

導入
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "root", "admin", {'RaiseError' => 1});
my $rows = $dbh->do("LOAD DATA LOCAL INFILE 'test_data.txt' INTO TABLE users FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'");
print "$rows row(s) affected ";

參考
http://www.cnblogs.com/ggjucheng/archive/2012/11/05/2755683.html

導出：
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "root", "admin", {'RaiseError' => 1});
my $rows = $dbh->do("select * from users into outfile '/tmp/test_out_data.txt' fields terminated by '\t' enclosed by '\"' lines terminated by '\n'");
print "$rows row(s) affected ";
參考：
http://www.cnblogs.com/wxb-km/archive/2012/10/23/2736394.html