使用 ColdFusion 和 MySQL 插入后获取表 ID

2022-01-09 00:00:00 mysql sql-server coldfusion

我插入一个表然后取回 ID 以便我可以插入另一个表的正常过程在 MSSQL 中是这样的:

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

我的问题分为两部分.首先，这是最好的方法吗?我读到 GUID 有可能在我身上发生变化，最终我在第二个表中得到了无效的 GUID.我假设这种情况的可能性非常小，而且我多年来一直在各种项目中这样做并且从未遇到过问题.

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.

我的问题的第二部分是这样的事情在 MySQL 中是否有效?我开始使用 MySQL 开展一个新项目，但我不确定最好的方法.我过去通常只在 MSSQL 上工作过.

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.

我在这个新项目中使用 CF9 和 MySQL.

I am using CF9 and MySQL on this new project.

在这方面的任何帮助都会很棒.

Any help on this would be great.

提前致谢.

推荐答案

第 1 部分:我个人不会在单个查询中批处理多个语句以降低 SQL 注入的风险.这是 ColdFusion 管理员数据源中的一个设置.执行存储过程，这可能是你正在做的事情(?)，是另一回事，但是，如果这是你的意图，你应该将你的问题改写为使用 mySQL 存储过程插入后获取主键".

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.

第 2 部分:ColdFusion 和许多事情一样，使得获取新插入记录的主键变得非常容易——即使您使用的是自动递增键、GUID 或 Oracle 的 ROWNUM 之类的东西.这将适用于 Adob​​e ColdFusion 支持的几乎所有数据库，包括 MSSQL 或 MySQL.唯一的例外是数据库的版本——例如，MySQL 3 将不支持它；但是，MySQL 4+ 会.

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>

从 CF9+ 开始，您可以使用通用键名访问新 ID(适用于任何数据库):

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

result.GENERATEDKEY    // All databases

对于 CF8，不同的数据库在结果值中会有不同的键.这是我从 cfquery 文档.

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

相关文章