Linux环境下Perl语言对数据库的操纵

实例：同一主机下的运行
主机配置：PIII450 128M 15GB
操作系统：Red Hat Linux 6.1
数据库：MySQL-3.22.29
建议使用RPM方式安装，至少要安装下面三个包：
MySQL-3.22.29-1.i386.rpm
MySQL-client-3.22.29-1.i386.rpm
MySQL-devel-3.22.29-1.i386.rpm
WWW服务器：Apache 1.3.6 for Linux
Perl 解释器：version 5.005_03 built for i386-linux
DBI: 版本为：1.13
Data-ShowTable: 版本为：3.3
DBD: Msql-Mysql-modules-1.2018

本人成功地在上述环境下实现了对MySQL数据库的访问。与Windows环境下的数据库不同，它不需要建立外部数据源.

下面是一个应用的简单例子：

为了统计用户访问我们的网站的次数，我们建立了一个数据库, 在其中建一个表，表名为：usedata, 共有三列：userno,userpass,lognum,分别代表用户名,密码,登录次数.

假定我们使用test数据库，在该库中创建表usedata, 我们既可以编一个PERL程序实现，也可以在命令行方式下实现：

 
#mysql test 
>CREATE TABLE usedata(userno CHAR(8) NOT NULL, usepass CHAR(8) NOT NULL, lognum INT); 
>exit 
共有两个htm文件，两个pl文件. 
regist.htm <html> 
<head> 
<meta http-equiv="Content-Type" 
content="text/html; charset=gb_2312-80"> 
<meta name="GENERATOR" content="Microsoft FrontPage Express 2.0"> 
<title>注册</title> 
</head> 
<body> 
<center>用户在线注册</center> 
 
<form action="/cgi-bin/database/regist.pl" method="POST"> 
<table border="0"> 
<td valign="top">用户名<input type=text name='IDNO' size=4 maxlength=8> 
<td valign="top">密码<input type=password name='PASS' size=6 maxlength=8> 
</table> 
<input type="submit" 
value="注册"> <input type="reset" value="清除"> 
</form> 
</body> 
</html> 
login.htm <html> 
<head> 
<meta http-equiv="Content-Type" 
content="text/html; charset=gb_2312-80"> 
<meta name="GENERATOR" content="Microsoft FrontPage Express 2.0"> 
<title>登录</title> 
</head> 
<body> 
<center>用户在线登录</center> 
 
<form action="/cgi-bin/database/login.pl" method="POST"> 
<table border="0"> 
<td valign="top">用户名<input type=text name='IDNO' size=4 maxlength=8> 
<td valign="top">密码<input type=password name='PASS' size=6 maxlength=8> 
</table> 
<input type="submit" 
value="登录"> <input type="reset" value="清除"> 
</form> 
</body> 
</html> 
regist.pl #!/usr/bin/perl 
# regist.pl
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'}); 
@pairs = split(/&/, $buffer); 
foreach $pair (@pairs) { 
($name, $value) = split(/=/, $pair); 
$value =~ tr/+/ /; 
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; 
$value =~ s///g; 
$value=~ s/<([^>]|\n)*>//g; 
$value=~ s/>/>/g; 
$value=~ s/</</g; 
$FORM{$name} = $value; 
}
print "Content-type: text/html\n\n";
print "<HTML><HEAD><TITLE>注册</TITLE></HEAD>\n";
print "<body bgcolor=#FFFFFF>\n";
 
if ($FORM{'IDNO'} eq "") 
{ 
print<<EOF; 
<center>用户名不能为空! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
exit(0); 
}
if ($FORM{'PASS'} eq "") 
{ 
print<<EOF; 
<center>密码不能为空! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
exit(0); 
}
$host= shift || ""; 
$test_db="test"; 
$opt_user=$opt_password="";
use DBI;
$|= 1; # Autoflush
$table="usedata";
$dbh = DBI->connect("DBI:mysql:$test_db:$host",$opt_user,$opt_password) || die "Can't connect: $DBI::errstr\n";
$n1 = $FORM{'IDNO'};
$sth=$dbh->prepare("select * from usedata where userno=$n1 ") or die $dbh->errstr;
$sth->execute() or die $sth->errstr;
if (($row = $sth->fetchrow_arrayref)) 
{ 
$dbh->disconnect(); 
print<<EOF; 
<center>用户名已存在! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
exit(0); 
} 
$n2 = $FORM{'PASS'}; 
$n3 = 1; 
$dbh->do("insert into $table values($n1, $n2,$n3)") or die $DBI::errstr;
$dbh->disconnect(); 
print<<EOF; 
<center>您已注册成功!</center> 
</BODY></HTML> 
EOF 
exit(0);
login.pl #!/usr/bin/perl 
# login.pl
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'}); 
@pairs = split(/&/, $buffer); 
foreach $pair (@pairs) { 
($name, $value) = split(/=/, $pair); 
$value =~ tr/+/ /; 
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg; 
$value =~ s///g; 
$value=~ s/<([^>]|\n)*>//g; 
$value=~ s/>/>/g; 
$value=~ s/</</g; 
$FORM{$name} = $value; 
}
print "Content-type: text/html\n\n";
print "<HTML><HEAD><TITLE>登录</TITLE></HEAD>\n";
print "<body bgcolor=#FFFFFF>\n";
 
if ($FORM{'IDNO'} eq "") 
{ 
print<<EOF; 
<center>用户名不能为空! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
exit(0); 
}
if ($FORM{'PASS'} eq "") 
{ 
print<<EOF; 
<center>密码不能为空! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
exit(0); 
}
$host= shift || ""; 
$test_db="test"; 
$opt_user=$opt_password=""; 
use DBI; 
$|= 1; # Autoflush
$table="usedata";
$dbh = DBI->connect("DBI:mysql:$test_db:$host",$opt_user,$opt_password) || die "Can't connect: $DBI::errstr\n";
$n1 = $FORM{'IDNO'}; 
$sth=$dbh->prepare("select * from usedata where userno=$n1 ") or die $dbh->errstr;
$sth->execute() or die $sth->errstr; 
if (($row = $sth->fetchrow_arrayref)) 
{ 
if ($row->[1] eq $FORM{'PASS'}) 
{ 
$NUM = $row->[2]+1; 
$sth=$dbh->prepare("UPDATE usedata SET lognum=$NUM where userno=$n1 ") or die $dbh->errstr; 
$sth->execute() or die $sth->errstr; print<<EOF; 
<center>您第 $NUM 次登录!</center> 
EOF 
} 
else 
{ 
print<<EOF; 
<center>密码不正确! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
} 
} 
else 
{ 
print<<EOF; 
<center>用户名不正确! 
返回<a href='javascript:history.go(-1);'>前页</a>修改 
</center> 
EOF 
} 
$dbh->disconnect(); 
print <<EOF; 
</body></html> 
EOF 
exit(0);