如何使用 CodeIgniter 框架从数组中插入多行?
我正在使用插入命令通过 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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
更多
发布评论
评论(14)
在 MySQL 中,将多行组合成一个 INSERT 语句比每行组合一个 INSERT 语句要快得多。
也就是说,听起来您可能会遇到 PHP 中的字符串处理问题,这实际上是一个算法问题,而不是语言问题。基本上,在处理大字符串时,您希望最大限度地减少不必要的复制。首先,这意味着您要避免串联。构建大型字符串(例如一次插入数百行)的最快且最有效的内存方法是利用 implode() 函数和数组赋值。
这种方法的优点是,您不必复制和重新复制到目前为止用每个串联组装的 SQL 语句;相反,PHP 在
implode()
语句中执行此操作一次。这是一个重大胜利。如果您有很多列需要放在一起,并且其中一个或多个列非常长,您还可以构建一个内部循环来执行相同的操作,并使用 implode() 来将 value 子句分配给外部循环大批。
Assembling one
INSERT
statement with multiple rows is much faster in MySQL than oneINSERT
statement per row.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.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.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 现在支持多重插入/批量插入。
Multiple insert/ batch insert is now supported by CodeIgniter.
您可以使用 mysqli_stmt 类准备插入一行的查询,然后迭代数据数组。类似于:
其中“idsb”是您要绑定的数据的类型(int、double、string、blob)。
You could prepare the query for inserting one row using the mysqli_stmt class, and then iterate over the array of data. Something like:
Where 'idsb' are the types of the data you're binding (int, double, string, blob).
PHP 5 中的 mysqli 是一个具有一些良好功能的对象,可以让您加快上面答案的插入时间:
插入多行时关闭自动提交会大大加快插入速度,因此将其关闭,然后按照上面提到的方式执行,或者只需创建一个字符串(sqlCombined),其中包含许多用分号分隔的插入语句,并且多查询可以很好地处理它们。
mysqli in PHP 5 is an object with some good functions that will allow you to speed up the insertion time for the answer above:
Turning off autocommit when inserting many rows greatly speeds up insertion, so turn it off, then execute as mentioned above, or just make a string (sqlCombined) which is many insert statements separated by semi-colons and multi-query will handle them fine.
您始终可以使用 mysql 的
LOAD DATA
:进行批量插入,而不是使用一堆
INSERT
语句。You could always use mysql's
LOAD DATA
:to do bulk inserts rather than using a bunch of
INSERT
statements.好吧,您不想执行 1000 个查询调用,但这样做很好:
根据您的数据源,填充数组可能就像打开文件并通过
file() 将内容转储到数组中一样简单
。Well, you don't want to execute 1000 query calls, but doing this is fine:
Depending on your data source, populating the array might be as easy as opening a file and dumping the contents into an array via
file()
.您可以在 codeigniter 中通过多种方式完成此操作,例如
You can do it with several ways in codeigniter e.g.
虽然现在回答这个问题已经太晚了。这是我对此的回答。
如果您使用的是 CodeIgniter,那么您可以使用 query_builder 类中定义的内置方法。
$this->db->insert_batch()
根据您提供的数据生成插入字符串,并运行查询。您可以将数组或对象传递给函数。以下是使用数组的示例:
第一个参数将包含表名称,第二个参数是值的关联数组。
您可以在此处找到有关 query_builder 的更多详细信息
Although it is too late to answer this question. Here are my answer on the same.
If you are using CodeIgniter then you can use inbuilt methods defined in query_builder class.
$this->db->insert_batch()
Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. Here is an example using an array:
The first parameter will contain the table name, the second is an associative array of values.
You can find more details about query_builder here
如果有人专门搜索 CodeIgniter 4:
In case anyone is searching specifically for CodeIgniter 4:
我创建了一个执行多行的类,其使用方式如下:
其中该类定义如下:
I have created a class that performs multi-line that is used as follows:
where the class is defined as follows:
使用codeigniter中的insert batch插入多行数据。
Use insert batch in codeigniter to insert multiple row of data.
我必须将超过 14000 行插入到表中,发现使用 Mysqli 准备语句逐行进行需要十多分钟,而使用相同 Mysqli 准备语句的字符串参数进行参数解包只用了不到 10 秒。我的数据非常重复,因为它是 id 的倍数和一个常量整数。
10 分钟代码:
10 秒代码:
I had to INSERT more than 14000 rows into a table and found that line for line with Mysqli prepared statements took more than ten minutes, while argument unpacking with string parameters for the same Mysqli prepared statements did it in less than 10 seconds. My data was very repetitive as it was multiples of id's and one constant integer.
10 minutes code:
10 second code:
我创建了这个简单的功能,你们可以轻松使用。您需要针对插入数据传递表名
($tbl)
、表字段($insertFieldsArr)
、数据数组($arr)< /代码>。
I have created this simple function which you guys can use easily. You will need to pass the table-name
($tbl)
, table-field($insertFieldsArr)
against your inserting data, data array($arr)
.