一、概念理解：perl DBI 學習總結

DBI和DBD的不同關係模型：

perl >> perl-DBI >>DBD

DBI的設計的作用是使用你忽略不同廠商的庫的細節。它為你想要使用的SQL查詢提供簡單的介面。DBI不知道怎樣和不同的資料庫部分進行交流，但是它知道怎樣找到和載入DBD（Database Driver）模組。在DBD模組裡，有不同廠商的庫，並且DBD知道怎樣去和真正的資料庫交流。對於不同的資料庫，有一個DBD模組。

當你想讓DBI做一個查詢的時候，它會傳送這個查詢給適當的DBD模組，DBD將和真正的資料庫互動。當它得到返回結構時，它將結果傳送給DBI。然後DBI將結果給你。所以，你的程式只需要和DBI進行互動，而不是和真正的資料庫。
看到這裡會發現，DBI就是類似java連線資料庫的JDBC的玩意兒。

二、DBI安裝：DBI-1.634.tar.gz   DBD-mysql-4.032.tar.gz
DBI 最新下載包連結： style="font-size:18px;line-height:25.7142868041992px;white-space:normal;">
DBD 最新下載包連結： style="font-size:18px;line-height:25.7142868041992px;white-space:normal;">
capn：是per檔案庫，我把它理解為perl 資料大全庫
#tar -zxvf DBI-1.604.tar.gz
#cd DBI-1.604
#perl Makefile.PL
#make
#make test
#make install
三、DBD的安裝：
DBD的安裝需要mysql_config，mysql_config在安裝包MySQL-devel-XXXXXX.i386.rpm
以下是安裝的截圖，中間有刪節：

[root@red55 package]# cd DBD-mysql-4.032
[root@red55 DBD-mysql-4.032]# ls
ChangeLog dbdimp.h LICENSE MANIFEST META.yml README.pod
constants.h eg Makefile.PL MANIFEST.SKIP myld t
dbdimp.c lib Makefile.PL.embedded META.json mysql.xs TODO
[root@red55 DBD-mysql-4.032]# perl Makefile.PL
Can't exec "mysql_config": 沒有那個檔案或目錄 at Makefile.PL line 73.

Cannot find the file 'mysql_config'! Your execution PATH doesn't seem
not contain the path to mysql_config. Resorting to guessed values!
mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';

You can also optionally set the user to run 'make test' with:

perl Makefile.PL --testuser=username

Can't exec "mysql_config": 沒有那個檔案或目錄 at Makefile.PL line 481.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
Failed to determine directory of mysql.h. Use
perl Makefile.PL --cflags=-I
to set this directory. For details see DBD::mysql::INSTALL,
section "C Compiler flags" or type
perl Makefile.PL --help
[root@red55 ~]# rpm -ivh /tmp/package/MySQL-devel-5.6.25-1.linux_glibc2.5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-devel ########################################### [100%]
[root@red55 ~]# find / -name mysql_config
/usr/bin/mysql_config

[root@red55 DBD-mysql-4.032]# perl Makefile.PL
PLEASE NOTE:
For 'make test' to run properly, you must ensure that the
database user 'root' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.
mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';
You can also optionally set the user to run 'make test' with:
perl Makefile.PL --testuser=username
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing
embedded (guessed ) =
ldflags (mysql_config) =
libs (mysql_config) = -L/usr/lib -lmysqlclient -lpthread -lm -lrt -ldl
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testport (default ) =
testsocket (default ) =
testuser (guessed ) = root
To change these settings, see 'perl Makefile.PL --help' and
'perldoc DBD::mysql::INSTALL'.
Checking if your kit is complete...
Looks good
Warning: prerequisite Test::Deep 0 not found.
Warning: prerequisite Test::Simple 0.90 not found. We have 0.62.
Multiple copies of Driver.xst found in: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/ /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/auto/DBI/ at Makefile.PL line 771
Using DBI 1.634 (for perl 5.008008 on i386-linux-thread-multi) installed in /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/
Writing Makefile for DBD::mysql
[root@red55 DBD-mysql-4.032]# make
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp README.pod blib/lib/DBD/README.pod
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
gcc -c -I/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -DVERSION=\"4.032\" -DXS_VERSION=\"4.032\" -fPIC "-I/usr/lib/perl5/5.8.8/i386-linux-thread-multi/CORE" dbdimp.c
dbdimp.c: In function ‘dbd_discon_all’:
dbdimp.c:2291: 警告：未使用的變數 ‘imp_xxh’
dbdimp.c: In function ‘mysql_st_prepare’:
dbdimp.c:2747: 警告：未使用的變數 ‘limit_flag’
/usr/bin/perl -p -e "s/~DRIVER~/mysql/g" /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/Driver.xst > mysql.xsi
/usr/bin/perl /usr/lib/perl5/5.8.8/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap mysql.xs > mysql.xsc && mv mysql.xsc mysql.c
Warning: duplicate function definition 'do' detected in mysql.xs, line 242
Warning: duplicate function definition 'rows' detected in mysql.xs, line 754
gcc -c -I/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -DVERSION=\"4.032\" -DXS_VERSION=\"4.032\" -fPIC "-I/usr/lib/perl5/5.8.8/i386-linux-thread-multi/CORE" mysql.c
mysql.xs: In function ‘XS_DBD__mysql__db_do’:
mysql.xs:260: 警告：未使用的變數 ‘statement_ptr’
mysql.xs: In function ‘XS_DBD__mysql__st_more_results’:
mysql.xs:691: 警告：未使用的變數 ‘retval’
Running Mkbootstrap for DBD::mysql ()
chmod 644 mysql.bs
rm -f blib/arch/auto/DBD/mysql/mysql.so
/usr/bin/perl myld gcc -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -L/usr/local/lib dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so \
-L/usr/lib -lmysqlclient -lpthread -lm -lrt -ldl \

