从选择中插入多行，并将生成的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列中。

我可以在测试版中创建这样的行：

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

如何将生成的beta id放入alpha中相应行的beta_id列中

测试版中的行没有独特的特征，这使我能够回顾性地识别它们。

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

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.显然，我们的实际模式有些复杂，但我试图将其归结为演示问题所需的信息}

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，其他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;

Answer 2

如果存在一对一映射，则可以使用类型列join返回：

update alpha a join
beta b 
on a.type_info = b.type_info
set a.beta_id = b.id;

编辑：

MySQL对此没有很好的功能。但您可以添加一个alpha_id(可能是暂时的(并执行：

alter table beta add column alpha_id int;
INSERT INTO beta (alpha_id, type_info)
SELECT a.id, a.type_info
FROM alpha a
WHERE beta_id IS NULL;
update alpha a join
beta b 
on a.id = b.alpha_id
set a.beta_id = b.id;

如果需要，可以在更新后删除该列。