“完整性约束违规：1062重复条目” - 但没有重复的行 [英] "Integrity constraint violation: 1062 Duplicate entry" - but no duplicate rows

问题描述

我将本地mysqli调用的应用程序转换为PDO。尝试将行插入具有外键约束的表时遇到错误。

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

信息PHP 5.3，MySQL 5.4

首先，这里是表格：
$ b $ pre $ codeREATE 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;

插入`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 `）参考`z_one`（`customer_id`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8;

或者如果您愿意的话，

 的MySQL>描述z_one; 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 |字段|类型|空|密钥|默认|额外| 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 | customer_id | int（10）unsigned | NO | PRI | 0 | | 
 | name_last | varchar（255）|是| | NULL | | 
 | name_first | varchar（255）|是| | NULL | | 
 | dateadded | datetime |是| | NULL | | 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 4行（0.00秒）
 
 
 mysql>描述z_many; 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 |字段|类型|空|密钥|默认|额外| 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 | order_id | varchar（15）| NO | PRI | | | 
 | customer_id | int（10）unsigned |是| MUL | NULL | | 
 | dateadded | datetime |是| | NULL | | 
 + ------------- + ------------------ + ------ + ----- + --------- + ------- + 
 3行（0.00秒）
  

下面是查询：

  $ order_id ='22BD24'; 
 $ customer_id = 1; 
 
尝试
 {
 $ q =
 INSERT INTO 
 z_many 
（
 order_id，
 customer_id， 
加了
）
 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）{
 //仅测试用例。不要将sql错误回显给最终用户。 
 echo $ err-> getMessage（）; 
 
 
 
 $ b $ p 
 $ p $这会导致下面的PDO错误：
 
 
 
 
 SQLSTATE [23000]：完整性约束违规：1062重复项'
'22BD24'键'PRIMARY'
 
 
当由 mysqli 处理时，相同的查询可以正常工作。为什么PDO在没有找到任何重复项的情况下拒绝带有重复项消息的INSERT？ 
 
解决方案
代码是可用的（从PHP方面），以防万一你的查询是在某种形式的循环中最快（也许是部分）的解决方案如下：
 
 
  $ order_id ='22BD24'; 
 $ customer_id = 1; 
尝试
 {
 $ 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）{
 //仅限测试用例。不要将sql错误回显给最终用户。 
 echo $ err-> getMessage（）; 
}; 
  
 
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.   


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

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: 

  
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
  '22BD24' for key '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?
解决方案
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:
$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();
}";


                        
这篇关于“完整性约束违规：1062重复条目” - 但没有重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！