使用PDO的MySQL更新和准备好的语句不起作用 [英] MySQL update using PDO and prepared statement not working

问题描述

我在使用php PDO和mysql时遇到了一个奇怪的问题.

I'm having a strange problem with php PDO and mysql.

我有下表:

create table test_table ( id integer, value text );

单行:

insert into test_table values (1, "asdf");

当我尝试使用准备好的语句更新这一行时，根据使用的语法，我会得到不同的行为:

when I try to update this single row with a prepared statement, I got different behaviours depending on the syntax I use:

// connection to db (common code)
$dbh = new PDO("mysql:host=localhost;dbname=test", "myuser", "mypass");

================================================ ==========

=========================================================

// WORKING
$q = 'update test_table set id=1, value='.rand(0,99999).' where id=1';
$dbh->exec($q);

================================================ ==========

=========================================================

// WORKING
$q = 'update test_table set value=:value where id=:id';
$par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
$sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute($par);

================================================ ==========

=========================================================

// NOT WORKING
$q = 'update test_table set id=:id, value=:value where id=:id';
$par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
$sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute($par);

在第三种情况下，在我的服务器上，没有任何原因也没有异常/错误，没有在该行上执行更新.在另一台服务器上它可以工作.我不是在寻找像这样的答案:等等?使用第一个或第二个实现":)

In the third case, on my server, the update is not performed on the row, without any reason nor exception/error. On another server it works. I' not looking for answers like: "and so? use the first or second implementation" :)

我在问为什么第三个实现不起作用，因为我将大量代码从服务器迁移到另一个(这不是我的代码)，并且其中包含很多查询像这样，我没有时间一一修复它们.在当前服务器上它可以工作，而在新服务器上则不能.

I'm asking why the third implementation doesn't work because I'm migrating a lot of code from a server to another one (it's not my code) and it contains a lot of queries like this one and I have no time to fix them one by one. On the current server it works and on the new one it doesn't.

为什么第三个实现无效? php/pdo/mysql是否有任何可能影响此行为的配置?

Why the third implementation doesn't work? Is there any kind of configuration for php/pdo/mysql which could affect this behaviour?

谢谢.

更新: 试图消除错误消息:

Update: Tried to sqeeze out error messages:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

try {
// NOT WORKING
  $q = 'update test_table set id=:id, value=:value where id=:id';
  $par = array(
    "id" => 1,
    "value" => rand(0,99999)
  );
  $sth = $dbh->prepare($q, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  print_r($sth);
  print_r($dbh->errorInfo());
} catch(PDOException $e) {
  echo $e->getMessage();
}

$sth->execute($par);

在两个服务器上都运行此代码(工作和不工作):

Executing this code on both servers (working and not working):

PDOStatement Object
(
    [queryString] => update test_table set id=:id, value=:value where id=:id
)
Array
(
    [0] => 00000
    [1] => 
    [2] => 
)

更新2

看看这个进一步的测试:

Look at this further test:

create table test_table ( value0 text, value text );
insert into test_table values ("1", "pippo");

// NOT WORKING

$q = 'update test_table set value0=:value0, value=:value where value0=:value0';
$par = array(
    "value0" => "1",
    "value" => rand(0, 839273)
);

create table test_table ( value0 text, value text );
insert into test_table values ("pippo", "1");

// WORKING

$q = 'update test_table set value=:value, value0=:value0 where value=:value';
$par = array(
    "value" => "1",
    "value0" => rand(0, 839273)
);

不可思议，不是吗?我现在的怀疑是，存在一些针对PDO +占位符处理的每个表的第一列的特殊更新行为.

Incredible, isn't it? My suspect now is that exists some special update beahaviour specifically made for the first column of every table on PDO+placeholder handling.

推荐答案

http://php.net/manual/en/pdo.prepare.php 指出:

您必须为每个要包含的值包括一个唯一的参数标记 调用PDOStatement :: execute()时传递给该语句. 您 不能多次使用相同名称的命名参数标记 除非打开了仿真模式，否则准备好的语句.

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

这表明，代码在一个服务器上而不是在另一台服务器上运行的可能原因是，在代码失败的服务器上禁用了PDO::ATTR_EMULATE_PREPARES.如文档所述，此属性有效地消除了限制，使您无法两次使用相同名称的参数标记(以及其他一些限制).

As this indicates, the likely reason behind your code working on one server and not another is that PDO::ATTR_EMULATE_PREPARES is disabled on the server which the code fails on. As the documentation says, this attribute effectively removes the restriction preventing you from using a parameter marker of the same name twice (along with some other restrictions).

这篇关于使用PDO的MySQL更新和准备好的语句不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！