問題描述

我有一個包含信息的數組，看起來或多或少是這樣的:

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 上的這個:

行集基本上是一個集合對象，因此我將向該類添加方法以允許將行添加到集合中.所以你應該能夠做到這一點:

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.

這篇關于如何使用 Zend_Db 添加多于一行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！