2013-03-11 17:21:31 
阿炯
-------------------------------How to how many rows a Perl DBI query returns?--取得某次查询影响了多少行
The DBD::mysql driver has a the rows() method that can return the count of the results:
$sth = $dbh->prepare( ... );
$sth->execute;
$rows = $sth->rows;
This is database-driver specific, so it might not work in other drivers, or it might work differently in other drivers.
my $th = $dbh->prepare(qq{SELECT count(*) FROM bounce_info WHERE ...});
$th->execute();
if ($th->fetch()->[0]) {
....
}
或者
my ($got_id) = $dbh->selectrow_array("SELECT count(*) from FROM bounce_info ...");
---
since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.
Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:
SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();
The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.
Although this solution also requires two queries it’s much more faster, as you execute the main query only once.
-------------------------------
查询结果行的操作
There are many different ways you can retrieve data from a statement handle. The most common are quite simple and their use is shown below:
my @row_array = $sth->fetchrow_array;
my $array_ref = $sth->fetchrow_arrayref;
my $hash_ref = $sth->fetchrow_hashref;
The first, fetchrow_array, will return each row in turn as an array. An example using data returned from the select above could be:
while (my @row_array = $sth->fetchrow_array) {
print $row_array[0], " is ", $row_array[1], " years old, and has a " ,
$row_array[2], "\n";
}
The second example is similar but returns an array reference rather than an array:
while (my $array_ref = $sth->fetchrow_arrayref) {
print $array_ref->[0], " is ", $array_ref->[1], " years old, and has a " , $array_ref->[2], "\n";
}
The third example, fetchrow_hashref, is often the most readable:
while (my $hash_ref = $sth->fetchrow_hashref) {
print $hash_ref->{name}, " is ", $hash_ref->{age},
" years old, and has a " , $hash_ref->{pet}, "\n";
}
The fetchrow_ methods literally just fetch one row at a time.
If you want all the rows of some columns, you can inefficiently push around data structures, or use a call meant for your situation.It seems to me you want to use selectcol_arrayref as in:
my $ary_ref = $dbh->selectcol_arrayref(
"select id, name from table",{ Columns=>[1,2] }
);
The column indexes refer to the position of the columns in the result set, not the original table.
my $row = $sth->fetchrow_hashref;
It should be:
my @rows;
while ( my $row = $sth->fetchrow_hashref ) {
push @rows, $row;
}
return @rows;
'@rows'的数据结构其实是一个array of hash。
-------------------------------
perl mysql socket connection(使用socket来建立连接)
my $dbh = DBI->connect("DBI:mysql:database=dbname;host=localhost;mysql_socket=/path/to/mysql.sock","someuser","somepassword", {'RaiseError' => 1});
-------------------------------
How can I fetch the last row I inserted using DBI(取得最后插入记录的id号)
From DBD::mysql documentation:
An alternative way for accessing this attribute is via $dbh->{'mysql_insertid'}.
This is a property of the statement handle. You should be able to access the ID like that:
$sth->{mysql_insertid}
-------------------------------
Perl DBI取Mysql空结果集的处理
Using Perl and MySql check for an empty result
my $sth = $dbh->prepare($stmt);
$sth->execute();
my $row = $sth->fetchrow_hashref;
print "List of IDs:\n" if $row;
while ($row) {
print "$row->{fid}\n";
$row = $sth->fetchrow_hashref;
}
如果在脚本中进入了DBI及DBD::mysql模块，可以调用->rows方法，可以很方便地取得上述查询的记录数量：
$sth->rows;
unless($sth->rows){
#empty set
}
$sth=$dbh->prepare("SELECT * FROM freeoa");
$sth->execute();
unless($sth->fetch()){ do sth..; }
while(...){
last unless($sth->rows);
}
-------------------------------
DBI disconnect invalidates 1 active statement handle
DBI::db=HASH(0x3a106b0)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at freeoa_get_mysql_stat_proces.pl line 70.
在调用'$dbh->disconnnect();'前应先调用'$sth->finish();'来完成语句。
Normally you don't need to call finish, unless you're not getting all the rows. If you get all the results in a loop using fetchrow_array, you don't call finish at the end unless you aborted the loop.
通常的讲，不需要直接调用'$sth->finish();'，除非是在循环中有未取完的数据。
-------------------------------
-------------------------------
-------------------------------
-------------------------------