本周我第一次与perl DBI合作 .
大多数查询/插入工作正常,但是我遇到了一个返回0行的特定查询的问题 . 当我为perl DBI启用跟踪,并将跟踪中完全相同的语句复制到服务器(通过HeidiSQL)时,返回1行 .
原始SQL查询中是否存在歧义?目的是检索具有最新时间戳的行 . 时间戳列中没有重复项 .
数据库连接的初始设置:
$dsn = 'dbi:mysql:<servername>:<port>';
$dbh = DBI->connect($dsn, "<username>","<password>") or die "unable to connect $DBI::errstr\n";
准备和执行语句:代码到达print'no rows found'
my $sth = $dbh->prepare("SELECT name, location, timestamp, notified FROM storage
WHERE name = ? AND location = ?
AND timestamp = (SELECT MAX(timestamp) FROM storage)");
$sth->execute($strg_data->{name}, $strg_data->{location});
my @latest = $sth->fetchrow_array();
if (@latest) {
<snipped>
}
else {
print "no rows found!\n";
}
从perl DBI跟踪中提取(级别设置为2):
-> prepare for DBD::mysql::db (DBI::db=HASH(0xebe4c0)~0xec0010 'SELECT name, location, timestamp, notified FROM storage
WHERE name = ? AND location= ? AND timestamp = (SELECT MAX(timestamp) FROM storage)')
Setting mysql_use_result to 0
<- prepare= DBI::st=HASH(0xecd7d0) at monitor.pl line 147
-> execute for DBD::mysql::st (DBI::st=HASH(0xecd7d0)~0xec9e50 'xxxx' '/tmp/')
-> dbd_st_execute for 00ecd7a0
-> mysql_st_interal_execute
Binding parameters: SELECT name, location, timestamp, notified FROM storage
WHERE name = 'xxxx' AND location= '/tmp/' AND timestamp = (SELECT MAX(timestamp) FROM storage)
<- mysql_st_internal_execute returning rows 0
<- dbd_st_execute returning imp_sth->row_num 0
<- execute= '0E0' at monitor.pl line 152
1 回答
SELECT MAX(timestamp) FROM storage
找到最大时间戳,而不考虑名称和位置 . 如果您指定的名称和位置为't have a record with that timestamp, you' ll获得0行 .你可能想要这个查询: