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

问题描述

我的硬盘驱动器(file.db)上有一个带5个表的sqlite3数据库.我想将这些表中的3个复制到内存数据库(:memory:).

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:).

是否有使用PHP5的PDO格式的简单方法?

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

推荐答案

不是特定于pdo的解决方案，在您的情况下可能会或可能不够:

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

• 创建一个:memory:数据库
• 附加现有数据库文件
• 创建表...按SELECT * FROM ...
• 分离数据库文件

• create a :memory: database
• Attach the existing database file
• CREATE TABLE ... AS SELECT * FROM ...
• Detach the database file

一个例子
首先，将示例数据库存储在mydb.sq3

edit: an example
First an example database stored in mydb.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();
}

现在我们有一个:memory:数据库，并想传输表foo

Now we have a :memory: database and want to transfer the table 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');

完成.但是，让我们看一下sqlite_master表

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

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

此打印

CREATE TABLE bar(x INT,y,z)

INTEGER PRIMARY KEY ASC 声明丢失.也许就足够了....

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

这篇关于PHP/SQLite-将表从磁盘复制到内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！