php - 如何编写主表和副表的 SQL 多插入查询

Question

我有两个表，我想编写一个多插入查询，以便将第一个表中的 PK 作为 fk 自动插入到另一个表中。

让我们假设以下表架构:

tblParent

ParentID (Primary Key Auto increment)
ParentValue

tblChild

ChildID (Primary Key Auto increment)
FkParentID (Foreign Key)
ChildValue

现在我想写一个像下面这样的多插入查询

INSERT INTO tblParent, tblChild (ParentValue, FkParentID, ChildValue) VALUES ('Parent 1', <ParentID of 'Parent 1'>, 'Child 1'), ('Parent 2', <ParentID of 'Parent 2'>, 'Child 2'), ('Parent 3', <ParentID of 'Parent 3'>, 'Child 3')

但是我不知道该怎么做。我有数百万条记录要插入这些表中。而且我不想在第一个表中插入记录，然后获取父 ID 并在第二个表中插入记录。

编辑 1:我的数据库是 Mysql InnoDb

编辑 2:有问题的表具有一对多关系。所以这意味着我想在 tblParent 中执行一次插入，并为 tblParent 中的每条记录在 tblChild 中执行多次插入

编辑 3:

I was looking in the MySql documentation and came across following description:

https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

所以我的下一个问题是，LAST_INSERT_ID 在为 tblParent 进行多次插入的情况下会如何表现，而 tblParent 又在 tblChild 中为每个 tblParent 进行多次插入

Answer 1

<?php
//your codes

       $inset_tblParent= "INSERT INTO tblParent (ParentValue, FkParentID, ChildValue)VALUES('Parent 1', <ParentID of 'Parent 1'>, 'Child 1')";

     $inset_tblChild= "INSERT INTO tblChild (ParentValue, FkParentID, ChildValue)VALUES('Parent 2', <ParentID of 'Parent 2'>, 'Child 2')";


//your doces            
    ?>

您可以像这样使用多个插入。