如何快速从一个数据库复制表到另一个 [英] How to copy table fast from one database to another

问题描述

我有一个数据库，一个表和另一个数据库。我需要复制这个表中从第一个数据库（简单表）到秒。 （在第二数据库应该FTS3表）。所以，我可以打开两个数据库，创建第二个新的数据库FTS3，并通过从第一个全部数据复制到第二个 - >插入查询。但是，有没有其他的方法来更快，更好地做到这一点？

I have one database with one table and another database. I need to copy this one table from the first database (with simple table) to the second. (In second database it should be fts3 table). So, I can open both databases, create new fts3 in the second db and copy all data from the first to the second via select -> insert queries. But is there any other ways to do it faster and better?

推荐答案

据我所知您所描述的方法（即 INSERT INTO db2.tbl SELECT * FROM db1.tbl ）通常应该是最高效的。

As far as I know the methodology you described (i.e. INSERT INTO db2.tbl SELECT * FROM db1.tbl) should generally be the most efficient.

你可以做的就是调整源码更快地做到这一点。我想到的第一件事是禁用日志（通常是危险的，但应该在您的方案是可以接受的，你仍然有数据的原件）：

What you can do is tweak sqlite to do it faster. The first thing that comes to mind is to disable journaling (normally dangerous, but should be acceptable in your scenario as you still have an original of the data):

pragma PRAGMA journal_mode=OFF:

您也可以关闭 同步编译 （也很危险）：

You can also turn off the synchronous pragma (also dangerous):

PRAGMA synchronous=OFF;

有一个或两个编译指示，你可以与发挥能有所作为，但我认为这两个我提到将有最大的影响。

There are one or two more pragmas you can play with that could make a difference, but I think the two I mention will have the biggest impact.

请务必复制后恢复这些编译指示到其原始值。

Be sure to restore those pragmas to their original values after the copy.

这篇关于如何快速从一个数据库复制表到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！