perl mysql dbi_使用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 ";

导出：

#!/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