PHP PDO不会在重复的键插入上引发异常 [英] PHP PDO does not throw exception on duplicate key insert
问题描述
我有一个奇怪的问题,即插入重复值时PDO不会引发异常.在这种情况下,我确实期望出现错误.
I have a strange problem with PDO not throwing an exception when a duplicate value is inserted. In this case I did expect an error.
相关代码:
try
{
$db_conn = new PDO("mysql:host=".$config["database"]["hostname"].";charset=utf8", $config["database"]["username"], $config["database"]["password"], []);
$db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db_conn->exec(file_get_contents("some_file_with_data.sql");
}
catch(Exception $e)
{
// PDOException extends RuntimeException extends Exception so exceptions should be catched here
// however for the duplicate key entry it will not throw an exception
}
包含SQL数据的文件包含多个插入,如下所示:
The file with SQL data contains multiple inserts like this:
INSERT INTO `a` (`b`, `c`) VALUES
(1, 1),
(2, 2),
(3, 2);
INSERT INTO `a` (`b`, `c`) VALUES
(1, 1);
表a
中的字段b
设置为主键.当我使用phpMyAdmin在完全相同的结构中插入完全相同的数据时,出现此错误:#1062 - Duplicate entry '65533' for key 'PRIMARY'
The field b
in table a
is set to being the primary key. When I insert the exact same data in the exact same structure using phpMyAdmin I get this error: #1062 - Duplicate entry '65533' for key 'PRIMARY'
在这种情况下,为什么PDO不会引发错误?甚至当我将错误模式设置为异常时?
Why does PDO not throw an error in this case? Even when I set the error mode to exception?
这是用于此特定表的表结构
This is the table structure used for this specific table
CREATE TABLE IF NOT EXISTS `a` (
`b` smallint(5) unsigned NOT NULL,
`c` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
推荐答案
This has often been reported as bug with PDO
: https://bugs.php.net/bug.php?id=61613
仅当 FIRST 语句无效时,才会引发异常.如果第一条语句运行顺利,则不会出现任何错误-并且第一条语句是有效的:
It will only throw an exception if the FIRST Statement is invalid. If the first statement runs smooth, you won't get any error - And your first statement is valid:
INSERT INTO `a` (`b`, `c`) VALUES
(1, 1),
(2, 2),
(3, 2);
作为一种解决方法-或根据您的常识正确的做法-您需要一张一行地处理行集(摘自错误报告注释):
as a workaround - or according to Your Common Sense the right way of doing it - you need to process the rowsets one-by-one (taken from the bug reports comments):
$pdo->beginTransaction();
try {
$statement = $pdo->prepare($sql);
$statement->execute();
while ($statement->nextRowset()) {/* https://bugs.php.net/bug.php?id=61613 */};
$pdo->commit();
} catch (\PDOException $e) {
$pdo->rollBack();
throw $e;
}
这篇关于PHP PDO不会在重复的键插入上引发异常的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!