通过php数组将多行插入mysql [英] insert multiple rows via a php array into mysql

问题描述

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

I'm passing a large dataset into a MySQL table via PHP using insert commands and I'm wondering if its 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.

这篇关于通过php数组将多行插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！