如何获取一个最近插入的记录数组? [英] how can i get an array of recently inserted records ids?

查看:301
本文介绍了如何获取一个最近插入的记录数组?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个名为questions的表。此表可能有多个问题。这里我将循环插入查询插入所有问题值有重复的问题。它是成功添加问题,但问题是我不能得到最近插入的问题记录ids的列表或数组。

I have one table named "questions". This table may have multiple questions. Here I am looping over the insert query to insert all question values for having duplicate questions. It is successfully adding questions, but the issue is I can't get the list or array of recently inserted question records ids.

以下是代码。

   <cfloop query="questions">
      <cfquery name="insertDuplicatequestions">
          INSERT INTO questions (
              survey_Id, questiontype_Id, question, rank, isrequired
          )
          VALUES 
          (
              <cfqueryparam cfsqltype="cf_sql_bigint" value="#getSurveyid.id#">
             , <cfqueryparam cfsqltype="cf_sql_bigint" value="#questions.questiontype_Id#">
             , <cfqueryparam cfsqltype="cf_sql_varchar" value="#questions.question#">
             , <cfqueryparam cfsqltype="cf_sql_numeric" value="#questions.rank#">
             , <cfqueryparam cfsqltype="cf_sql_integer" value="#questions.isrequired#"> 
          ) 
          <cfif questions.CurrentRow LT questions.RecordCount>,</cfif>
      </cfquery> 

      <cfquery name="getQuestionid">
       SELECT LAST_INSERT_ID() AS id                  
      </cfquery>  
       <cfset response = getQuestionid.id>
  </cfloop>

我试过LAST_INSERT_ID(),但它检索最后和单个记录id。任何人都可以让我知道,如果有反正找出这一点。感谢。

I have tried with LAST_INSERT_ID(), but it retrieves last and single record id. Could anyone please let me know if there is anyway to figure out this. Thanks.

推荐答案

(更多附录,但对评论太长了...)

(More of an addendum, but it is too long for comments ... )

Like RandomSeed说,捕获由生成的多个记录ID,在MySQL中不支持插入。他描述的替代方案之一是单独插入记录,并且在每次迭代中捕获新ID。以下是如何使用cfquery的 result 属性。

Like RandomSeed said, capturing multiple record id's generated by an insert is not supported in MySQL. One of the alternatives he described is inserting the records individually, and capturing the new ID on each iteration. Here is a rough outline of how you might do that using cfquery's result attribute.

 <!--- initialize array that will store the new ids --> 
 <cfset idArray = []>

 <!--- use a transaction to ensure ALL of the inserts succeed or fail together -->
 <cftransaction>
     <cfloop ...>

         <!--- use the "result" attribute to capture the new id --->
         <cfquery result="addRecord" ....>
           INSERT INTO YourTable (...) VALUES (...);
         </cfquery>

         <!--- save the id in the array -->
         <cfset arrayAppend(idArray, addRecord.GENERATED_KEY)>

     </cfloop>
 </cftransaction>

 <!--- display new id's --->
 <cfdump var="#idArray#">

另外,虽然可以使用 LAST_INSERT_ID(),它通常应放置在相同的 cfquery 标签中作为INSERT,以保证获得当前连接的最后一个ID。注意:要使其正常工作,数据源设置必须允许多个语句(默认情况下不启用) )。

As an aside, while you can use LAST_INSERT_ID(), it should normally be placed inside the same cfquery tags as the INSERT to guaranteed you get the last id for the current connection. Note: For it to work, the datasource settings must allow multiple statements (not enabled by default).

这篇关于如何获取一个最近插入的记录数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