跨多个数据库的Sqlite视图 [英] Sqlite view across multiple databases

问题描述

是否可以在连接了其他数据库的 Sqlite 数据库中创建 VIEW(非临时视图)?视图应该能够通过连接表访问所有数据库中的数据.

Is it possible to create a VIEW (not temporary view) in a Sqlite database that has other databases attached to it? The view should be able to access data from all databases via joined tables.

推荐答案

不行，视图必须是临时的，否则会报错:

No, the view must be temporary, otherwise an error will occur:

sqlite> create view view1 as select * from db2.foo union select * from main.foo;
Error: view view1 cannot reference objects in database db2
sqlite> create temp view view1 as select * from db2.foo union select * from main.foo;
sqlite> select * from view1;
...

这是有道理的，因为临时视图是自动创建的 temp 数据库的一部分，该数据库只存在于当前进程中.

This makes sense since a temporary view is part of the automatically created temp database which only exists for the current process.

您可以通过以下方式列出临时表和视图(都存储在自动创建的temp 数据库中):

You can list the temporary tables and views (all stored in the automatically created temp database) this way:

sqlite> .headers on
sqlite> select * from sqlite_temp_master;
type|name|tbl_name|rootpage|sql
view|view1|view1|0|CREATE VIEW view1 as select * from db2.foo union select * from main.foo

仅列出视图:

select * from sqlite_temp_master where type='view';

这篇关于跨多个数据库的Sqlite视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！