基于 LAST_INSERT_ID 的条件插入 [英] Conditional insert based on LAST_INSERT_ID

问题描述

此处 使用 IF 语句，但他没有得到可操作的答案，只是建议去 此处 未使用 IF 语句.我尝试使用后一个链接编写 IF 语句和条件语句，但我被卡住了(见下文).

A nearly identical question was asked here using an IF statement, but he didn't get an actionable answer, just suggested to go here where no IF statements are being used. I've tried to write both an IF statement and a conditional statement using the latter link but I'm stuck (see below).

我希望只有在前一次插入尝试实际插入了一行(ROW_COUNT > 0)时，才能有条件地插入一行.之前的插入可能是重复的数据，所以我特意将它的 LAST_INSERT_ID 设置为 null，这样 LAST_INSERT_ID 就不会发生后续的子插入.SQL 脚本是由 C# 脚本创建的，因此 LAST_INSERT_ID 很可能没有指向您期望的位置.

I want to be able to conditionally insert a row only if the previous insert attempt actually inserted a row (ROW_COUNT > 0). The previous insert could have been duplicate data, so I'm deliberately setting it's LAST_INSERT_ID to null so no subsequent child inserts can occur with that LAST_INSERT_ID. The SQL script is created by a C# script, so it would be very possible that the LAST_INSERT_ID is not pointing to where you'd expect.

这是脚本生成代码的一个很小的例子(最终数据库中有大约 300 万行):

Here's a very small example of the script generated code (there are ~3 million rows in the final database):

SET @Vendors_Vendor_ID = (SELECT vendor_ID FROM VENDORS WHERE vendorName = 'PCA');
INSERT IGNORE INTO PCBID (PCBID, PCBDrawing, AssemblyDrawing, PONumber, Vendors_Vendor_ID) 
VALUES (11001, '10405', '41606', '091557.5', @Vendors_Vendor_ID);
SET @eventType_ID = (SELECT EVENTTYPE_ID FROM EVENTTYPES WHERE EVENTTYPE = 'Creation');
SET @USER = 'CTHOMAS';
INSERT IGNORE INTO EVENTS (PCBID, EVENTTYPE_ID, DATETIME, USER) 
VALUES (11001, @eventType_ID, '2009-06-15T13:15:27', @USER);
SET @EVENT_ID = IF(ROW_COUNT() > 0, LAST_INSERT_ID(), null);
-- THIS DOES NOT WORK
SELECT IF(@EVENT_ID != null, 
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) 
    VALUES (@EVENT_ID, 'Notes', 'WO#89574'), 
    null);

-- THIS DOESN'T WORK EITHER
INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT @EVENT_ID, 'Notes', 'WO#89574'
WHERE @EVENT_ID != null;

PCBID 表对重复数据没有问题，Events 表有一个复合唯一键，通过使用INSERT IGNORE:

The PCBID table is not a problem for duplicate data, and the Events table has a composite unique key which prevents duplicate data by using INSERT IGNORE:

CREATE  TABLE IF NOT EXISTS `uniqueTest`.`events` (
`Event_ID` INT(11) NOT NULL AUTO_INCREMENT ,
`PCBID` INT(11) NOT NULL ,
`EventType_ID` INT(11) NOT NULL ,
`DateTime` DATETIME NOT NULL ,
`User` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`Event_ID`) ,
UNIQUE KEY `PDU_Index` (`PCBID`, `DateTime`, `User`),

问题:我需要能够根据之前对 Events 表的插入尝试进行条件插入，如果它被忽略(因为它是重复数据)，也不要插入任何子行.目前无法使任何 EventDetail 数据唯一，基于给定的 Event_ID 可能有多行合法数据.

The Problem: I need to be able to do a conditional insert based on the previous insert attempt into the Events table, if it was ignored (because it's duplicate data), don't insert any child rows either. There's currently no way to make any of the EventDetail data unique, there could be multiple rows of legitimate data based on a given Event_ID.

根据它是什么类型的数据，事件表下面可能有四个更深的级别，如果事件数据因为它是重复数据而没有被插入，那么也不会写入任何子数据(因为它会被复制为嗯).

There are four levels deeper possible below the Events table depending on what type of data it is, if the event data doesn't get inserted because it's duplicate data, no child data gets written either (because it'll be duplicate as well).

推荐答案

您的第二次尝试几乎是正确的.您必须使用 IS NOT NULL 检查 NULL 值.所以用

Your second try was nearly right. You've got to check of NULL values with IS NOT NULL. So use

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT @EVENT_ID, 'Notes', 'WO#89574' FROM DUAL
WHERE @EVENT_ID IS NOT NULL;  -- instead of != 

或

INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE)
SELECT t.* FROM (
    SELECT @EVENT_ID, 'Notes', 'WO#89574'
) t
WHERE @EVENT_ID IS NOT NULL;  -- instead of != 

第一个不能工作:

-- THIS DOES NOT WORK
SELECT IF(@EVENT_ID != null, 
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) ...

因为IF 是

IF(expr1,expr2,expr3)

IF(expr1,expr2,expr3)

如果 expr1 为 TRUE(expr1 <> 0 和 expr1 <> NULL)，则 IF() 返回 expr2；否则返回 expr3.IF() 返回数字或字符串值，具体取决于使用它的上下文.

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

条件执行语句只能在 stored例程.存储例程的 IF 语法 将允许类似>

Conditional execution of statements is only possible in stored routines. The IF syntax of stored routines would allow something like

IF @EVENT_ID IS NOT NULL THEN
    INSERT INTO EVENTDETAILS (EVENT_ID, ITEMNAME, ITEMVALUE) ...
END IF

你必须区分这两种语法版本.

You've got to distinguish those both syntax versions.

这篇关于基于 LAST_INSERT_ID 的条件插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！