Получить идентификатор таблицы после вставки с ColdFusion и MySQL

Мой обычный процесс для вставки в одну таблицу, а затем получения идентификатора обратно, чтобы я мог вставить в другую таблицу, похож на 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

Часть 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 +, вы можете получить доступ к новому ID (для любой базы данных) с помощью общего имени ключа:

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#" />

Ответ 2

Вот быстрое решение для 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>

Ответ 3

Функция 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>

Ответ 4

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

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

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

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

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