mysql - 如何在添加记录时插入 id 列表？

Question

我有一个问题 ID 列表，在添加记录时要插入到答案表中。

<!--- answers query --->
<cfquery name="answers">
    SELECT answer, rank 
    FROM answers 
    WHERE question_id IN (<cfqueryparam  cfsqltype="cf_sql_integer" list="true" separator="," value="#qid#">)                       
</cfquery>

qid 是旧问题 ID 的列表，我通过它检索答案，然后用新问题 ID 复制相同的答案。

<!--- list of questionids --->
<cfset questionid = ArrayToList(idArray)> 
    <cfquery name="insertanswers">
        INSERT INTO answers (
            question_id, answer, rank
        )
        VALUES 
            <cfloop query="answers">
                (
                <cfqueryparam cfsqltype="cf_sql_integer" list="true" separator="," value="#questionid#">
                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#answers.answer#">
                ,<cfqueryparam cfsqltype="cf_sql_numeric" value="#answers.rank#">
                )
                <cfif answers.CurrentRow LT answers.RecordCount>,</cfif> 
            </cfloop>
    </cfquery>

在添加记录时，我收到列计数与值计数不匹配错误，但是当我用cftry包装代码并转储它时，我发现它获得了两个ID每个答案均以逗号分隔。有什么方法可以为每个答案只传递一个 id 吗？我只是将旧的问题 ID 替换为新的问题 ID。

Answer 1

由于您需要链接新旧 ID，因此您需要采用与 the one used here 不同的方法。 。当您生成新的问题记录时，将 id 存储在结构体中，而不是数组中，这样您就可以维护旧值 => 新值的映射。

<!--- initialize mapping of old => new ids --> 
 <cfset idMapping = {}>

  <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 idMapping[ oldQuestionID ] = addRecord.GENERATED_KEY>

 </cfloop>

插入新答案时，使用旧 ID 进行查找并从结构中获取新问题 ID。这需要验证，但这是总体思路。

注意:为了确保数据完整性，两个查询 block 应包含在一个 <cftransaction> 中。

   <cfquery name="insertanswers">
        INSERT INTO answers (
            question_id, answer, rank
        )
        VALUES 
            <cfloop query="answers">
                (
                <!--- get the new id from the structure --->
                <cfqueryparam cfsqltype="cf_sql_integer" value="#idMapping[ oldQuestionID ]#">
                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#answers.answer#">
                ,<cfqueryparam cfsqltype="cf_sql_numeric" value="#answers.rank#">
                )
                <cfif answers.CurrentRow LT answers.RecordCount>,</cfif> 
            </cfloop>
    </cfquery>

更新1:

这是一个完整的示例:

• SQL
• CF Code

更新2:

如果这是经常发生的情况，我会建议采用不同的方法来消除循环。向主表添加 UUID 列(用于标识新记录)。然后使用临时表(带有自动填充 UUID 列)来存储要传输的记录。它并不像听起来那么复杂..

(我没有方便的 MySQL 语法，所以这是针对 SQL Server 的，但总体概念是相同的)。

    -- use DEFAULT to automatically generate a UUID for each record
    CREATE TABLE #NewQuestions ( 
        , OldQuestionID int
        , TheUUIDColumn uniqueidentifier DEFAULT(NewID())
    )

    --- insert the records you want to transfer  
    INSERT INTO #NewQuestions ( OldQuestionID )
    SELECT  QuestionID
    FROM    Questions
    WHERE   .....

接下来，使用 JOIN 将这些问题插入主表中。请注意，它如何存储 UUID，以便我们稍后可以识别新记录。

    INSERT INTO Questions( TheUUIDColumn, Question, ... )
    SELECT  tmp.TheUUIDColumn, q.Question, ....
    FROM    Questions q INNER JOIN #NewQuestions tmp 
                 ON tmp.OldQuestionID = q.QuestionID

最后，使用 UUID 来识别新旧 ID 并插入相关的“答案”。

    INSERT INTO answers ( QuestionID, Answer, ....)
    SELECT q.QuestionID, a.Answer
    FROM   Questions q 
                INNER JOIN #NewQuestions tmp ON tmp.TheUUIDColumn = q.TheUUIDColumn
                INNER JOIN answers a ON a.QuestionID = tmp.OldQuestionID

临时表方法提供了更好的控制。它也是基于集合的，这比通过循环一次处理一条记录要高效得多。