mysql - PDO 准备语句 "Invalid Parameter Number"

Question

我正在使用一个一直在处理我的代码的函数，现在我正在慢慢地从 MySQLi 转向 PDO - 这个函数应该“简单地”采用 SQL 语句和变量数组并设置准备好的语句，然后执行它并返回一个带有“成功/失败”代码的数组，然后返回lastInsertId。

到目前为止，该函数一直工作得很好，考虑到我收到的错误(而且我是 PDO 新手)，我想知道问题出在哪里。

首先，函数本身非常良性(在其他地方定义的常量)

function dbConnect(){
    try {
        if(!defined('PDO::ATTR_DRIVER_NAME')){
            debugPrint('Error: PDO unavailable');
            return false;
        }

        $dsn = 'mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME . ';charset=UTF8';

        $opt = [
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ];

        $db = new PDO($dsn, DB_USER, DB_PASS, $opt);
        return $db;
    } catch (PDOException $e) {
        debugPrint($e->getMessage());
        return false;
    }
}


function dbInsert($sql, $param){
    $ret = [];
    if(!$pdo = dbConnect()){
        $ret[] = 1;
        return $ret;
    }

    if(!$stmt = $pdo->prepare($sql){
        echo $pdo->errorInfo();
        $ret[] = 1;
        return $ret;
    }

    if(!$stmt->execute($param)){
        echo $pdo->errorInfo();
        $ret[] = 1;
        return $ret;
    } else {
        $ret[] = 0;
        $ret[1] = $pdo->lastInsertId();
        return $ret;
    }

我正在编写代码，该代码只是检查电子邮件地址是否出现在表中，如果没有出现，则将其插入。我在我的应用程序中经常进行此检查，大约 50% 的情况下提供的电子邮件将是现有条目。

$sql = 'insert into customer (email) select :email from DUAL where not exists (select 1 from customer where email = :email)';
$bind = [':email' => 'user@bogus.org'];

这是迄今为止我见过的最简洁的方法，是对数据库的单个调用并使用准备好的语句。但是，我收到 PDO 错误“参数号无效”和堆栈跟踪错误。我已经打开了 MySQL 日志记录，看起来准备工作没问题...

Prepare   insert into customer (email) select ? from DUAL where not exists (select 1 from customer where email = ?)

...所以我只能认为是绑定(bind)，考虑到参数错误的数量，这是有道理的，但异常似乎来自执行:

PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number in {filename}:78
Stack trace:
    #0 {filename}:78: PDOStatement->execute(Array)
    #1 {callingFile(33)}: dbInsert('insert into cus...', Array)
    #2 {main}
        thrown in {filename} on line 78

由于我是 PDO 领域的新手，并且渴望学习，因此我想找出应该在哪里解决这个问题。

我很喜欢 PDO 的功能，我希望这不是一个需要解决的太大问题，但目前我不知道为什么会抛出这个错误。我确实想知道是否是因为我使用了“1”

注意:1 我知道我可以使电子邮件在表中唯一，但这意味着 $pdo->execute 返回非零，使我的函数返回非零(错误)(至少在我看来)这并不完全正确。

注意:2 我也知道我可以先检查表格以查看电子邮件是否存在，然后将其插入(如果不存在):但是，我不确定这是最好的做事的方式(并且由于我当前正在更新的代码就是这样做的，所以我认为将其进行 PDO 处理，并且可能对 SQL 调用本身更加聪明)。

注意:3我还将 SQL 语句更改为以下内容:

$sql = 'insert ignore into customer (email) values (:email)';
$bind = [':email' => 'user@bogus.org'];

这是“好的”，但是如果存在现有电子邮件，则收件人表中的自动递增主键会跳过(再次尝试保持一切干净、简单和逻辑)，我真的不想破解这个通过附加一个(慢)语句更改表以将 auto_increment 设置为 1

Answer 1

1) 在函数 dbInsert 中，您错过了 if 语句的最后一个括号:

if (!$stmt = $pdo->prepare($sql)) {...}

<小时/>2)如 PDO::prepare官方文档所述:

You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

例如不允许在 sql 语句中多次使用 :email，除非激活 ATTR_EMULATE_PREPARES 选项:

PDO::ATTR_EMULATE_PREPARES => TRUE

如果不这样做，您将收到与您所提供的完全相同的错误。

因此，要么应用上面的建议，要么保持模拟不变(例如 FALSE)并使用两个不同的命名参数标记(:email1 和 :电子邮件2):

$email = 'user@bogus.org';

$sql = 'insert into customer (email) select :email1 from DUAL where not exists (select 1 from customer where email = :email2)';

$bind = [
    ':email1' => $email,
    ':email2' => $email,
];

或者您可以使用问号 (?) 参数标记:

$email = 'user@bogus.org';

$sql = 'insert into customer (email) select ? from DUAL where not exists (select 1 from customer where email = ?)';

$bind = [
    1 => $email,
    2 => $email,
];

作为一个非常好的资源，我向您推荐 this教程。