php - "Integrity constraint violation: 1062 Duplicate entry"- 但没有重复的行

Question

我正在将一个应用程序从 native mysqli 调用转换为 PDO。尝试向具有外键约束的表中插入行时遇到错误。

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

信息 PHP 5.3、MySQL 5.4

首先，这是表格:

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;

或者如果你愿意，

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)

接下来是查询:

    $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();
    }   

这会导致以下 PDO 错误:

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

同样的查询在 mysqli 处理时工作正常。为什么在未找到任何重复项时 PDO 拒绝带有“重复项”消息的 INSERT？

Answer 1

由于并非所有代码都可用(从 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();

}