PHP mysqli_multi_query大插入问题 [英] PHP mysqli_multi_query large insert issue

问题描述

我尝试了多种方法来尝试从文本文件中插入大量的数据(在服务器上大约2秒钟内，将200,000行文本被解析到数组中，所以我知道这不是问题).我正在使用的mysqli_multi_query以5,000个为单位发送查询(由于某些原因，如果我想再使用一次multi_query，它拒绝做更大的事情)或者如果我将文本文件拆分为50,000个行，则可以全部插入在2秒内一次.如果我这样做了，那么它将拒绝运行另一个查询(我确实读过，我需要做一会儿(mysqli_more_results($ db)&& mysqli_next_result($ db))；要清除结果，我确实需要5,000一口气

I've tried many ways in an attempt to insert a large amount of data parsed from a text file (200,000 lines of text gets parsed into the array in about 2 seconds on my server so I know that part isn't the issue). The mysqli_multi_query I'm using sends the queries in blocks of 5,000 (for some reason it refuses to do anything larger if I want to use multi_query more then once) OR if I split the text file down to 50,000ish rows I can insert them all at once in 2 seconds. If I do that then it refuses to run another query (I did read I need to do while (mysqli_more_results($db) && mysqli_next_result($db)); to clear the results out and I do have that for my 5,000 split one)

50,000个插入操作在2秒内运行一次，5,000个插入操作(50,000行)需要40秒，我很想在foreach中创建一个新的mysqli连接(对于50,000个插入操作)，但是它没有并非总是工作...

The 50,000 insert one runs in 2 seconds, the 5,000 insert one (of 50,000 rows) takes 40 seconds, I'm kind of tempted to just create a new mysqli connection in the foreach (for 50,000 inserts), but it doesn't always work...

我想了解一些有关如何使我的函数运行更快的提示，因为当我正在读取的文件可能包含数十万行或几百万行时，要花40秒的时间才能永久插入所有50,000条信息

I'd like some tips on how to make my function run faster since 40 seconds to insert all 50,000 takes forever when the file I'm reading from can contain sever hundred thousand possibly millions of lines

function log2db(){
$db = mysqli_connect(SQLHOST, SQLUSER, SQLPASS, SQLDB);
if($db){
    $qar = logparse("world2.chat", loglen());
    $query = "";
    $i = 0;
    foreach($qar['data'] as $q){
        $i++;
        $uid = $q['uid'];
        $type = $q['type'];
        $chldst = $q['chldst'];
        $time = $q['date'];
        $msg = $q['msg'];
        $query .= "insert into `messages` (`uid`, `type`, `chldst`, `time`, `message`) values ('$uid', '$type', '$chldst', '$time', '$msg');\n";
        if(!($i % 5000)){
            $qquery[] = $query;
            $query = "";
        }
    }
    $qquery[] = $query;
    foreach($qquery as $qq){
        $q = mysqli_multi_query($db, $qq);
        if($q){
            while (mysqli_more_results($db) && mysqli_next_result($db));
        } else {
            mysqli_error($db);
            return false;
        }
    }
    if($qar !== null){
        loglen($qar['filelen']);
    }
    return true;
}
return false;

}

我想出了什么(总是可行，但就像我说的那样很慢)，关于如何改善它的任何提示?

Is what I've come up with (always works but like I said is slow), any tips on how I can improve it?

推荐答案

您是否意识到INSERT INTO查询可以在单个查询中插入多行?

You do realize the INSERT INTO query can insert multiple rows in a single query?

$parts  = array();

foreach($qar['data'] as $q){

    $uid    = $q['uid'];
    $type   = $q['type'];
    $chldst = $q['chldst'];
    $time   = $q['date'];
    $msg    = $q['msg'];

    $parts[] = "('$uid', '$type', '$chldst', '$time', '$msg')";
 }

$query  = "INSERT INTO `messages` (`uid`, `type`, `chldst`, `time`, `message`)";
$query .= "VALUES ".implode(', ', $parts);

mysqli_query($db, $query)

这篇关于PHP mysqli_multi_query大插入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！