如何在 SQLite 中合并多个数据库文件? [英] How to Merge Multiple Database files in SQLite?

问题描述

我有多个数据库文件，它们存在于多个位置，结构完全相似.我知道附加功能可用于将多个文件连接到一个数据库连接，但是，这将它们视为单独的数据库.我想做类似的事情:

I have multiple database files which exist in multiple locations with exactly similar structure. I understand the attach function can be used to connect multiple files to one database connection, however, this treats them as seperate databases. I want to do something like:

SELECT uid, name FROM ALL_DATABASES.Users;

还有，

SELECT uid, name FROM DB1.Users UNION SELECT uid, name FROM DB2.Users ;

不是有效答案，因为我需要合并任意数量的数据库文件.最后，数据库文件必须保持独立.有人知道如何做到这一点吗?

is NOT a valid answer because I have an arbitrary number of database files that I need to merge. Lastly, the database files, must stay seperate. anyone know how to accomplish this?

一个答案给了我一个想法:是否可以创建一个包含所有不同表的视图?是否可以查询所有数据库文件以及它们挂载"的数据库，然后在视图查询中使用它来创建主表"?

an answer gave me the idea: would it be possible to create a view which is a combination of all the different tables? Is it possible to query for all database files and which databases they 'mount' and then use that inside the view query to create the 'master table'?

推荐答案

由于 SQLite 对一次可以附加的数据库数量施加了限制，因此无法在单个查询中完成您想要的操作.

Because SQLite imposes a limit on the number of databases that can be attached at one time, there is no way to do what you want in a single query.

如果可以保证数量在 SQLite 的限制范围内(这违反了任意"的定义)，则没有什么可以阻止您使用正确的 UNION 集生成查询是时候执行它了.

If the number can be guaranteed to be within SQLite's limit (which violates the definition of "arbitrary"), there's nothing that prevents you from generating a query with the right set of UNIONs at the time you need to execute it.

要支持真正任意数量的表，您唯一真正的选择是在不相关的数据库中创建一个表并重复INSERT 来自每个候选者的行:

To support truly arbitrary numbers of tables, your only real option is to create a table in an unrelated database and repeatedly INSERT rows from each candidate:

ATTACH DATABASE '/path/to/candidate/database' AS candidate;
INSERT INTO some_table (uid, name) SELECT uid, name FROM candidate.User;
DETACH DATABASE candidate;

这篇关于如何在 SQLite 中合并多个数据库文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！