PHP / SQLite -将表从磁盘复制到内存

I have a sqlite3 database on my harddrive (file.db) with 5 tables. I'd like to copy 3 of these tables to an in-memory database (:memory:).

我的harddrive (file.db)上有一个sqlite3数据库，包含5个表。我想将这些表中的3个复制到内存中的数据库(:memory:)。

Is there a simple way to do so using PHP5's PDO format?

使用PHP5的PDO格式是否有一种简单的方法来实现这一点?

5 个解决方案

#1

Not a pdo-specific solution that may or may not be sufficient in your case:

在你的案例中，没有一个特定于pdo的解决方案可能或可能不充分:

create a :memory: database
创建一个内存:数据库
Attach the existing database file
附加现有的数据库文件
CREATE TABLE ... AS SELECT * FROM ...
创建表……从…中选择*
Detach the database file
分离数据库文件

edit: an example
First an example database stored in mydb.sq3

编辑:首先是一个示例数据库，存储在db.sq3中

<?php
$pdo = new PDO('sqlite:mydb.sq3');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE foo(x INTEGER PRIMARY KEY ASC, y, z)');

$stmt = $pdo->prepare("INSERT INTO foo (x,y,z) VALUES (:x,:y,:z)");
$stmt->bindParam(':x', $x);
$stmt->bindParam(':y', $y);
$stmt->bindParam(':z', $z);

for($x=0; $x<100; $x++) {
    $y = $x*2;
    $z = $x*2+1;
    $stmt->execute();
}

Now we have a :memory: database and want to transfer the table foo

现在我们有了:memory: database，并希望传输表foo

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('ATTACH "mydb.sq3" as filedb');
$pdo->exec('CREATE TABLE bar AS SELECT * FROM filedb.foo');
$pdo->exec('DETACH filedb');

Done. But let's take a look at the sqlite_master table

完成了。让我们看一下sqlite_master表。

foreach($pdo->query('SELECT sql FROM sqlite_master') as $row) {
    echo $row['sql'];
}

this prints

这个打印

CREATE TABLE bar(x INT,y,z)

The INTEGER PRIMARY KEY ASC declaration is lost. Might be sufficient though....

该整数主键ASC声明丢失。可能是足够虽然....

#2

If that's what you need to do, then VolkerK's answer is the one I'd provide, but I feel that I have to point out that you're going to read the contents of those tables into memory each time you run that code (every time that page loads?), so it might be better just to query the data files from disk.

如果这就是你需要做的,那么VolkerK的回答是我会提供,但是我觉得我必须指出你将这些表的内容读入内存每次运行该代码(每次页面加载?),所以它可能是更好的查询数据文件从磁盘。

#3

Note that one could always use some kind of shared memory mechanism (e.g. APC, memcache, etc..) to keep sqlite's in-memory databases persistent across connections.

请注意，可以始终使用某种共享内存机制(例如APC、memcache等)来保持sqlite的内存中数据库在连接之间的持久性。

#4

You can dump the database at the end of the connection, save it as apc variable and then load and run again from apc at the beginning of the next execution.

您可以在连接的末尾转储数据库，将其保存为apc变量，然后在下一次执行开始时从apc加载并再次运行。

#5

Using the method outlined by VolkerK roughly doubled the performance of my code when using a ~150Mb sqlite database.

使用VolkerK概述的方法，当使用大约150Mb的sqlite数据库时，代码的性能大约提高了一倍。

Loading the database into an in-memory sqlite db didn't require any other changes to my existing code.

将数据库装入内存中的sqlite db不需要对现有代码进行任何其他更改。

My use case was batch processing data so I didn't have to deal with the problems Wez Furlong highlights.

我的用例是批处理数据，所以我不必处理Wez Furlong高亮显示的问题。

Reading the data into memory was surprisingly fast. The whole 150Mb was loaded into memory from SSD in less than two seconds. It seemed too good to be true so I checked and rechecked the data.

将数据读入内存的速度快得惊人。在不到两秒的时间内，整个150Mb从SSD加载到内存中。看起来太好了，不可能是真的，所以我又检查了一下数据。

Many thanks to VolkerK for a magic solution!

非常感谢VolkerK提供一个神奇的解决方案!

I also found that when I indexed the in-memory tables my queries executed three times faster

我还发现，当我索引内存表时，我的查询执行速度要*倍

//adapting VolkerK's example...
//loop through tables from the local sqlite db 
$tables = array('companies', 'directors',  'previous_names');
foreach($tables as $table){
    //load each table into memory
    $pdo->exec("CREATE TABLE $table AS SELECT * FROM filedb.$table");

    //index each table on the relevant columns
    $pdo->exec("CREATE INDEX IF NOT EXISTS `".$table."_company_number` 
                ON $table (`company_number`);");
}

#1