使用PHP的大量SQLite插入 [英] Large number of SQLite inserts using PHP

问题描述

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

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
}

?>

此查询需要很长时间，并且要在不中断的情况下运行它，我将不得不使用PHP-CLI. 有没有更好(更快)的方法来做到这一点?

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?

推荐答案

通过在单个事务中包装插入内容，您将获得良好的性能提升.如果您不这样做，SQLite会将每个插入视为自己的事务.

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();

?>

这篇关于使用PHP的大量SQLite插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！