使用关系型数据库时,经常需要在不同的表(Table)之间联合操作。对于同一个数据库中的表,容易实现,譬如使用JOIN。如果表处于不同的数据库(.db文件),如何实现?
SQLite以“连接”为使用数据库的基本方式,一个连接对应到一个db文件。对于连接到数据库A,同时需要使用数据库B中的数据的情况,SQLite提供了一种将外部数据库附加到当前数据库连接的机制——Attach DB。
Attach DB的使用方法依然是SQL语句,语法为:
附加:
attach [database] filename as database_name;
取消附加:
detach [database] database_name;
下面使用具体的db试用一下Attach DB,探讨一些细节。
基本用法
两个测试数据库test.db,test1.db
~/test_sqlite$ sqlite3 test.db
SQLite version -- ::
Enter ".help" for usage hints.
sqlite>
sqlite> .tables
test_table test_table2
sqlite>
sqlite> .schema
CREATE TABLE test_table (id integer primary key, name text, description text);
CREATE TABLE test_table2 (a TEXT, b TEXT, c TEXT, d TEXT);
CREATE INDEX test_table2_index on test_table2 (a,b,c,d);
sqlite>
sqlite> .headers on
sqlite>
sqlite> select * from test_table;
id|name|description
|name0|des0
|name1|des1
|name2|des2
|name3|des3
~/test_sqlite$ sqlite3 test1.db
SQLite version -- ::
Enter ".help" for usage hints.
sqlite>
sqlite> .tables
test_table test_table2 test_table3
sqlite>
sqlite> .schema
CREATE TABLE test_table (id integer primary key, name text, description text);
CREATE TABLE test_table2 (id integer primary key, name text, description text);
CREATE TABLE test_table3 (id integer primary key, name text, description text);
sqlite>
sqlite> .headers on
sqlite>
sqlite> select * from test_table;
id|name|description
|text0|description0
连接到test.db,附加test1.db:
~/test_sqlite$ sqlite3 test.db
SQLite version -- ::
Enter ".help" for usage hints.
sqlite>
sqlite> .tables
test_table test_table2
sqlite>
sqlite> attach 'test1.db' as test1;
sqlite>
sqlite> .tables
test1.test_table test1.test_table3 test_table2
test1.test_table2 test_table
从.tables命令的结果,已经能够看到test1.db中的表了,通过select也能够得到test1.db中的数据:
sqlite> select * from test1.test_table;
id|name|description
|text0|description0
这里面需要注意几个点:
(1)attach语句的第一个参数必须在单引号之内,且必须是相对于当前SQLite终端的完整路径文件名。
(2)attach语句的第二个参数可以用单引号,也可以不用。
(3)对于attach进来的数据库,是一个as后面的参数(附加数据库别名)作为ID,也就是说:如果想附加多个数据库,需要指定不同的别名,别名不可重复;同一个数据库可以多次附加,只要保证使用不同的别名即可。例如:
sqlite> attach 'test1.db' as 'test2';
sqlite>
sqlite> .tables
test1.test_table test1.test_table3 test2.test_table2 test_table
test1.test_table2 test2.test_table test2.test_table3 test_table2
数据同步
如果被附加的db在其他连接中有数据更新,能够同步吗?实验一下,连接一打开test.db并附加test1.db,连接二打开test1.db并做更新操作。
连接一:
~/test_sqlite$ sqlite3 test.db
SQLite version -- ::
Enter ".help" for usage hints.
sqlite>
sqlite> attach 'test1.db' as test1;
sqlite>
sqlite> .tables
test1.test_table test1.test_table3 test_table2
test1.test_table2 test_table
sqlite>
sqlite> select * from test1.test_table;
|text0|description0
sqlite>
连接二:
插入新的数据到test_table,并drop test_table2
~/test_sqlite$ sqlite3 test1.db
SQLite version -- ::
Enter ".help" for usage hints.
sqlite>
sqlite> .headers on
sqlite>
sqlite> .tables
test_table test_table2 test_table3
sqlite>
sqlite> select * from test_table;
id|name|description
|text0|description0
sqlite>
sqlite> .schema
CREATE TABLE test_table (id integer primary key, name text, description text);
CREATE TABLE test_table2 (id integer primary key, name text, description text);
CREATE TABLE test_table3 (id integer primary key, name text, description text);
sqlite>
sqlite> insert into test_table values (,'text1','description1');
sqlite>
sqlite> select * from test_table;
id|name|description
|text0|description0
|text1|description1
sqlite>
sqlite> drop table test_table2;
sqlite>
sqlite> .tables
test_table test_table3
sqlite>
现在,回到连接一,看一下数据的情况:
sqlite> .tables
test1.test_table test1.test_table3 test_table test_table2
sqlite>
sqlite> .headers on
sqlite>
sqlite> select * from test1.test_table;
id|name|description
|text0|description0
|text1|description1
看到连接二中的数据更新已经完全同步到连接一中。
反过来,在连接一中,对于附加进来的test1.db做更新操作:
sqlite> drop table test1.test_table3;
sqlite>
sqlite> .tables
test1.test_table test_table test_table2
sqlite>
sqlite> insert into test1.test_table values (,'text2','description2');
sqlite>
sqlite> select * from test1.test_table;
id|name|description
|text0|description0
|text1|description1
|text2|description2
回头在连接二中查看数据:
sqlite> .tables
test_table
sqlite>
sqlite> select * from test_table;
id|name|description
|text0|description0
|text1|description1
|text2|description2
看到同样的结果,连接一中对于附加数据库的数据更新也同步回了原数据库的连接(连接二)。
所以,SQLite的Attach DB机制是一种非常宽松的机制,对于附加数据库,可以同步和被同步。这种机制很强大和灵活,但也增加了风险性。