如何使用 Zend_Db 添加多于一行?

2021-12-29 00:00:00 php mysql zend-framework zend-db-table 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)
}

<小时>Bill Karwin，前 Zend 框架开发人员，撰写了 不久前在 Nabble 上的这个:

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() 确实有意义，因为这与将元组传递给 createRow() 的用法一致.

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:

插入 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:

准备插入 t (col1, col2, col3) VALUES (?, ?, ?);执行 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.

相关文章