mysql - 从选择中插入多行并将生成的 ID 放入其他表中

Question

给定 MySQL 表，如下所示¹：

alpha (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, beta_id INT NULL
, type_info INT NOT NULL
, CONSTRAINT fk_alpha_beta FOREIGN KEY (beta_id) REFERENCES beta (id)
) ENGINE=InnoDB;

beta (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, type_info INT NOT NULL
) ENGINE=InnoDB;

我的 alpha 表包含很多行，我想在表 beta 中创建相应的行，并将 id 存储在alpha.beta_id列中。

我可以像这样一次性创建 beta 中的行：

INSERT INTO beta (type_info)
SELECT type_info FROM alpha
WHERE beta_id IS NULL;

我怎样才能做到这一点，以便我可以将生成的 beta id 放入beta_idalpha 中相应行的列中？

beta 中的行没有独特的特征，可以让我回顾性地识别它们。

如果我只创建一行，我可以使用类似的东西：

UPDATE alpha
SET beta_id=LAST_INSERT_ID()
WHERE beta_id IS NULL;

但这似乎不适用于多行。

这个问题有点相似，但它正在使用INSERT ... VALUES并且不适用于INSERT ... SELECT.

这个问题有类似的要求，我知道我可以ROW_COUNT用来查看插入了多少行，但这并没有给我添加的行和为其创建的 alpha 行之间的对应关系。

我只剩下编写一个非 SQL 脚本，该脚本将提取所有信息，一次创建一个 beta 行，然后单独更新 alpha 中的行。但我宁愿在 SQL 中执行此操作，这似乎是一个相当明显的用例。

---

^{1. 显然，我们的实际模式要复杂一些，但我试图将其归结为演示问题所需的信息。}

标签： mysqlsql

Answer 1

您可以假设多行 INSERT 生成连续的自动增量 id，除非innodb_auto_inc_lock_mode=2. 见https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

LAST_INSERT_ID() 将告诉您插入生成的第一个 id，其他的每个都会大 1，与插入的行数一样多。

然后你可以 UPDATE alpha 来设置这些，但一定要使用 ORDER BY 来指定复制的顺序和 UPDATE 的顺序，所以它们保持不变。

INSERT INTO beta (type_info)
SELECT type_info FROM alpha
WHERE beta_id IS NULL
ORDER BY id;

SET @id = LAST_INSERT_ID()-1;

UPDATE alpha
SET beta_id=(@id := @id + 1)
WHERE beta_id IS NULL
ORDER BY id;