dpt62283
2019-03-05 09:11
浏览 101
已采纳

如何为主表和辅助表编写SQL多插入查询

I have two tables and I want to write a multi insert query such that, the PK from first table is auto inserted as fk in another table.

Let us assume following table schemas:

tblParent

ParentID (Primary Key Auto increment)
ParentValue

tblChild

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

Now I want to write a multi insert query like following

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')

But I do not know how to do this. I have millions of records to be inserted in these tables. And I do not want to insert a record in first table, then fetch parent id and insert records in second table.

Edit 1: My Database is Mysql InnoDb

Edit 2: The tables in question have one-to-many relationship. So this means I want to perform one insert in tblParent and multiple inserts into tblChild for each record in tblParent

Edit 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.)

So my next question is, how would LAST_INSERT_ID behave in case of multi insert for tblParent which in turn has multiple inserts in tblChild for each tblParent

图片转代码服务由CSDN问答提供 功能建议

我有两个表,我想写一个多插入查询,这样,第一个表中的PK是自动插入的 在另一个表格中作为fk。

让我们假设以下表格模式:

tblParent \ n

  ParentID(主键自动增量)
ParentValue 
   
 
 

tblChild ChildID(主键自动增量) FkParentID(外键) ChildValue

现在我想写一个多插入查询,如下所示

  INSERT INTO tblParent,tblChild(ParentValue,FkParentID,ChildValue)VALUES('Parent 1',&lt; ParentID'父母1'&gt;,'Child 1'),(' 父2',&lt;'父2'的父母'>,'儿童2'),('父母3',&lt;父母3'的父母ID','儿童3')
  <  / pre> 
 
 

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

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

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

编辑3:

我正在查看MySql文档并遇到以下描述:

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

当前正在执行的语句不会影响LAST_INSERT_ID()的值。 假设您使用一个语句生成AUTO_INCREMENT值,然后在多行INSERT语句中引用LAST_INSERT_ID(),该语句将行插入到具有自己的AUTO_INCREMENT列的表中。 LAST_INSERT_ID()的值将在第二个语句中保持稳定; 它的第二行和后一行的值不受先前行插入的影响。 (但是,如果混合对LAST_INSERT_ID()和LAST_INSERT_ID(expr)的引用,则效果未定义。)

所以我的下一个问题是, LAST_INSERT_ID 在tblParent的多插入的情况下表现,tblParent又为每个tblParent在tblChild中有多个插入

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • duanmeng1950 2019-03-06 07:20
    已采纳

    Anyways, for now what I did is used multi_query to insert values.

    I did following inside a while loop:

    $autoIncForParent = <Max ID from tblParent>;
    while(<condition to evaluate>) {
        if (!empty($multiQuery)) {
            $multiQuery .= ";";
        }
        $multiQuery .= "INSERT INTO tblParent (ParentID , ParentValue) VALUES ($autoIncForParent, 'Parent 1');";
        $multiQuery .= "INSERT INTO tblChild (FkParentID , ChildValue) VALUES ($autoIncForParent, 'Child 1');";
    $autoIncForParent++;
    }
    $mysqli->multi_query($multiQuery);
    

    I am pretty sure there must be another good way of solving this.

    I have taken care of memory issues, race conditions and what nots in my actual code. This snippet is for elaboration purpose only. By no means this snippet is perfect and I would not recommend this for production.

    Edit 1: Added missing semicolons. In case someone refers this

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duanbi2003 2019-03-05 09:31
    <?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            
        ?>
    

    you can able to use multiple insert like this.

    评论
    解决 无用
    打赏 举报
  • duanhuan6857 2019-03-05 11:37

    You can Insert data in two table following way:-

    $result = INSERT INTO tblParent (ParentValue) VALUE('ABC');
    if($result == true){
        $query = SELECT * tblParent ORDER BY ParentID DESC LIMIT 1;
        $row = $query->fetch_assoc();
        $ParentID = $row['ParentID'];
        INSERT INTO tblChild (FkParentID,ChildValue) VALUE('$ParentID','XYZ');
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题