在插入ColdFusion和MySQL之后获取表ID [英] Get table ID after insert with ColdFusion and MySQL

问题描述

我插入一个表，然后获取ID，以便我可以插入另一个表的正常过程就像这样在MSSQL：

  DECLARE @transactionKey uniqueidentifier 
 SET @transactionKey = NEWID（）
 
 INSERT INTO事务（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的一个新项目，我不完全确定最好的方法做到这一点。

我在这个新项目中使用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> 
 
< ---获取插入记录的主键---> 
< cfset NewPrimaryKey = result.generatedkey> 
 

从CF9 +起，您可以使用通用键名称访问新ID（对于任何数据库）：

  result.GENERATEDKEY //所有数据库
  

对于CF8，不同的数据库在结果值中将有不同的键。以下是一个简单的表格，可帮助我从 cfquery文档 a>。

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

你有任何问题，你可以看到一个漂亮的转储如下：

 < cfdump var =＃result＃/> 
  




My normal process for inserting into one table and then getting the ID back so that I can insert into another table is like this in 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

My question is 2 parts. First, is this the best way to do this? I read that there is a chance that the GUID changes on me and I end up with a invalid GUID in the second table. I am assuming the chances of that are very slim and I have been doing this for years on various projects and have never had a problem.

The second part of my question is does something like this work in MySQL? I am starting to work on a new project using MySQL and I am not exactly sure the best way to do this. I have normally only worked on MSSQL in the past.

I am using CF9 and MySQL on this new project.

Any help on this would be great.

Thanks in advance.

解决方案

Part 1: I would personally not batch multiple statements within a single query to reduce the risk of SQL injection. This is a setting within your datasource on the ColdFusion administrator. Executing a stored procedure, which might be what you are doing(?), is another story, but, you should rephrase your question to "Get primary key after insert with mySQL Stored Procedure" if that is your intention.

Part 2: ColdFusion, like many things, makes getting the primary key for a newly inserted record very easy--even if you are using auto-increment keys, GUIDs or something like Oracle's ROWNUM. This will work on any almost every database supported by Adobe ColdFusion including MSSQL or MySQL. The only exception is the version of the databse--for example, MySQL 3 will not support this; however, MySQL 4+ will.

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

As of CF9+, you can access the new ID (for any database) using the generic key name:

result.GENERATEDKEY    // All databases

For CF8, different databases will have different keys within the results value. Here is a simple table to help I copied from the cfquery documentation.

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

If you have any questions you can see a pretty dump as follows:

<cfdump var="#result#" />

这篇关于在插入ColdFusion和MySQL之后获取表ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！