如何访问 SQL 脚本中最后插入的行 ID?

本文介绍了如何访问 SQL 脚本中最后插入的行 ID?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 SQLite，我有一个属性表和一个子属性表.每个子属性都使用 fkPropertyId 列指向其父属性.现在，为了创建初始数据库，我有一个看起来像这样的脚本:

I'm using SQLite, and I have a table for properties, and a table for sub-properties. Each sub-property points to its parent using the fkPropertyId column. Right now, to create the initial database, I've got a script that looks something like this:

INSERT INTO property VALUES(1,.....);
INSERT INTO property VALUES(2,.....);
INSERT INTO property VALUES(3,.....);
   INSERT INTO subproperty VALUES(1,.....,3);
   INSERT INTO subproperty VALUES(2,.....,3);
   INSERT INTO subproperty VALUES(3,.....,3);
INSERT INTO property VALUES(4,.....);

现在，我想去掉硬编码的 rowId，所以它会是这样的:

Now, I want to get rid of the hard-coded rowId, so it would be something like:

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
   INSERT INTO subproperty VALUES(NULL,.....,X);
INSERT INTO property VALUES(NULL,.....);

其中 x 指的是属性表中最后插入的 rowId.现在是

Where x refers to the last inserted rowId in the property table. Right now, that's

(SELECT MAX(rowId) FROM property)

有没有更好(技术上更准确)的方法来编写这个脚本?

Is there any better (and more technically accurate) way to write this script?

推荐答案

好吧，我想出的解决方案使用了 Ben S 的 last_insert_rowid 函数:

Well, the solution I came up with used the last_insert_rowid function from Ben S:

INSERT INTO property VALUES(NULL,.....);
INSERT INTO property VALUES(NULL,.....);

   INSERT INTO subproperty VALUES(1,.....,-1);
   INSERT INTO subproperty VALUES(2,.....,-1);
   INSERT INTO subproperty VALUES(3,.....,-1);
INSERT INTO property VALUES(NULL,.....);
UPDATE subproperty SET fkPropertyId = (SELECT last_insert_rowid()) WHERE fkPropertyId=-1;

INSERT INTO property VALUES(NULL,.....);

不确定这是否是最好的方法，但它对我有用，而且它不使用任何额外的表来临时存储数据.

Not sure if that's the best approach, but it works for me, and it doesn't use any extra tables for temporary data storage.

这篇关于如何访问 SQL 脚本中最后插入的行 ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Any