带预备语句的MySQL上的LIMIT关键字 [英] LIMIT keyword on MySQL with prepared statement

问题描述

SELECT id, content, date
FROM comment
WHERE post = ?
ORDER BY date DESC
LIMIT ?, ?

使用PDO(我正在使用具有Apache 2.2.21，PHP最高为5.3.6和MySQL 5.5.9的MAMP 2.0.5)准备好的语句，如果我使用以下方式更改查询，则该操作将无效

With PDO (I'm using MAMP 2.0.5 that has Apache 2.2.21, PHP up to 5.3.6, and MySQL 5.5.9) prepared statement this doesn't work, if I change the query with

LIMIT 0, 10

有效.

我在MySQL的错误中看到这是先前版本中的错误，但我不知道是否仍要解决.

I see in the bugs of MySQL that this was a bug in previous version but I can't understand if this is still to be fixed.

如果仍然存在问题，是否可以通过另一种方式选择行范围?

If this is still a problem, there is a way to select a range of rows in another way?

代码:

$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max)); 

推荐答案

问题出在这里:

$comments = $db->prepare($query); 
/* where $db is the PDO object */ 
$comments->execute(array($post, $min, $max));

PDOStatement :: execute()的手册页说(强调我的):

参数

input_parameters一个值数组，该数组具有与元素一样多的元素 执行的SQL语句中的绑定参数. 所有值为 视为PDO :: PARAM_STR .

input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

因此，您的参数将作为字符串插入，因此最终的SQL代码如下所示:

Thus your parameters are getting inserted as strings, so the final SQL code looks like this:

LIMIT '0', '10'

在特殊情况下，MySQL不会转换为数字，但会触发解析错误:

This is a particular case where MySQL will not cast to number but trigger a parse error:

mysql> SELECT 1 LIMIT 0, 10;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> SELECT 1 LIMIT '0', '10';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0', '10'' at line 1

文档怎么说:

What docs have to say:

LIMIT子句可用于限制行数 SELECT语句返回的值. LIMIT取一或两个数字 参数，都必须都是非负整数常量，并带有 这些例外:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• 在准备好的语句中，可以使用?指定LIMIT参数.占位符标记.

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

在存储的程序中，可以使用整数值的例程参数或局部变量来指定LIMIT参数.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

您的选择包括:

• 一个个地绑定参数，以便您可以设置类型:

• Bind parameters one by one so you can set a type:

$comments->bindParam(1, $post, PDO::PARAM_STR);
$comments->bindParam(2, $min, PDO::PARAM_INT);
$comments->bindParam(3, $min, PDO::PARAM_INT);

请勿将这些值作为参数传递

Do not pass those values as parameters:

$query = sprintf('SELECT id, content, date
    FROM comment
    WHERE post = ?
    ORDER BY date DESC
    LIMIT %d, %d', $min, $max);

禁用模拟准备(MySQL驱动程序具有一个错误/功能，会使它引用数字参数):

Disable emulated prepares (the MySQL driver has a bug/feature that will make it quote numeric arguments):

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

这篇关于带预备语句的MySQL上的LIMIT关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！