如何使用CodeIgniter框架从数组中插入多行? [英] How to insert multiple rows from array using CodeIgniter framework?

问题描述

我正在使用插入命令通过PHP将大型数据集传递到MySQL表中，我想知道是否有可能通过查询来一次插入大约1000行，而不是将每个值附加到mile-的末尾，长字符串，然后执行它.我正在使用CodeIgniter框架，因此我也可以使用它的功能.

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if it's possible to insert approximately 1000 rows at a time via a query other than appending each value on the end of a mile-long string and then executing it. I am using the CodeIgniter framework so its functions are also available to me.

推荐答案

在MySQL中，将一个带有多行的 INSERT 语句组装起来比每行一个 INSERT 语句快得多.

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row.

也就是说，听起来您可能会遇到PHP中的字符串处理问题，这实际上是算法问题，而不是语言问题.基本上，在处理大字符串时，您希望最大程度地减少不必要的复制.首先，这意味着您要避免串联.构建大型字符串(例如一次插入数百行)的最快，最节省内存的方法是利用 implode()函数和数组分配.

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

这种方法的优点是，您无需复制和重新复制到目前为止已与每个串联组合的SQL语句；取而代之的是，PHP在 implode()语句中执行一次.这是大获胜.

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

如果您有很多列要组合在一起，并且一个或多个列很长，则还可以构建一个内部循环来执行相同的操作，并使用 implode()来分配values子句到外部数组.

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

这篇关于如何使用CodeIgniter框架从数组中插入多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！