chmod 755 blib/arch/auto/DBD/mysql/mysql.so
cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs
chmod 644 blib/arch/auto/DBD/mysql/mysql.bs
Manifying blib/man3/DBD::mysql.3pm
lib/DBD/mysql.pm:891: Unknown command paragraph "=encoding utf8"
Manifying blib/man3/DBD::README.3pm
Manifying blib/man3/DBD::mysql::INSTALL.3pm
lib/DBD/mysql/INSTALL.pod:1: Unknown command paragraph "=encoding utf8"
Manifying blib/man3/Bundle::DBD::mysql.3pm
[root@red55 DBD-mysql-4.032]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.............................ok 2/6# Driver version is 4.032
t/00base.............................ok
t/05dbcreate.........................# DBI connect('','root',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at t/05dbcreate.t line 13

t/version............................Bareword "done_testing" not allowed while "strict subs" in use at t/version.t line 16.
Execution of t/version.t aborted due to compilation errors.
# Looks like your test died before it could output anything.
t/version............................dubious
Test returned status 255 (wstat 65280, 0xff00)
Failed Test Stat Wstat Total Fail Failed List of Failed
-------------------------------------------------------------------------------
t/10connect.t 255 65280 ?? ?? % ??
t/30insertfetch.t 255 65280 ?? ?? % ??
t/35prepare.t 255 65280 ?? ?? % ??
t/40blobs.t 255 65280 ?? ?? % ??
t/40nulls_prepare.t 255 65280 ?? ?? % ??
t/40server_prepare_error.t 255 65280 ?? ?? % ??
t/41blobs_prepare.t 255 65280 ?? ?? % ??
t/53comment.t 255 65280 ?? ?? % ??
t/60leaks.t 255 65280 ?? ?? % ??
t/70takeimp.t 255 65280 ?? ?? % ??
t/87async.t 2 512 ?? ?? % ??
t/90no-async.t 255 65280 ?? ?? % ??
t/rt50304-column_info_parentheses 255 65280 ?? ?? % ??
t/rt83494-quotes-comments.t 255 65280 ?? ?? % ??
t/rt85919-fetch-lost-connection.t 255 65280 ?? ?? % ??
t/rt86153-reconnect-fail-memory.t 255 65280 ?? ?? % ??
t/rt88006-bit-prepare.t 255 65280 ?? ?? % ??
t/version.t 255 65280 ?? ?? % ??
43 tests skipped.
Failed 18/62 test scripts, 70.97% okay. 0/6 subtests failed, 100.00% okay.
make: *** [test_dynamic] 錯誤 255
[root@red55 DBD-mysql-4.032]# make install
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.bs
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/Bundle/DBD/mysql.pm
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/README.pod
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql.pm
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql/INSTALL.pod
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql/GetInfo.pm
Installing /usr/share/man/man3/DBD::mysql.3pm
Installing /usr/share/man/man3/DBD::mysql::INSTALL.3pm
Installing /usr/share/man/man3/Bundle::DBD::mysql.3pm
Installing /usr/share/man/man3/DBD::README.3pm
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod

四、最後就是測試DBD安裝是否成功，使用如下連線程式碼：

[mysql@red55 perl 17:35:46]$ vi test.pl
#!/bin/perl
use DBI;
# Connect to target DB
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost","system","123456", {'RaiseError' => 1});
# Insert one row
my $rows = $dbh->do("INSERT INTO stu1 (id, username) VALUES (7, 'xiang')");
# query
my $sqr = $dbh->prepare("SELECT username FROM stu1");
$sqr->execute();
while(my $ref = $sqr->fetchrow_hashref()) {
print "$ref->{'username'}\n";
}
$dbh->disconnect();
"test.pl" 19L, 423C 已寫入
[mysql@red55 perl 17:36:25]$perl test.pl
xiebin
wenqing
xiang
xiang
xiang

大功告成，連線成功。DBD安裝好了
五、看到網上也有同學採用如下命令安裝：yum install perl-DBD-MySQL。採用這個命令安裝會出現如下錯誤：
Transaction Check Error:
file /etc/my.cnf from install of mysql-5.0.77-4.el5_5.4.i386 conflicts with file from package MySQL-server-5.6.25-1.linux_glibc2.5.i386
原因是：perl-DBD-MySQL對應了5.0版本的mysql，而系統安裝5.6的版本。解決辦法是像上面一樣，手動安裝DBD。

perl DBI DBD和java 的JDBC對比學習

相關文章