自动更新具有自动增量外键的MySQL关联表

Question

我有一个应用程序，其中一个特定事件用INSERT将我的数据库更新为两个表A和B。还有一个附加的关联/链接表AB与外键id_A、id_B到A和B映射它们的多到多关系。我的问题是，当应用程序逻辑不知道其中一个外键时，如何最好地更新AB (在本例中是AUTO_INCREMENT ID)。

其他详情：

• 只有id_B设置为AUTO_INCREMENT，因此在插入之前不知道它的值。
• B使用多行INSERT进行更新；A是单行INSERT。
• id_B是一个唯一的键，但也有可能是由ON DUPLICATE KEY UPDATE处理的，它用新的值替换旧的值。
• 响应于同一事件，A和B在同一个事务中被更新。
• MySQL 5.7使用InnoDB。

我的想法是将所有insert语句包含在存储过程中，其中最终的INSERT到AB将从LAST_INSERT_ID()获取id_B的值，但显然这将只返回多行插入：last-insert-id中第一行的值。

我想，如果将更新AB的逻辑分离为触发器或与更新父表所用的语句不同的语句，这将是一个额外的好处。

编辑:对评论的回应。

Answer 1

在用于将数据插入A和B的语句之前，请放置以下语句.

SET @before = ( SELECT MAX( id_B ) AS maxIDB
                FROM B );

此时，这将在B中找到最大的B值。所有后续插入到B中的值都将大于此。我们可以利用这一事实对我们有利。

在上述insert语句之后放置以下语句..。

INSERT INTO AB ( id_A,
                 id_B )
SELECT id_A_value,
       B.id_B
FROM B
WHERE B.id_B > @maxIDB;

注:通过您的上述问题，我假设id_A是已知的。我用id_A_value表示这个值。

此语句将插入到AB行中，其中包含id_A的已知值和所有新生成的id_B值。

如果您有任何问题或意见，请随时发表相应的评论。