mysql - "Insert...select"具有多个值和规范化数据库的事务

Question

This question正确解释了如何在“分层”结构(is-a 关系)的数据库中同时插入数据:

No, you can't insert into multiple tables in one MySQL command. You can however use transactions.

BEGIN;
INSERT INTO base_class (data0, data1) VALUES('0', '1');
INSERT INTO derived_class (base_id, data2, data3) VALUES(LAST_INSERT_ID(), '2', '3');
COMMIT;

该解决方案在一次插入一个东西时效果很好。

但是，我需要使用 INSERT...SELECT 同时插入多个值。

BEGIN;
INSERT INTO base_class (data0, data1) SELECT d0, d1 FROM stuff
INSERT INTO derived_class (base_ids, data2, data3) SELECT ???, d2, d3 FROM stuff
COMMIT;

我应该如何告诉 MySQL 通过基类 ID 将 derived_class 的每个实例“链接”到先前创建的 base_class？

理想情况下，我会同时“循环”两个表，但这在 MySQL 或 MariaDB 中是不可能的:

# PSEUDOCODE! NOT VALID.
BEGIN;
INSERT INTO 
                base_class (data0, data1) 
     ALONG_WITH derived_class (base_ids, data2, data3)   
     SELECT d0, d1, GET_ALONG_FIELD(base_class, id), d2, d3 FROM stuff
     #      _______ table 1 values
     #              _______________________________________ table 2 values
COMMIT;

如何在保持表的“分层”设计的同时解决这个问题？

---

编辑:

我再次打开了这个问题，因为我很好奇是否有可能实现我想要的行为而不必求助于类似过程的 SQL 代码(使用游标)。

有没有不用游标解决这个问题的方法？

Answer 1

游标应该可以解决问题。您可以遍历 stuff 表并一次执行一个插入操作，同时获取插入 ID。像这样的东西(未经测试):

BEGIN;

  DECLARE get_stuff CURSOR FOR SELECT id FROM stuff;
  DECLARE current_id INT;

  OPEN get_stuff;
  insert_stuff: LOOP

    FETCH get_stuff INTO current_id;
    INSERT INTO base_class (data0, data1)
      SELECT d0, d1 FROM stuff WHERE id = current_id
    INSERT INTO derived_class (base_ids, data2, data3)
      SELECT mysql_insert_id(), d2, d3 FROM stuff WHERE id = current_id

  END LOOP insert_stuff;
  CLOSE get_stuff;

COMMIT;

另一种可能的方法是在两个“插入”表上创建一个 View 。根据您的表的结构，可能会限制插入到 View ，但这样的事情可能有效(也未测试):

CREATE VIEW stuff_view AS
  SELECT b.data0, b.data1, d.data2, d.data3 FROM base_class AS b
  INNER JOIN dervied_class AS d ON (d.base_id = b.id)

INSERT INTO stuff_view (data0, data1, data2, data3)
  SELECT d0, d1, d2, d3 FROM stuff

我实际上不确定 MySQL 是否会自动为基类表和派生类表分配适当的 ID。