使用PDO准备SQL语句 [英] Preparing SQL Statements with PDO

问题描述

我的代码如下：

// Connect to SQLite DB
DB('/path/to/sqlite.db');

DB('BEGIN TRANSACTION;');

// These loops are just examples.
for ($i = 1; $i <= 10000; $i++)
{
    for ($j = 1; $j <= 100; $j++)
    {
        DB('INSERT INTO "test" ("id", "name") VALUES (?, ?);', $i, 'Testing ' . $j);
    }
}

DB('END TRANSACTION;');

这里是DB（）函数：

function DB($query)
{
    static $db = null;

    if (is_file($query) === true)
    {
        $db = new PDO('sqlite:' . $query, null, null, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
    }

    else if (is_a($db, 'PDO') === true)
    {
        $result = $db->prepare($query);

        if (is_a($result, 'PDOStatement') === true)
        {
            if ($result->execute(array_slice(func_get_args(), 1)) === true)
            {
                if (stripos($query, 'INSERT') === 0)
                {
                    return $db->lastInsertId();
                }

                if (stripos($query, 'SELECT') === 0)
                {
                    return $result->fetchAll(PDO::FETCH_ASSOC);
                }

                return $result->rowCount();
            }
        }

        return false;
    }

    return true;
}

问题是内部循环中的DB很久就要完成了，我想这样做可以加快执行速度：

The problem is that the DB() call inside the inner loop is taking to long to complete, I was thinking that doing something like this could speed up considerably the execution:

DB('BEGIN TRANSACTION;');

for ($i = 1; $i <= 10000; $i++)
{
    $queries = array();

    for ($j = 1; $j <= 100; $j++)
    {
        $queries[] = 'INSERT INTO "test" ("id", "name") VALUES (?, ?);' /*, $i, 'Testing ' . $j;*/
    }

    DB(implode("\n", $queries));
}

DB('END TRANSACTION;');

问题是我不知道如何准备

The problem is I don't know how I could prepare (replace and escape) all the question marks with the respective variables, is there any way I can accomplish this?

推荐答案

如果你在表中插入大量数据，尝试在一个查询中插入数据。

If you are inserting lots of data into a table, try inserting the data in one query.

$query = 'INSERT INTO "test" ("id", "name") VALUES ';
$data = array();
for ($i = 1; $i <= 10000; $i++) {
  for ($j = 1; $j <= 100; $j++) {
    $query .= '(?,?),';
    $data[] = $i;
    $data[] = 'Testing '.$j;
  }
}

$query = substr($query, 0, -1);
DB($query, $data);

这将消除单插入查询的开销。
然而，查询lenght有一个限制，如果你对查询lenght有麻烦，尝试在for循环中更频繁地调用DB（）调用。

This should eliminate the overhead with single insert queries. There's a limit on the query lenght though, if you have troubles with query lenght, try issueing DB() calls more often in for loop.

这篇关于使用PDO准备SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！