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

问题描述

我正在使用插入命令通过 PHP 将一个大型数据集传递到 MySQL 表中，我想知道是否可以通过查询一次插入大约 1000 行，而不是将每个值附加到一英里的末尾-长字符串，然后执行它.我正在使用 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屋！