如何使用Zend_Db添加多个行? [英] How do I add more than one row with Zend_Db?

问题描述

我有一个数组，其中的信息或多或少看起来像这样:

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

不会同时添加两个条目.我究竟做错了什么?我必须使用Zend_ Db_Table吗?

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

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

当然可以

推荐答案

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

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

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

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)
}

---

前Zend Framework开发人员 Bill Karwin 写道

---

Bill Karwin, a former Zend Framework developer, wrote this on Nabble some time ago:

行集基本上是一个集合对象，因此我将向该类添加方法以允许将行添加到集合中.因此，您应该可以执行以下操作:

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

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

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.

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

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

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

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

这将执行与上述上一个示例相同的循环(末尾的save()除外)，创建一个新行的新行集，准备好进行save()d.

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.

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

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

1. 使用具有多个行的单个INSERT语句:

1. Use a single INSERT statement with multiple rows:

INSERT INTO t(col1，col2，col3)值(1、2、3)，(4、5、6)，(7、8、9);

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

准备INSERT语句并多次执行:

Prepare an INSERT statement and execute it multiple times:

将PREPARE INSERT INTO t(col1，col2，col3)值(?，?，?); 执行1、2、3 执行4，5，6 执行7、8、9

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

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

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.

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

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(例如，在mysql CLI中)，并且在单个INSERT语句中插入多行，则只能获取最后生成的id值，而不能获取所有插入的行的id值.这是SQL行为.对于任何语言或任何框架都是如此.

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.

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

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

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.

这篇关于如何使用Zend_Db添加多个行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！