使用php将数据批量插入MySQL数据库 [英] Batch insertion of data to MySQL database using php

问题描述

我有成千上万的数据通过巨大的XML解析，可以使用PHP和MySQL插入数据库表.我的问题是将所有数据插入表中花费的时间太长.有没有一种方法可以将我的数据分成较小的组，以便按组进行插入过程?例如，如何设置一个脚本来处理100个数据?这是我的代码:

I have a thousands of data parsed from huge XML to be inserted into database table using PHP and MySQL. My Problem is it takes too long to insert all the data into table. Is there a way that my data are split into smaller group so that the process of insertion is by group? How can set up a script that will process the data by 100 for example? Here's my code:

foreach($itemList as $key => $item){
     $download_records  = new DownloadRecords();
    //check first if the content exists
    if(!$download_records->selectRecordsFromCondition("WHERE Guid=".$guid."")){
         /* do an insert here */
    } else {
         /*do an update */
    }

}

*注意:$ itemList大约有62,000，并且仍在增长.

*note: $itemList is around 62,000 and still growing.

推荐答案

使用for循环吗?

但是将数据加载到MySQL的最快选择是使用

But the quickest option to load data into MySQL is to use the LOAD DATA INFILE command, you can create the file to load via PHP and then feed it to MySQL via a different process (or as a final step in the original process).

如果无法使用文件，请使用以下语法:

If you cannot use a file, use the following syntax:

insert into table(col1, col2) VALUES (val1,val2), (val3,val4), (val5, val6)

所以您减少了要运行的句子总数.

so you reduce to total amount of sentences to run.

给出您的摘录，看来您可以从 INSERT ..中受益. MySQL的ON DUPLICATE KEY UPDATE 语法，使数据库能够完成工作并减少查询量.假设您的表具有主键或唯一索引.

Given your snippet, it seems you can benefit from the INSERT ... ON DUPLICATE KEY UPDATE syntax of MySQL, letting the database do the work and reducing the amount of queries. This assumes your table has a primary key or unique index.

要每隔100行访问数据库，您可以执行以下操作(请对其进行检查并修复为您的环境)

To hit the DB every 100 rows you can do something like (PLEASE REVIEW IT AND FIX IT TO YOUR ENVIRONMENT)

$insertOrUpdateStatement1 = "INSERT INTO table (col1, col2) VALUES ";
$insertOrUpdateStatement2 = "ON DUPLICATE KEY UPDATE ";
$counter = 0;
$queries = array();

foreach($itemList as $key => $item){
    $val1 = escape($item->col1); //escape is a function that will make 
                                 //the input safe from SQL injection. 
                                 //Depends on how are you accessing the DB

    $val2 = escape($item->col2);

    $queries[] = $insertOrUpdateStatement1. 
    "('$val1','$val2')".$insertOrUpdateStatement2.
    "col1 = '$val1', col2 = '$val2'";

    $counter++;

    if ($counter % 100 == 0) {
        executeQueries($queries);
        $queries = array();
        $counter = 0;
    }
}

然后executeQueries将获取数组并发送单个多重查询:

And executeQueries would grab the array and send a single multiple query:

function executeQueries($queries) {
   $data = "";
     foreach ($queries as $query) {
        $data.=$query.";\n";
    }
    executeQuery($data);
}

这篇关于使用php将数据批量插入MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！