Perl DBI MySQL数据库访问示例_MySQL

#! /usr/bin/perl -wuse strict;use DBI;# 创建mysql连接sub create_conn    # returns connection handle{ my $dsn = "DBI:mysql:test:127.0.0.1:3306"; my $user = "test"; my $pass = "password"; my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError=>0, PrintError => 1})  or die "Could not connect to mysql server: $DBI::err($DBI::errstr)/n";}# 使用fetchrow_array获取记录并打印sub fetch_and_print_results  # params: stmt handle{ my $sth = shift(@_); while (my @row = $sth->fetchrow_array()) {  print join("/t", @row), "/n"; }}# 使用fetchrow_arrayref获取记录引用并打印sub fetch_and_print_results2  # params: stmt handle{ my $sth = shift(@_); while (my $rowref = $sth->fetchrow_arrayref()) {  my $delim = "";  for( my $i = 0; $i < @{$rowref}; ++$i)  {   #$rowref->{$i} = " " if !defined ($rowref->{$i}); # NULL to space   print $delim . @{$rowref}[$i];   $delim = ',';  }  print "/n"; }}# 使用fetchrow_hashref获取记录hash表引用并打印sub fetch_and_print_results3  # params: stmt handle{ my $sth = shift(@_); my $labels = $sth->{NAME}; my $cols = $sth->{NUM_OF_FIELDS};  print ">>>> field count $cols/n"; while (my $rowref = $sth->fetchrow_hashref() ) {  my $delim = "";  for( my $i = 0; $i < $cols; ++$i)  {   print $delim . $labels->[$i]. ' = '.%{$rowref}->{$labels->[$i]};   $delim = ',';  }  print "/n"; }}# 删除表中所有记录sub test_clear_table{ my $dbh = create_conn; my $rows = $dbh->do(qq/delete from member/);  # 直接执行删除语句，并返回删除记录数 print ">>>> total $rows records deleted/n"; $dbh->disconnect;}# 数据插入与查询sub test_insert_and_select{ my $dbh = create_conn();  # 执行数据插入语句 print '>>>>>>>> test_insert_and_select'."/n"; my $sql_insert = "insert into member (username, password) values ('julia', 'roberts')"; my $rows = $dbh->do($sql_insert); print "$rows row(s) inserted/n";  # 执行查询语句 print ">>>> fetch result 1/n"; my $sth = $dbh->prepare("select * from member"); $sth->execute; fetch_and_print_results $sth; $sth->finish;  print ">>>> fetch result 2/n"; $sth = $dbh->prepare("select username, password from member"); $sth->execute; fetch_and_print_results2 $sth; $sth->finish;  # 执行带参数的查询语句 print ">>>> fetch result 3/n"; $sth = $dbh->prepare("select username, password from member where username = ?"); $sth->execute('julia'); fetch_and_print_results3 $sth; $sth->finish;  $dbh->disconnect;}# 参数化insert语句执行sub test_param_insert{ my $dbh = create_conn(); my $sth = $dbh->prepare(qq{insert into member (username, password) values (?, ?)}); my $rows = $sth->execute('maria', 'louise'); print "$rows".' inserted: maria louise'; $sth->finish;  # undef处表示未设置的查询选项参数，不能省略 $dbh->do(qq/insert into member (username, password) values (?, ?)/, undef,          'george', 'cardon');  $dbh->disconnect;}# 绑定select输出sub test_select_out_param_bind { print ">>>>>>> test_select_param_bind/n"; my ($user, $pass); my $dbh = create_conn; my $sth = $dbh->prepare(qq{select username, password from member}); $sth->execute(); $sth->bind_col(1, /$user); $sth->bind_col(2, /$pass); print(">>1 == $user, $pass/n") while $sth->fetch();  $sth->finish();  $sth = $dbh->prepare(qq{select username, password from member}); $sth->execute(); $sth->bind_columns(/$user, /$pass); print(">>2 == $user, $pass/n") while $sth->fetch();  $sth->finish();  $dbh->disconnect;}# 事务调用sub test_transaction{ print ">>>>>>> test_transaction/n"; my $dbh = create_conn; $dbh->{AutoCommit} = 0; my $sth = $dbh->prepare(qq{insert into member (username, password) values (?,?)}); $sth->execute('tom', 'jerry'); $dbh->commit;  $sth->execute('tom', 'tom'); $dbh->rollback; $sth->finish;  $dbh->disconnect;}sub main { test_clear_table; test_param_insert; test_insert_and_select; test_transaction; test_select_out_param_bind;}exit( main );