我正在尝试将数据从csv文件加载到更规范的数据库结构中。
简化的例子，考虑一个人与他们的地址（号码，街道，城市）的列表。多个人可能住在一个地址，但每个人只有一个家。我有下面的临时表包含从CSV文件加载的数据：
CREATE TABLE temp (name TEXT, houseNo INTEGER, street TEXT, city TEXT);
INSERT INTO temp VALUES('Mr Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Prime Minister',10,'Downing Street','London');
INSERT INTO temp VALUES('Mrs Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Baby Benn',52,'Festive Road','London');
INSERT INTO temp VALUES('Reggie Perrin',12,'Coleridge Close','London');
INSERT INTO temp VALUES('Larry The Cat',10,'Downing Street','London');我要加载这些表：
CREATE TABLE people(name TEXT PRIMARY KEY);
CREATE TABLE addresses(id INTEGER PRIMARY KEY, houseNo INTEGER, street TEXT, city TEXT,UNIQUE(houseNo,street,city) );
CREATE TABLE homes(id INTEGER PRIMARY KEY, name TEXT NOT NULL, addressId INTEGER NOT NULL,FOREIGN KEY (name) REFERENCES people(name), FOREIGN KEY (addressId) REFERENCES addresses(id));因此，对于“temp”中的每一行，我希望：
1.将name插入到people表中
1.将地址字段向上插入addresses，取回id（无论是新的还是现有的），以及
1.使用此id在homes表中插入一行
我可以这样写：
INSERT INTO people SELECT name FROM temp;
INSERT INTO addresses(houseNo,street,city) SELECT houseNo,street,city FROM temp WHERE TRUE ON CONFLICT DO NOTHING;
INSERT INTO homes(name,addressId) SELECT t.name,a.id FROM addresses a, temp t WHERE a.houseNo = t.houseNo AND a.street = t.street AND a.city=t.city;
SELECT * FROM homes它的作用是：
id name addressId
-- ---- ---------
1 Mr Benn 1
2 Prime Minister 2
3 Mrs Benn 1
4 Baby Benn 1
5 Reggie Perrin 3
6 Larry The Cat 2但是使用这个，* 我运行了临时表3次 *（我的实际数据集更大，并且有更多的列）。这看起来效率很低，因为我已经从步骤2中知道了步骤3中使用的id。
在Sqlite中是否有更高效的方法来实现我的目标？
1条答案
按热度按时间jhiyze9q1#
溶液1：使用VIEW和INSTEAD OF触发器
将temp定义为VIEW，并创建TRIGGER将对TEMP的INSERT重定向为对其他表的INSERT
然后，您可以继续从CSV加载临时表。
PRO：在导入数据时，您的表将在一次传递中填充，甚至不需要存储临时表。
缺点：对于每一行，都需要在地址表中搜索地址的ID，因为在纯SQL中无法使用刚刚插入（或忽略）的值。
溶液2：使用“快速”温度表
虽然原始的解决方案对于大型输入文件来说可能会很慢，但可以通过利用临时模式从Sqlite中获得最大的收益：
然后继续从CSV加载数据，然后用原始查询填充其他表。
使用这种语法，TEMPORARY表temp将在内存中创建（并在连接关闭时删除），因此即使您从CSV加载它并运行它3次，它仍然比普通表快。
PRO：最简单的解决方案，可轻松适应许多需要加载外部数据并在以最终形式存储之前对其进行操作的场景。
缺点：根据数据大小和操作类型，这可能比解决方案1快或慢。
当然，在这两种解决方案中，将所有数据加载 Package 在单个事务中会大大加快速度。