php 如何使用 Zend_Db 添加多于一行？

How do I add more than one row with Zend_Db?

提问by Thomaschaaf

I have an array with information which looks more or less like this:

我有一个包含信息的数组，它看起来或多或少是这样的：

$data[] = array('content'=>'asd');
$data[] = array('content'=>'asdf');

And I want to add both entries into the Database.

我想将这两个条目都添加到数据库中。

$db->insert('table', $data);

does not add both entries. What am I doing wrong? Do I have to use Zend_ Db_Table?

不添加两个条目。我究竟做错了什么？我必须使用 Zend_ Db_Table 吗？

$data = array('content'=>'asdf');
$db->insert('table', $data);

works of course

当然有效

回答by markus

I don't think Zend_Db supports insertion of multiple rows.

我不认为 Zend_Db 支持插入多行。

But if you just have two rows or a little more you can just use a loop.

但是，如果您只有两行或更多行，则可以使用循环。

foreach ($data as $row)
{
    $db->insert('table', $row)
}

---

---

Bill Karwin前 Zend 框架开发人员Bill Karwin写道this on Nabble some time ago前段时间在 Nabble 上：

Rowsets are basically a collection object, so I would add methods to that class to allow rows to be added to the set. So you should be able to do this:

行集基本上是一个集合对象，因此我会向该类添加方法以允许将行添加到集合中。所以你应该能够做到这一点：

// creates a rowset collection with zero rows
$rowset = $table->createRowset();

// creates one row with unset values 
$row = $table->createRow();

// adds one row to the rowset 
$rowset->addRow($row); 

// iterates over the set of rows, calling save() on each row
$rowset->save(); 

It makes no sense to pass an integer to createRowset() to create N empty rows. You would just have to iterate through them to populate them with values anyway. So you might as well write a loop to create and populate individual rows with application data, and then add them to the collection.

将整数传递给 createRowset() 以创建 N 个空行是没有意义的。无论如何，您只需要遍历它们即可用值填充它们。因此，您不妨编写一个循环来使用应用程序数据创建和填充各个行，然后将它们添加到集合中。

$rowset = $table->createRowset();
foreach ($appData as $tuple) 
{
    $row = $table->createRow($tuple);
    $rowset->addRow($row);
}
$rowset->save();

It does make sense to allow an array of arrays to be passed to createRowset(), since this would be consistent with the usage of passing a tuple to createRow().

允许将数组数组传递给 createRowset() 确实有意义，因为这与将元组传递给 createRow() 的用法一致。

$rowset = $table->createRowset($appData); // pass array of tuples

This would perform the same loop as the previous example above (except for the save() at the end), creating a new rowset of new rows, ready to be save()d.

这将执行与上一个示例相同的循环（最后的 save() 除外），创建新行的新行集，准备进行 save()d。

There are two ways in SQL to improve the efficiency of inserting data:

SQL中有两种方法可以提高插入数据的效率：

1. Use a single INSERT statement with multiple rows:

   INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);

2. Prepare an INSERT statement and execute it multiple times:

   PREPARE INSERT INTO t (col1, col2, col3) VALUES (?, ?, ?); EXECUTE 1, 2, 3 EXECUTE 4, 5, 6 EXECUTE 7, 8, 9

1. 使用包含多行的单个 INSERT 语句：

   INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);

2. 准备一个 INSERT 语句并执行多次：

   准备插入 t (col1, col2, col3) VALUES (?, ?, ?); 执行 1、2、3 执行 4、5、6 执行 7、8、9

However, supporting either of these improvements would add complexity to the Row and Rowset classes. This is due to the internal way the current Zend_Db_Table_Row class differentiates between a row that needs to be INSERTed or UPDATEd when you call save(). This distinction is encapsulated by the Row object, so the Rowset doesn't know if the individual rows are new rows or modified copies of existing rows. Therefore for the Rowset class to offer a multi-row save() method that uses more efficient SQL, the management of dirty data would have to be totally refactored. The easier solution is for the Rowset to iterate over its rows, calling save() on each one. This is better for OO encapsulation, though it doesn't help optimize SQL for inserting a rowset.

但是，支持这些改进中的任何一个都会增加 Row 和 Rowset 类的复杂性。这是由于当前 Zend_Db_Table_Row 类在调用 save() 时区分需要插入或更新的行的内部方式。这种区别由 Row 对象封装，因此 Rowset 不知道各个行是新行还是现有行的修改副本。因此，为了让 Rowset 类提供使用更高效 SQL 的多行 save() 方法，必须完全重构脏数据的管理。更简单的解决方案是让 Rowset 迭代其行，对每一行调用 save()。这对于 OO 封装更好，尽管它无助于优化用于插入行集的 SQL。

In any case, it's really rare to bulk-load many rows of data in a typical web request, when there's the greatest need for efficient SQL. The difference in efficiency for a small number of rows is small, so it would be a noticeable improvement only if you're bulk-loading a huge number of rows. If that's the case, you shouldn't be using INSERT anyway, you should be using MySQL's LOAD DATA statement, or equivalent feature if you use another RDBMS brand. INSERT is not usually the most efficient choice for loading lots of data.

