“插入……选择”具有多个值和规范化数据库的事务

This question properly explains how to simultaneously insert data in a database for "hierarchical" structures (is-a relationship):

这个问题恰当地解释了如何在数据库中同时为“分层”结构(is-a关系)插入数据:

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

不，不能在一个MySQL命令中插入多个表。但是，您可以使用事务。
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;

The solution works well when inserting things one at a time.

当每次插入一个东西时，这个解决方案可以很好地工作。

I'm, however, in a situation where I need to insert multiple values at the same time using INSERT...SELECT.

但是，我需要同时使用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;

How am I supposed to tell MySQL to "link" every instance of derived_class to the previously created base_class through the base class IDs?

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

Ideally, I would "loop" through the two tables simultaneously, but that's impossible in MySQL or MariaDB:

理想情况下，我将同时对这两个表进行“循环”，但在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;

How can I solve this issue while maintaining the "hierarchical" design of my tables?

如何在维护表的“层次化”设计的同时解决这个问题?

EDIT:

编辑:

I've opened this question again because I'm curious if it's possible to achieve the behavior I desire without having to resort to procedural-like SQL code (using cursors).

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

Is there a way of solving this issue without using cursors?

有没有一种不用游标的方法来解决这个问题?

2 个解决方案

#1

Cursors should do the trick. You can loop through the stuff table and perform the inserts one at a time, grabbing the insert IDs as you go. Something like this (untested):

游标应该可以做到这一点。您可以对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;

Another possible approach would be to create a view on the two "insert" tables. There may be restrictions on inserting to the view based on how your tables are structured, but something like this might work (also not tested):

另一种可能的方法是在两个“插入”表上创建一个视图。根据表的结构，可能对插入视图有限制，但类似这样的操作可能有效(也没有经过测试):

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

I'm actually not sure if MySQL will automatically assign appropriate IDs to both the base and derived class tables.

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

#2

I'm not entirely sure I understand your question. I think you're trying to insert d0 and d1 into base_class and base_ids, d2, and d3 into derived_class in the same order so that the nth inserted values in both base_class and derived_class all come from the nth record in stuff. If my understanding is correct, the following transaction will do what you want.

我不完全明白你的问题。我认为你在尝试将d0和d1以相同的顺序插入到base_class和base_id, d2和d3到derived_class中，这样第n个在base_class和derived_class中插入的值都来自第n条记录。如果我的理解是正确的，下面的事务将做你想做的。

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

#1