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

问题描述

SELECT id、内容、日期来自评论在哪里发布 = ?按日期订购 DESC限制 ?， ?

使用 PDO(我使用的是具有 Apache 2.2.21、PHP 最高 5.3.6 和 MySQL 5.5.9 的 MAMP 2.0.5)准备好的语句这不起作用，如果我用

LIMIT 0, 10

它有效.

我在 MySQL 的错误中看到这是以前版本中的错误，但我不明白这是否仍然需要修复.

如果这仍然是一个问题，有没有办法以另一种方式选择一系列行?

代码:

$comments = $db->prepare($query);/* 其中 $db 是 PDO 对象 */$comments->execute(array($post, $min, $max));

解决方案

问题来了:

$comments = $db->prepare($query);/* 其中 $db 是 PDO 对象 */$comments->execute(array($post, $min, $max));

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

<块引用>

参数

input_parameters 具有与元素数量相同的值的数组正在执行的 SQL 语句中的绑定参数.所有值都是被视为 PDO::PARAM_STR.

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

LIMIT '0', '10'

这是一种特殊情况，MySQL 不会强制转换为数字，但会触发解析错误:

mysql>选择 1 限制 0, 10;+---+|1 |+---+|1 |+---+1 行(0.00 秒)mysql>选择 1 限制 '0', '10';ERROR 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的0"、10"附近使用的正确语法

文档有什么要说的:><块引用>

LIMIT 子句可用于限制行数由 SELECT 语句返回.LIMIT 需要一两个数字参数，它们都必须是非负整数常量，具有这些例外:

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

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

您的选择包括:

• 一一绑定参数，设置类型:

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

• 不要将这些值作为参数传递:

  $query = sprintf('SELECT id, content, date来自评论在哪里发布 = ?按日期订购 DESC限制 %d, %d', $min, $max);

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

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

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

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

it works.

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?

code:

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

解决方案

Here's the problem:

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

The manual page for PDOStatement::execute() says (emphasis mine):

Parameters

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.

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

LIMIT '0', '10'

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:

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:

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

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

Your choices include:

• 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);
  

• 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屋！