如何将一个表的记录ID作为外键插入另一个表 [英] How do I insert record ID of one table, to another table as a foreign key

问题描述

我有table1和table2。

table1的主键是自动更新recordID。

table2的外键是table1的recordID。

我目前正在做的是，

首先插入table1，

I have table1 and table2.
Primary key of table1 is auto updating recordID.
Foreign key of table2 is recordID of the table1.
What I'm currently doing is,
First insert to table1,

INSERT INTO table1(col1, col2, col3) VALUES (val1, val2 val3)

然后选择插入记录的自动更新记录ID，

Then select the auto updated record id of the inserted record,

SELECT table1RecordID FROM table1 ORDER BY table1RecordID DESC LIMIT 1

并将该记录ID作为外键插入table2。

And insert that record id to the table2 as the foreign key.

INSERT INTO table2(foreignKeyCol, col1, col2) VALUES (table1ReacordID, val1, val2)

还有其他更好的方法吗？这样做？

Are there any other preferable ways of doing this?

推荐答案

不要这样做！ MySQL和MSSQL是多用户系统，因此无法保证您的选择将检索您刚刚输入的记录的ID - 没有任何内容可以阻止其他用户在您选择之后以及在您选择之前输入记录。

而是使用@@ IDENTITY来检索当前连接上的最后一个自动生成的ID（您可以使用 LAST_INSERT_ID（） [ ^ ]，但@@ IDENTITY适用于MSSQL和MYSQL）或更好，使用GUID ID并从其他人设置它们代码 - 这样你就不需要在插入后获取任何东西了。

Don't do it like that! MySQL and MSSQL are multiuser systems, so there is no guarantee that your select will retrieve the ID of the record you just entered - there is nothing that stops a different user entering a record after you do, and before your select.
Instead, either use @@IDENTITY to retrieve the last automatically generated ID on teh current connection (you can use LAST_INSERT_ID()[^] as well, but @@IDENTITY works for MSSQL and MYSQL as well) or better, use GUID ID's and set them from your other code - that way you don't need to fetch anything once inserted.

INSERT INTO table1(col1, col2, col3) VALUES (val1, val2 val3)
INSERT INTO table2(foreignKeyCol, col1, col2) VALUES (@@IDENTITY, val1, val2)

这篇关于如何将一个表的记录ID作为外键插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！