Блог

Мой обычный процесс вставки в одну таблицу, а затем получения идентификатора обратно, чтобы я мог вставить в другую таблицу, выглядит следующим образом в MSSQL:

 DECLARE @transactionKey uniqueidentifier
SET @transactionKey = NEWID()

INSERT INTO transactions(transactionKey, transactionDate, transactionAmount)
    VALUES(@transactionKey, '#transactionDate#', '#transactionAmount#')

DECLARE @transactionID int
SELECT @transactionID = transactionID
    FROM transactions
    WHERE transactionKey = @transactionKey


INSERT INTO transactionItems(transactionID, itemID, itemAmount)
    VALUES(@transactionID, '#itemID#', '#itemAmount#')


SELECT @transactionID as transactionID
 

Мой вопрос состоит из 2 частей. Во-первых, это лучший способ сделать это? Я читал, что есть вероятность, что идентификатор GUID изменится у меня, и я получу недопустимый идентификатор GUID во второй таблице. Я предполагаю, что шансы на это очень малы, и я годами занимался этим в различных проектах и никогда не сталкивался с проблемами.

Вторая часть моего вопроса: работает ли что-то подобное в MySQL? Я начинаю работать над новым проектом, используя MySQL, и я не совсем уверен, что лучший способ сделать это. В прошлом я обычно работал только с MSSQL.

Я использую CF9 и MySQL в этом новом проекте.

Любая помощь в этом была бы отличной.

Заранее спасибо.

Часть 1: Лично я бы не стал использовать несколько операторов в одном запросе, чтобы снизить риск внедрения SQL. Это настройка в вашем источнике данных у администратора ColdFusion. Выполнение хранимой процедуры, которая может быть тем, что вы делаете (?), — это другая история, но вам следует перефразировать свой вопрос на «Получить первичный ключ после вставки с помощью хранимой процедуры MySQL», если это ваше намерение.

Часть 2. ColdFusion, как и многое другое, упрощает получение первичного ключа для вновь вставленной записи — даже если вы используете ключи автоматического увеличения, идентификаторы GUID или что-то вроде Oracle ROWNUM. Это будет работать практически с любой базой данных, поддерживаемой Adobe ColdFusion, включая MSSQL или MySQL. Единственным исключением является версия базы данных — например, MySQL 3 не будет поддерживать это; однако MySQL 4 будет.

 <cfquery result="result">
  INSERT INTO myTable (
      title
  ) VALUES (
    <cfqueryparam value="Nice feature!" cfsqltype="cf_sql_varchar">
  )
</cfquery>

<--- get the primary key of the inserted record --->
<cfset NewPrimaryKey = result.generatedkey>
 

Начиная с CF9 , вы можете получить доступ к новому идентификатору (для любой базы данных), используя общее имя ключа:

 result.GENERATEDKEY    // All databases
 

Для CF8 разные базы данных будут иметь разные ключи в пределах значения результатов. Вот простая таблица, которую я скопировал из документации cfquery.

 result.identitycol    // MSSQL
result.rowid          // Oracle
result.sys_identity   // Sybase
result.serial_col     // Informix
result.generated_key  // MySQL
 

Если у вас есть какие-либо вопросы, вы можете увидеть симпатичный дамп следующим образом:

 <cfdump var="#result#" />
 

Вот быстрое решение для MSSQL. Он использует функцию SCOPE_IDENTITY(), которая возвращает идентификатор последней строки, вставленной в предыдущий оператор insert.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

 <cfquery>
    DECLARE @iNewGeneratedID INT

    INSERT INTO transactions
        (
            transactionDate,
            transactionAmount
        )
    VALUES
       (
            <cfqueryparam value="#transactionDate#" type="cf_sql_date">,
            <cfqueryparam value="#transactionAmount#" type="cf_sql_integer">
       )

    SET @iNewGeneratedID = SCOPE_IDENTITY()

    INSERT INTO transactionItems
        (
            transactionID,
            itemID,
            itemAmount
        )
    VALUES
        (
            @iNewGeneratedID,
           <cfqueryparam value="#itemID#" type="cf_sql_integer">,
           <cfqueryparam value="#itemAmount#" type="cf_sql_integer">
        )

    SELECT @iNewGeneratedID AS iNewGeneratedID
</cfquery>
 

Согласно ответу @aaron-greenlee, результирующая переменная запросов INSERT содержит пару ключ-значение, которая является автоматически сгенерированным идентификатором вставленной строки; это доступно только для баз данных, поддерживающих эту функцию.

И ниже приведен возможный способ вернуть вставленную запись для всех баз данных.

 <cfquery result="result">
   INSERT INTO myTable (
      title
   )
   OUTPUT INSERTED.*
   VALUES (
   <cfqueryparam value="Nice feature!" cfsqltype="cf_sql_varchar">
   )
</cfquery>
 

В результате вы получите вставленные сведения о записи.

Надеюсь, это поможет. Спасибо.

Функция getGeneratedKey в cflib.org может использоваться с большинством баз данных:

Пример:

 <cfquery name="insertArtist" datasource="cfartgallery" result="r">
    insert into artists (firstName, lastName)
    values('todd','sharp')
</cfquery>
<cfquery name="getArtists" datasource="cfartgallery">
    select *
    from   artists
</cfquery>
<cfdump var="#getArtists#">
<cfoutput>#getGeneratedKey(r)#</cfoutput>

<cffunction name="getGeneratedKey" 
    hint="I normalize the key returned from cfquery" output="false">

      <cfargument name="resultStruct" hint="the result struct returned from cfquery" />
      <cfif structKeyExists(arguments.resultStruct, "IDENTITYCOL")>
          <cfreturn arguments.resultStruct.IDENTITYCOL />
      <cfelseif structKeyExists(arguments.resultStruct, "ROWID")>
          <cfreturn arguments.resultStruct.ROWID />
      <cfelseif structKeyExists(arguments.resultStruct, "SYB_IDENTITY")>
          <cfreturn arguments.resultStruct.SYB_IDENTITY />
      <cfelseif structKeyExists(arguments.resultStruct, "SERIAL_COL")>
          <cfreturn arguments.resultStruct.SERIAL_COL />  
      <cfelseif structKeyExists(arguments.resultStruct, "GENERATED_KEY")>
          <cfreturn arguments.resultStruct.GENERATED_KEY />
      <cfelse>
          <cfreturn />
      </cfif>
</cffunction>