SQLSTATE [23000]:完整性约束违反错误处理 [英] SQLSTATE[23000]: Integrity constraint violation error handling

问题描述

我知道错误的含义以及发生的原因，这意味着这不是涉及State 23000的许多主题的重复项.我正在使用PDO将重复记录插入具有复合PK的表中.我故意这样做是为了自动取消在此特定表中插入任何重复的记录.

I know what the error means, and why it's occurring, meaning this is not a duplicate of the many topics covering State 23000. I'm using PDO to insert a duplicate record into a table that has a compound PK. I did this on purpose, in order to automatically cancel the insertion of any duplicated records in to this specific table.

错误SQLSTATE[23000]: Integrity constraint violation正在终止脚本.有没有一种方法可以设置此特定错误的警告级别，从字面上告诉它关闭并继续进行操作?

The error, SQLSTATE[23000]: Integrity constraint violation, is killing the script. Is there a way to set the warn level for this particular error, to literally tell it to shut up and carry on?

如果我们不能让复合PK为我们工作，我不明白拥有复合PK的目的吗?

I don't understand the purpose of having compound PKs if we can't allow them to work for us?

我对数据库管理的经验不是很丰富，所以如果我遇到一点如果我用锤子敲打它应该会起作用"的信息，您必须原谅我.

I'm not very experienced with DB administration so you'll have to forgive me if I come across a bit 'it should work if I hit it with a hammer'.

表结构:

CREATE TABLE `cart` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userid` int(10) NOT NULL,
 `itemid` int(10) NOT NULL,
 UNIQUE KEY `id` (`id`),
 UNIQUE KEY `unique_records` (`userid`,`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

请注意称为唯一记录"的复合PK.

Note the compound PK called 'unique records'.

所以我有这张桌子:

| id | userid | itemid |
|  1 |    175 |     12 |

然后执行此查询:

    $insertItem = $db->query("
                              INSERT INTO cart (userid, itemid) 
                              VALUES (:userid, :itemid), 
                              array("userid"=>"175", "itemid"=>"12")
                            ");

执行此查询会提示以下错误:SQLSTATE[23000]: Integrity constraint violation.

Executing this query prompts this error: SQLSTATE[23000]: Integrity constraint violation.

我完全理解为什么会收到此错误-我只是不明白为什么它会杀死我的提交?我认为它应该继续工作，而忽略一些记录因为重复而没有插入的事实.

I fully understand why I'm getting this error - I just don't understand why it's killing my submission? I thought it should just keep working ignoring the fact that a few records weren't inserted because they were duplicates?

建议感激！

推荐答案

如果希望insert继续工作，可以尝试使用INSERT IGNORE:

If you want the insert to keep working, you can try using INSERT IGNORE:

INSERT IGNORE INTO cart(userid, itemid) 
    VALUES (:userid, :itemid);

INSERT IGNORE可能有点强，因为它会忽略所有错误.如果您只想忽略重复的键错误，请使用on duplicate key并为其设置一个值:

INSERT IGNORE can be a bit strong because it ignores all errors. If you just want to ignore duplicate key errors, use on duplicate key and set a value to itself:

INSERT IGNORE INTO cart(userid, itemid) 
    VALUES (:userid, :itemid)
    ON DUPLICATE KEY userid = values(userid);

set部分不执行任何操作-值保持不变-但其作用是忽略该错误.

The set part does nothing -- the value doesn't change -- but the effect is to ignore the error.

这篇关于SQLSTATE [23000]:完整性约束违反错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！