“完整性约束违反:1062重复项”——但没有重复行。

I'm converting an app from native mysqli calls to PDO. Running into an error when attempting to insert a row into a table with a foreign key constraint.

我正在将一个来自mysqli的应用程序转换为PDO。当试图将一行插入到带有外键约束的表中时，会出现错误。

Note: this is a simplified test case and should not be copy/pasted into a production environment.

注意:这是一个简化的测试用例，不应该复制/粘贴到生产环境中。

Info PHP 5.3, MySQL 5.4

Info PHP 5.3, MySQL 5.4

First, here are the tables:

首先，这里有一些表格:

CREATE TABLE `z_one` (
  `customer_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name_last` varchar(255) DEFAULT NULL,
  `name_first` varchar(255) DEFAULT NULL,
  `dateadded` datetime DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `z_one` VALUES (1,'Khan','Ghengis','2014-12-17 10:43:01');

CREATE TABLE `z_many` (
  `order_id` varchar(15) NOT NULL DEFAULT '',
  `customer_id` int(10) unsigned DEFAULT NULL,
  `dateadded` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `order_index` (`customer_id`,`order_id`),
  CONSTRAINT `z_many_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `z_one` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Or if you prefer,

如果你喜欢的话,

mysql> describe z_one;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| customer_id | int(10) unsigned | NO   | PRI | 0       |       |
| name_last   | varchar(255)     | YES  |     | NULL    |       |
| name_first  | varchar(255)     | YES  |     | NULL    |       |
| dateadded   | datetime         | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


mysql> describe z_many;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| order_id    | varchar(15)      | NO   | PRI |         |       |
| customer_id | int(10) unsigned | YES  | MUL | NULL    |       |
| dateadded   | datetime         | YES  |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Next, here is the query:

接下来是查询:

    $order_id = '22BD24';
    $customer_id = 1;

    try 
    {
        $q = "
            INSERT INTO 
                z_many 
                (
                    order_id,
                    customer_id,
                    dateadded
                )
            VALUES 
                (
                    :order_id,
                    :customer_id,
                    NOW()
                )
        ";
        $stmt = $dbx_pdo->prepare($q);
        $stmt->bindValue(':order_id', $order_id, PDO::PARAM_STR);
        $stmt->bindValue(':customer_id', $customer_id, PDO::PARAM_INT);
        $stmt->execute();

    } catch(PDOException $err) {
        // test case only.  do not echo sql errors to end users.
        echo $err->getMessage();
    }

This results in the following PDO error:

这导致了以下PDO错误:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '22BD24' for key 'PRIMARY'

SQLSTATE[23000]:完整性约束违例:1062重复条目'22BD24'表示键'PRIMARY'

The same query works fine when handled by mysqli. Why is PDO rejecting the INSERT with a 'duplicate entry' message when there aren't any duplicates found?

mysqli处理相同的查询时可以正常工作。为什么PDO拒绝带有“重复条目”消息的插入，而没有找到任何重复条目?

4 个解决方案

#1

Since not all code is available (from php side) just in case your query is in some sort of loop the quickest (and perhaps partly) solution to this is the following:

因为并不是所有的代码都是可用的(从php方面)，如果您的查询处于某种循环中，那么最快(也许是部分)的解决方案如下:

$order_id = '22BD24';
$customer_id = 1;

try {
    $q = "INSERT INTO `z_many` (`order_id`,`customer_id`,`dateadded`)
        VALUES (:order_id,:customer_id,NOW())
        ON DUPLICATE KEY UPDATE `dateadded`=NOW()";

    $stmt = $dbx_pdo->prepare($q);
    $stmt->bindValue(':order_id', $order_id, PDO::PARAM_STR);
    $stmt->bindValue(':customer_id', $customer_id, PDO::PARAM_INT);
    $stmt->execute();

} catch(PDOException $err) {

    // test case only.  do not echo sql errors to end users.
    echo $err->getMessage();

}

#2

I've copied the SQL schema you provided on my mysql DB and added script code, but with PDO initialization at the start: $dbx_pdo = new PDO('mysql:host=127.0.0.1;dbname=test12;charset=utf8','root','');

我已经复制了您在我的mysql DB上提供的SQL模式并添加了脚本代码，但是在开始时使用PDO初始化:$dbx_pdo = new PDO('mysql:host=127.0.0.1;dbname=test12;charset=utf8'，'root'， ");

.. and it worked fine, but my setup is php 5.5.9 and mysql 5.6.16

. .它运行得很好，但是我的设置是php 5.5.9和mysql 5.6.16

So I think your code executes twice and maybe its inside of a transaction, so you get rollback. Need to know more context

我认为你的代码执行了两次可能是在事务内部，所以你会得到回滚。需要了解更多的背景知识

#3

Please REMOVE default for primary key column. Also use the construction

请删除主键列的默认值。也使用建设

INSERT INTO (`field1`, `field2`, `...`) values (val1, val2, val3);

If you tell what default value is for inserts - some mysql versions can see errors when inserting. Thats why you should use auto-increment or dont use default value at all.

如果您告诉插入的默认值是什么，一些mysql版本可以在插入时看到错误。这就是为什么你应该使用自动增值或不使用默认值的原因。

#4

Just a shot in the dark. I use PDO with ORACLE PL/SQL only with bindParam(). And have a look at the forth parameter ,15 if it is a PARAM_STR value. So try this, hope it helps.

只是瞎猜。我只对bindParam()的ORACLE PL/SQL使用PDO。看看第四个参数，如果它是一个PARAM_STR值的话是15。所以试试这个，希望它能有所帮助。

   $stmt->bindParam(':order_id', $order_id, PDO::PARAM_STR, 15);
   $stmt->bindParam(':customer_id', $customer_id, PDO::PARAM_INT);

This 15 descibes the (bufffer-)length from order_id in your table.

这15将从表中的order_id中确定(bufffer-)长度。

#1