javascript - MySQL 在其他表中使用表中最后一个自动递增的 ID

Question

我目前有以下声明。

INSERT INTO TripsTaken (ProfileId, DestinationLocation, Name, ImageUrl, StartDate, EndDate, Summary, Latitude, Longitude) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?);
INSERT INTO Transportation(Type, Location, StartDate, EndDate) VALUES (?, ?, ?, ?);
INSERT INTO TripsTakenTransportation (TripId, id) VALUES( (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) , LAST_INSERT_ID());
INSERT INTO Transportation(Type, Location, StartDate, EndDate) VALUES (?, ?, ?, ?);
INSERT INTO TripsTakenTransportation (TripId, id) VALUES( (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) , LAST_INSERT_ID());

我收到以下错误:

{ [Error: ER_NO_REFERENCED_ROW_: Cannot add or update a child row: a foreign key constraint fails (tripsdatabase.tripstakentransportation, CONSTRAINT tripstakentransportation_ibfk_1 FOREIGN KEY (TripId) REFERENCES TripsTaken (TripId))] code: 'ER_NO_REFERENCED_ROW_', errno: 1452, sqlState: '23000', index: 2 }

只要语句 INSERT INTO TripsTakenTransportation... 出现两次，就会发生错误。我怀疑我的子查询 (SELECT MAX(LAST_INSERT_ID()) FROM TripsTaken) 第二次没有达到我的预期。

如何从表中获取 LAST_INSERT_ID()、存储它并在其他查询中重用它？

注意:

交通还有一个自动增量列。

Answer 1

您赋予了 LAST_INSERT_ID() 比它更多的智能。您不能说 MAX(LAST_INSERT_ID()) FROM table 因为它不与表绑定(bind)。

这是您需要的东西:

INSERT INTO TripsTaken 
       (ProfileId, DestinationLocation, Name, 
        ImageUrl, StartDate, EndDate, Summary, Latitude, Longitude) 
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?);

SET @tripsID := LAST_INSERT_ID();

INSERT INTO Transportation
       (Type, Location, StartDate, EndDate) 
VALUES (?, ?, ?, ?);

INSERT INTO TripsTakenTransportation 
       (TripId, id)
VALUES (@tripsID , LAST_INSERT_ID());

LAST_INSERT_ID() 返回最近增加的自动增量列值的值。好消息是，每个不同的 MySQL 连接都有一个 LAST_INSERT_ID() 值，因此您可以信赖它在连接内的 SQL 语句之间都是正确的。坏消息是您必须非常小心，不要编辑 SQL 代码来添加新的 INSERT 语句，因为它们会产生更改 LAST_INSERT_ID() 的副作用。小心破解。