如何使用 last_insert_rowid 插入多行? [英] How can you use last_insert_rowid to insert multiple rows?
问题描述
假设我有一个包含两个表 Persons
和 PhoneNumbers
的数据库,其中 PhoneNumbers
表有一个指向 Persons 的外键代码>.如果我想在单笔交易中插入一个有电话号码的人,我可以写一个这样的查询:
Let's say I have a database with two tables Persons
and PhoneNumbers
, where the PhoneNumbers
table has a foreign key to Persons
. If I want to insert a person with a phone number in a single transaction, I can write a query like this:
BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
END TRANSACTION;
但是如果我想插入一个有多个电话号码的人怎么办?显而易见的方法:
But what if I want to insert a person with multiple phone numbers? The obvious way:
BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
END TRANSACTION;
当然不行,因为对于第二个电话号码,last_insert_rowid()
将返回第一个电话号码的 rowid 而不是人.
won't work, of course, because for the second phone number, last_insert_rowid()
will return the rowid of the first phone number instead of the person.
有没有办法使用基本的 SQL(特别是 SQLite)来做到这一点?
Is there a way to do this using basic SQL (SQLite, specifically)?
结论
显然,没有直接的方法可以做到这一点.我根据@Michal Powaga 的建议和其他一些基于临时表的想法做了一些基准测试,最快的方法似乎是这样的:
Apparently, there is no direct way to do this. I've made a few benchmarks with @Michal Powaga's suggestions and a few other ideas based on temporary tables, and the fastest way to do it seems to be something like this:
CREATE TEMPORARY TABLE IF NOT EXISTS Insert_PhoneNumbers(PersonForeignKey INTEGER, PhoneNumber VARCHAR);
DELETE FROM Insert_PhoneNumbers;
INSERT INTO Insert_PhoneNumbers(PhoneNumber) VALUES ('Phone 1');
INSERT INTO Insert_PhoneNumbers(PhoneNumber) VALUES ('Phone 2');
INSERT INTO Persons(Name) VALUES(...);
UPDATE Insert_PhoneNumbers SET PersonForeignKey=last_insert_rowid();
INSERT INTO PhoneNumbers(PersonForeignKey, Number)
SELECT PersonForeignKey, PhoneNumber
FROM Insert_PhoneNumbers
创建和更新临时表似乎非常快(与对 Persons 或 PhoneNumbers 表的查询相比)并且插入速度不取决于数据库中已有的人数/电话号码,所以这就是我选择的解决方案.
creating and updating a temporary table seems to be very fast (compared to queries on the Persons or PhoneNumbers-tables) and the insert speed won't depend on the number of persons/phone numbers already in the database, so that's the solution I chose.
推荐答案
你可以在个人插入到临时表之后存储 last_insert_rowid() 或者这也可以:
You can store last_insert_rowid() after person insertion to temporary table or this might work too:
BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number)
VALUES(last_insert_rowid(), 'number 1');
INSERT INTO PhoneNumbers(PersonForeignKey, Number)
SELECT PersonForeignKey, 'number 2'
FROM PhoneNumbers where PhonePrimaryKey = last_insert_rowid();
INSERT INTO PhoneNumbers(PersonForeignKey, Number)
SELECT PersonForeignKey, 'number 3'
FROM PhoneNumbers where PhonePrimaryKey = last_insert_rowid();
END TRANSACTION;
这篇关于如何使用 last_insert_rowid 插入多行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!