在任何情况下，当最需要高效的 SQL 时，在典型的 Web 请求中批量加载多行数据真的很少见。少量行的效率差异很小，因此只有当您批量加载大量行时，才会有明显的改进。如果是这种情况，您无论如何都不应该使用 INSERT，您应该使用 MySQL 的 LOAD DATA 语句，或者如果您使用另一个 RDBMS 品牌的等效功能。INSERT 通常不是加载大量数据的最有效选择。

Regarding returning auto-generated keys, I wouldn't bother. Notice that if you use plain SQL (in the mysql CLI for example), and you insert multiple rows in a single INSERT statement, you can only get the last generated id value, not the id values for all rows inserted. This is SQL behavior; it's true for any language or any framework.

关于返回自动生成的密钥，我不会打扰。请注意，如果您使用普通 SQL（例如在 mysql CLI 中），并且在单个 INSERT 语句中插入多行，则只能获取最后生成的 id 值，而不是所有插入行的 id 值。这是 SQL 行为；对于任何语言或任何框架都是如此。

INSERT INTO t (col1, col2, col3) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
SELECT LAST_INSERT_ID(); -- returns only the id for the third tuple

If you do need the id for each row, you should write a loop and insert the rows one at a time, retrieving the generated id after each row inserted.

如果确实需要每一行的 id，则应该编写一个循环并一次插入一行，在插入每一行后检索生成的 id。

回答by Bill Karwin

You can execute any SQL syntax you want -- including multi-row INSERTstatements -- via the Zend_Db_Adapter_Abstract::query()method.

您可以INSERT通过该Zend_Db_Adapter_Abstract::query()方法执行您想要的任何 SQL 语法——包括多行语句。

But methods of the Zend_Db_Tableand Zend_Db_Table_Rowsetclasses don't have any support for inserting multiple rows in one go.

但是Zend_Db_Table和Zend_Db_Table_Rowset类的方法不支持一次性插入多行。

回答by risnandar

to insert multiple rows you can use Zend_Db

要插入多行，您可以使用 Zend_Db

$stmt = $table->getAdapter()->prepare('INSERT INTO tablename (col1, col2, col3) VALUES (?, ?, ?), (?, ?, ?)');

$stmt->execute( array($value1, $value2, $value3, $value4, $value5, $value6) );

(from Bill Karwin)

（来自比尔·卡文）

in your case we can change that to this code:

在您的情况下，我们可以将其更改为以下代码：

$data[] = array('content'=>'asd');
$data[] = array('content'=>'asdf');

$stmt = $table->getAdapter()->prepare('INSERT INTO table (col1) VALUES (?), (?)');
$stmt->execute( $data );

to generate these '(?), (?)' dynamically, incase the data is dynamic, you can try using this snippet:

要动态生成这些 '(?), (?)'，如果数据是动态的，您可以尝试使用以下代码段：

$values = implode(',',array_fill(0,count($data),'(?)'));

hope this helps

希望这可以帮助

regards, Riki Risnandar

问候， Riki Risnandar

回答by ovnia

here is my solution:

这是我的解决方案：

public function saveRows($array) {
        $vAmount    = count($array);
        $values     = array();
        $columns    = array();

        foreach ($array as $colval) {
            foreach ($colval as $column=>$value) {
                array_push($values,$value);
                !in_array($column,$columns) ? array_push($columns,$column) : null;
            }
        }

        $cAmount    = count($columns);
        $values     = array_chunk($values, $cAmount);
        $iValues    = '';
        $iColumns   = implode("`, `", $columns);

        for($i=0; $i<$vAmount;$i++)
            $iValues.="('".implode("', '", $values[$i])."')".(($i+1)!=$vAmount ? ',' : null);

        $data="INSERT INTO `".$this->_name."` (`".$iColumns."`) VALUES ".$iValues;
        die($data);
        $this->query($data);
    }

回答by Sergei Khaletskiy

If you do use ZF2 then solution might be like this:

如果您确实使用 ZF2，那么解决方案可能是这样的：

$insert = $this->getSql()->insert();
foreach ($values as $value) {
    $relation = array(
        'column_one' => $value,
        'column_two' => $value
    );
    $insert->values($relation, Insert::VALUES_MERGE);
}

$insertRes = $this->executeInsert($insert);

回答by Knase

It's work.

这是工作。

 $query = 'INSERT INTO ' . $db->quoteIdentifier('table') . ' (`col1`, `col2`) VALUES ';
    $queryVals = array();
    foreach ($data as $row) {
        foreach($row as &$col) {
            $col = $db->quote($col);
        }
        $queryVals[] = '(' . implode(',', $row) . ')';
    }
    $stmt = $db->query($query . implode(',', $queryVals));

how to build multiple insert query in zend framework

如何在zend框架中构建多个插入查询