使用 PHP 进行大量 SQLite 插入

发布于 2024-11-06 03:03:52 字数 703 浏览 0 评论 0原文

我有大约 14000 行逗号分隔值，我试图使用 PHP PDO 将它们插入到 sqlite 表中，如下所示：

<?php
// create a PDO object
$dbh = new PDO('sqlite:mydb.sdb');

$lines = file('/csv/file.txt'); // import lines as array
foreach ($lines as $line) {
    $line_array = (','$line); // create an array of comma-separated values in each line
    $values = '';
    foreach ($line_array as $l) {
        $values .= "'$l', ";
    }
    substr($values,-2,0); // get rid of the last comma and whitespace
    $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement
    $dbh->query($query); // run the query
}

?>

此查询需要很长时间，并且要不间断地运行它，我必须使用 PHP-CLI。有没有更好（更快）的方法来做到这一点？

原文

I have about 14000 rows of comma separated values that I am trying to insert into a sqlite table using PHP PDO, like so:

<?php
// create a PDO object
$dbh = new PDO('sqlite:mydb.sdb');

$lines = file('/csv/file.txt'); // import lines as array
foreach ($lines as $line) {
    $line_array = (','$line); // create an array of comma-separated values in each line
    $values = '';
    foreach ($line_array as $l) {
        $values .= "'$l', ";
    }
    substr($values,-2,0); // get rid of the last comma and whitespace
    $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement
    $dbh->query($query); // run the query
}

?>

This query takes a long time, and to run it without interuption, I would have to use PHP-CLI.
Is there a better (faster) way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情话墙 2024-11-13 03:03:52

通过将插入包含在单个事务中，您将看到良好的性能增益。如果不这样做，SQLite 会将每个插入视为自己的事务。

<?php
// create a PDO object
$dbh = new PDO('sqlite:mydb.sdb');

// Start transaction
$dbh->beginTransaction();
$lines = file('/csv/file.txt'); // import lines as array
foreach ($lines as $line) {
    $line_array = (','$line); // create an array of comma-separated values in each line
    $values = '';
    foreach ($line_array as $l) {
        $values .= "'$l', ";
    }
    substr($values,-2,0); // get rid of the last comma and whitespace
    $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement
    $dbh->query($query); // run the query
}
// commit transaction
$dbh->commit();

?>

You will see a good performance gain by wrapping your inserts in a single transaction. If you don't do this SQLite treats each insert as its own transaction.

<?php
// create a PDO object
$dbh = new PDO('sqlite:mydb.sdb');

// Start transaction
$dbh->beginTransaction();
$lines = file('/csv/file.txt'); // import lines as array
foreach ($lines as $line) {
    $line_array = (','$line); // create an array of comma-separated values in each line
    $values = '';
    foreach ($line_array as $l) {
        $values .= "'$l', ";
    }
    substr($values,-2,0); // get rid of the last comma and whitespace
    $query = "insert into sqlite_table values ($values)"; // plug the value into a query statement
    $dbh->query($query); // run the query
}
// commit transaction
$dbh->commit();

?>

回复收藏 0 原文

浅笑依然 2024-11-13 03:03:52

在循环之前启动事务并在循环之后提交事务
您的代码现在的工作方式是，它在每次插入时都会启动一个事务

回复收藏 0 原文

花期渐远 2024-11-13 03:03:52

如果您希望提高速度，请使用准备/获取，这样 SQL 引擎就不必每次都解析出文本字符串。

$name = $age = '';
$insert_stmt = $db->prepare("insert into table (name, age) values (:name, :age)");
$insert_stmt->bindValue(':name', $name);
$insert_stmt->bindValue(':age', $age);

// do your loop here, like fgetcsv
while (get the data) {
list($name, $age) = split(',', $string);
$insert_stmt->execute();
}

在循环外进行绑定是违反直觉的，但这就是该方法如此快的原因之一，您基本上是在说“使用这些变量中的数据执行此预编译查询”。因此它甚至不需要在内部移动数据。并且您希望避免重新解析查询，如果您使用类似“插入表（名称）值（'$name'）”之类的内容，这就是问题，每个查询都会将整个文本字符串发送到数据库以重新解析解析。

还有一件事可以加快速度——将整个循环包装在一个事务中，然后在循环完成时提交事务。

If you're looking for a bit more speed, use prepare/fetch, so the SQL engine doesn't have to parse out the text string each time.

$name = $age = '';
$insert_stmt = $db->prepare("insert into table (name, age) values (:name, :age)");
$insert_stmt->bindValue(':name', $name);
$insert_stmt->bindValue(':age', $age);

// do your loop here, like fgetcsv
while (get the data) {
list($name, $age) = split(',', $string);
$insert_stmt->execute();
}

It's counter-intuitive that you do the binding outside the loop, but this is one reason why this method is so fast, you're basically saying "Execute this pre-compiled query using data from these variables". So it doesn't even need to move the data around internally. And you want to avoid re-parsing the query, which is the problem if you use something like "insert into table (name) values ('$name')", every query sends the entire text string to the database to be re-parsed.

One more thing to speed it up -- wrap the whole loop in a transaction, then commit the transaction when the loop is finished.

回复收藏 0 原文

怪我入戏太深 2024-11-13 03:03:52

来自 SQLlite 常见问题解答：

交易速度受到磁盘驱动器速度的限制，因为（默认情况下）
SQLite实际上会等待数据
确实安全地存储在磁盘上
交易前的表面
完全的。这样的话，如果你突然
断电或者如果您的操作系统崩溃，您的
数据仍然安全。有关详细信息，请阅读
关于 SQLite 中的原子提交..
[...]
另一个选项是运行 PRAGMA synchronous=OFF。该命令将
导致 SQLite 不等待数据
到达磁盘表面，这将
使写操作看起来是
快得多。但如果你失去力量
交易的中间，你的
数据库文件可能会损坏。

我想说最后一段就是你所需要的。

编辑：对此不确定，但我相信使用 sqlite_unbuffered_query() 应该可以解决问题。