PDO绑定参数的神奇bug

在使用pdo查询数据库的时候遇到的神奇bug(绑定参数查询结果不准确):

$dbh = new \PDO('mysql:host=localhost;dbname=user', 'root', 'root');
$sth = $dbh->prepare('select `user`.*, `content`.`cid` as `cid`, `fans`.`fid` as `fid` from `user` left join `content` on `content`.`uid` = `user`.`uid` left join `fans` on `fans`.`to_uid` = `content`.`uid` and `content`.`uid` = ? where (`delete_time` is null) order by `cid` desc limit 10 offset 0');
$sth->execute(['user.uid']);
$result = $sth->fetchAll();
echo '<pre>';
var_dump($result);
die;

结果异常。

改为完整语句:

$dbh = new \PDO('mysql:host=localhost;dbname=user', 'root', 'root');
$sth = $dbh->prepare('select `user`.*, `content`.`cid` as `cid`, `fans`.`fid` as `fid` from `user` left join `content` on `content`.`uid` = `user`.`uid` left join `fans` on `fans`.`to_uid` = `content`.`uid` and `content`.`uid` = user.uid where (`delete_time` is null) order by `cid` desc limit 10 offset 0');
$sth->execute();
$result = $sth->fetchAll();
echo '<pre>';
var_dump($result);
die;

正常。

经过排查非框架bug

在遇到类似的问题时应取消where的方式

如:(使用where是绑定参数方式)

DB::table('users')
  ->join('contacts', function ($join) {
   $join->on('users.id', '=', 'contacts.user_id')
     ->where('contacts.user_id', '>', 5);
  })
  ->get();

应改为:(完整sql方式)

DB::table('users')
 ->join('contacts', function ($join) {
  $join->on('users.id', '=','contacts.user_id')->orOn(...);
  })
 ->get();

总结,匿名函数使用 on()->on() 连贯操作生成的是完整的语句!

发表评论

您的电子邮箱地址不会被公开。