假设我有一个包含两个表的数据库Persons
and PhoneNumbers
,其中PhoneNumbers
表有一个外键Persons
。如果我想在一次事务中插入一个拥有电话号码的人,我可以编写如下查询:
BEGIN TRANSACTION;
INSERT INTO Persons(Name) VALUES(...);
INSERT INTO PhoneNumbers(PersonForeignKey, Number) VALUES(last_insert_rowid(), ...);
END TRANSACTION;
但是如果我想插入一个有多个电话号码的人怎么办?显而易见的方法:
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 而不是人员。
有没有办法使用基本 SQL(特别是 SQLite)来做到这一点?
结论
显然,没有直接的方法可以做到这一点。我根据 @Michal Powaga 的建议和基于临时表的其他一些想法做了一些基准测试,最快的方法似乎是这样的:
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 表的查询相比),并且插入速度不取决于数据库中已有的人员/电话号码的数量,所以这就是我选择的解决方案。