問題描述

我有這樣的查詢:

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模板網！