PDO不存在的MYSQL插入 [英] 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. select内的PDO变量，后跟Where语句

1. PDO can only execute one statement at a time
2. Same error, seems to be a different source
3. PDO variables inside a select followed by a Where statement

非常感谢！

已回答

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.

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