参数化 PDO 查询和 `LIMIT` 子句 - 不工作

问题描述

我有这样的查询:

SELECT imageurl 
FROM entries 
WHERE thumbdl IS NULL 
LIMIT 10;

它与 PDO 和 MySQL Workbench 完美配合(它根据我的需要返回 10 个网址).

It works perfectly with PDO and MySQL Workbench (it returns 10 urls as I want).

但是我尝试使用 PDO 参数化 LIMIT:

However I tried to parametrize LIMIT with PDO:

$cnt = 10;
$query = $this->link->prepare("
             SELECT imageurl 
             FROM entries 
             WHERE imgdl is null 
             LIMIT ?
         ");

$query->bindValue(1, $cnt);

$query->execute();

$result = $query->fetchAll(PDO::FETCH_ASSOC);

返回空数组.

推荐答案

我刚刚测试了一堆案例.我在 OS X 上使用 PHP 5.3.15，并查询 MySQL 5.6.12.

I just tested a bunch of cases. I'm using PHP 5.3.15 on OS X, and querying MySQL 5.6.12.

如果您设置了任何组合:

Any combination works if you set:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

以下所有工作:您可以使用 int 或字符串；你不需要使用 PDO::PARAM_INT.

All of the following work: you can use either an int or a string; you don't need to use PDO::PARAM_INT.

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$int = intval(1);
$int = '1';

$stmt->bindValue(1, 1);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1');
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, 1, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindValue(1, '1', PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $string, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

您也可以忘记 bindValue() 或 bindParam()，而是将数组参数中的 int 或字符串传递给 execute().这工作正常并且做同样的事情，但使用数组更简单，通常更方便编码.

You can also forget about bindValue() or bindParam(), and instead pass either an int or a string in an array argument to execute(). This works fine and does the same thing, but using an array is simpler and often more convenient to code.

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->execute(array($int));
print_r($stmt->fetchAll());

$stmt->execute(array($string));
print_r($stmt->fetchAll());

如果您启用模拟准备，则只有一种组合有效:您必须使用整数作为参数并且您必须指定 PDO::PARAM_INT:

If you enable emulated prepares, only one combination works: you must use an integer as the parameter and you must specify PDO::PARAM_INT:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

$stmt = $dbh->prepare("select user from mysql.user limit ?");

$stmt->bindValue(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

$stmt->bindParam(1, $int, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll());

如果您启用了模拟准备，则无法将值传递给 execute().

Passing values to execute() doesn't work if you have emulated prepares enabled.

这篇关于参数化 PDO 查询和 `LIMIT` 子句 - 不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！