perl链接数据库实例

目标：方便自己快速查询数据库表记录；

示例：

数据库主机：127.0.0.1

数据库名称：tmp

数据库表名：tmp

表结构：

mysql>?desc?tmp.tmp;
+------------+--------------+------+-----+---------+----------------+
|?Field??????|?Type?????????|?Null?|?Key?|?Default?|?Extra??????????|
+------------+--------------+------+-----+---------+----------------+
|?id?????????|?int(11)??????|?NO???|?PRI?|?NULL????|?auto_increment?|
|?PhoneNum???|?char(11)?????|?NO???|?MUL?|?NULL????|????????????????|
|?status?????|?int(11)??????|?NO???|?????|?NULL????|????????????????|
|?statusDesc?|?varchar(255)?|?YES??|?????|?NULL????|????????????????|
+------------+--------------+------+-----+---------+----------------+
4?rows?in?set?(0.04?sec)

表数据：

mysql>?select?*?from?tmp.tmp;
+----+-------------+--------+------------+
|?id?|?PhoneNum????|?status?|?statusDesc?|
+----+-------------+--------+------------+
|??1?|?18600000000?|?????10?|?北京???????|
|??2?|?18600000001?|?????21?|?上海???????|
|??3?|?18600000002?|?????10?|????????????|
|??4?|?18600000000?|?????10?|?NULL???????|
+----+-------------+--------+------------+
4?rows?in?set?(0.00?sec)

查询代码：

#!D:Perl64bin

use?DBI;
use?strict;
#use?warnings;

if?(@ARGV?!=?1)?{
????print?"Please?input?a?Phone?Num?~?nEg:perl?sql.pl?18600000000?~?n";
}else?{
my?$tmp?=?{
????user?=>?'tmp',????????????
????pass?=>?'tmp',????????????
????host?=>?'127.0.0.1',????database?=>?'tmp',????port?=>?3306,};

my?$Phone_num?=?shift;
chomp?$Phone_num;

##链接数据库
my?$tmp_dbh=DBI->connect("DBI:mysql:$tmp->{database};host=$tmp->{host};port=$tmp->{port}",$tmp->{user},$tmp->{pass},{RaiseError=>1});
##设置数据库编码
$tmp_dbh->do?("set?character_set_client?=?'gb2312'");
$tmp_dbh->do?("set?character_set_connection?=?'gb2312'");
$tmp_dbh->do?("set?character_set_results?=?'gb2312'");
###查询语句
my?$tmp_sql=qq{select?*?from?tmp?where?phoneNum?=?$Phone_num?;};
my?$tmp_sth=$tmp_dbh->prepare($tmp_sql)?or?die?'Unable?to?perpare?our?query:'.$tmp_dbh->errstr."n";
###执行语句
my?$results=$tmp_sth->execute()?or?die?'Unable?to?execute?our?query:'.$tmp_dbh->errstr."n";
###如果没有结构，打印N/A
print?"N/A"?if?$results?==?0;
###将结果以hash方式引用给$reslut_ref
while?(my?$reslut_ref?=?$tmp_sth->fetchrow_hashref){

????print?"---------------New?Line--------------n";
????print?"id????????:????$reslut_ref->{id}n";
????print?"PhoneNum????:????$reslut_ref->{PhoneNum}n";
????print?"status????????:????$reslut_ref->{status}n";
????print?"statusDesc????:????$reslut_ref->{statusDesc}n";
}
#####介于statusDesc字段可能为NULL，打印时会提示该变量未初始化，这也是上面关闭warnings的原因，当然也可以局部关闭；
$tmp_sth->finish();
$tmp_dbh->disconnect();
}

执行结果；

E:WorkspacesMyEclipse10PerlProject>perl?sql.pl
Please?input?a?Phone?Num?~
Eg:perl?sql.pl?18600000000?~

E:WorkspacesMyEclipse10PerlProject>perl?sql.pl?18600000000
---------------New?Line--------------
id??????????????:???????1
PhoneNum????????:???????18600000000
status??????????:???????10
statusDesc??????:???????北京
---------------New?Line--------------
id??????????????:???????4
PhoneNum????????:???????18600000000
status??????????:???????10
statusDesc??????:

E:WorkspacesMyEclipse10PerlProject>