MYSQL 在 PDO 不存在的地方插入 [英] MYSQL Insert Where Not Exists with PDO

问题描述

我正在使用 PHP PDO 命令有条件地将值插入 MySQL 数据库.条件是如果要插入的行已经存在，就不要插入.如果不存在，则插入该行.当我通过 PHPmyadmin 执行时，代码执行得很好，因此将 PDO 变量(:a、:b)与静态数字交换.

I am conditionally inserting values to a MySQL database using a PHP PDO command. The condition states that if the row to be inserted already exists, do not insert it. If it does not exist, insert the row. The code performs just fine when I execute through PHPmyadmin, and thus swap the PDO variables (:a, :b) with static numbers.

我收到以下错误:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name '0'' in...

PHP代码如下:

我准备sql语句...

I prepare the sql statement...

$InsertFoo = $pdo->prepare("

    INSERT INTO `MyTbl` (
                            `ColA`,
                            `ColB`
                            )
    SELECT * FROM ( SELECT :a, :b ) AS tmp
    WHERE NOT EXISTS (
        SELECT `MyTbl`.`ColA`
        FROM `MyTbl`
            WHERE `ColA` = :aa
            AND `ColB` = :bb
        )
    LIMIT 1

");

然后循环遍历一个数组来一遍又一遍地执行准备好的语句.(该数组之前是使用 SimpleXML 生成的，因此使用了 ->.如果您不熟悉 SimpleXML，只需将 $foo->fooA 视为一个可以更改的变量每个循环.)

And then loop through an array to execute the prepared statement over and over. (The array was generated earlier using SimpleXML, hence the ->. If you're unfamiliar with SimpleXML, just consider $foo->fooA as a variable that changes for each loop.)

foreach ($loopme as $foo) {

    // query to be executed for ticket insertion
    $TicketsToInsert->execute(array(
                                    a => $foo->fooA,
                                    b => $foo->fooB,
                                    aa => $foo->fooA,
                                    bb => $foo->fooB
                                    ));

}

非常感谢任何建议:

附带说明一下，有很多类似的问题，但我还没有找到任何可以在插入时完全处理此 PDO 问题的问题.

As a side note, there are many similar questions, but I have not found any that exactly deal with this PDO issue on insert.

1. PDO 一次只能执行一条语句
2. 同样的错误，似乎成为不同的来源
3. 选择中的 PDO 变量后跟 Where 声明

非常感谢！

已回答

Phil 提供了一种更简单的解决方案，即跨多行应用唯一约束，这反过来会拒绝新插入的在所述行中唯一的行.这与测试将要插入的行是否不存在相反.更多详情如下.

Phil offered the much simpler solution of applying a unique constraint across multiple rows, which would in turn reject a newly inserted row that was unique across said rows. This as opposed to testing for the non existence of the row that is about to be inserted. More details below.

推荐答案

您的查询看起来完全一团糟，尤其是在您的 EXISTS 子查询中.您正在从 tickets???

Your query looks completely messed up, especially in your EXISTS sub-query. You're selecting MyTbl.ColA from tickets???

我的建议是简单地在 MyTbl (ColA, ColB) 上添加一个唯一约束.

My advice would be to simply add a unique constraint on MyTbl (ColA, ColB).

ALTER TABLE MyTbl ADD UNIQUE (ColA, ColB);

然后，您的 INSERT 将失败，并出现 唯一的约束冲突，这可能会被 PDOException 捕获.

Then, your INSERT will fail with a unique constraint violation which can be caught in a PDOException.

$stmt = $pdo->prepare('INSERT INTO MyTbl (ColA, ColB) VALUES (?, ?)');
foreach ($loopme as $foo) {
    try {
        $stmt->execute([$foo->fooA, $foo->fooB]);
    } catch (PDOException $e) {
        $errorCode = $stmt->errorInfo()[1];
        if ($errorCode == 1586) {
            // I think 1586 is the unique constraint violation error.
            // Trial and error will confirm :)
        } else {
            throw $e;
        }
    }
}

解决您看到的错误消息...这是因为您没有区分 INSERT 表和子查询表.

To address the error message you're seeing... it's because you aren't differentiating between the INSERT table and the sub-query table.

这篇关于MYSQL 在 PDO 不存在的地方插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！