插入2万条数据到MySQL遇Request Entity Too Large错误求解决

Absolutely, breaking your insert into smaller batches (like 5000 records per batch) is the ideal fix here. This splits up the massive POST payload and SQL query into manageable chunks, avoiding the "Request Entity Too Large" error and staying within MySQL's max_allowed_packet limits. Plus, we'll upgrade your code to use prepared statements for better security and performance—direct SQL string concatenation carries injection risks even with mysql_prep.

Modified Code with Batch Processing #

Here's how to adjust your existing code to handle batches safely:

<?php
if(isset($_POST['exportBtn']) && isset($_POST['sflt'])){ 
    set_time_limit(0); // Set once outside the loop, no need to repeat
    $batchSize = 5000; // Adjust this number if needed
    $totalRecords = count($_POST['sflt']);
    // Split the main array into smaller batches, keep original keys to match other POST fields
    $batches = array_chunk($_POST['sflt'], $batchSize, true);

    $cby = $_SESSION['username'];
    $ct = date('Y-m-d H:i:s');

    // Prepare the INSERT statement once (reuse for every record in all batches)
    $stmt = mysqli_prepare($link, "INSERT INTO tab_mydbtrans(
        fltno, model, engine, loc, serviceType, nextSvr, usageHr, 
        partName, partNo, costUnit, qty, total, createdBy, created_at, mtype
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

    if(!$stmt) {
        die("Prepare failed: " . mysqli_error($link));
    }

    // Bind parameters to the statement (adjust type codes if your columns use integers/dates)
    mysqli_stmt_bind_param($stmt, "ssssssssssissss", 
        $eflt, $emodel, $eengine, $eloc, $estye, $ensvr, $eehd, 
        $epname, $epn, $ecu, $eqty, $ett, $cby, $ct, $mtyp2
    );

    foreach($batches as $batch) {
        mysqli_begin_transaction($link); // Start a transaction for the batch
        foreach($batch as $key => $value) { 
            $eflt = mysql_prep($_POST['sflt'][$key]); 
            $emodel = mysql_prep($_POST['smodel'][$key]); 
            $eengine = mysql_prep($_POST['sengine'][$key]); 
            $eloc = mysql_prep($_POST['sloc'][$key]); 
            $estye = mysql_prep($_POST['sstye'][$key]); 
            $ensvr = mysql_prep($_POST['snsvr'][$key]); 
            $eehd = mysql_prep($_POST['sehd'][$key]); 
            $epname = mysql_prep($_POST['spname'][$key]); 
            $epn = mysql_prep($_POST['spn'][$key]); 
            $ecu = mysql_prep($_POST['scu'][$key]); 
            $eqty = mysql_prep($_POST['sqty'][$key]); 
            $ett = mysql_prep($_POST['stt'][$key]); 
            $mtyp = mysql_prep($_POST['sstye'][$key]); 
            $mtyp2 = $mtyp=='T'?'T':'S'; 

            // Execute the prepared statement for this single record
            if(!mysqli_stmt_execute($stmt)) {
                mysqli_rollback($link); // Undo the entire batch if one record fails
                die("Insert failed for record $key: " . mysqli_stmt_error($stmt));
            }
        }
        mysqli_commit($link); // Save the batch once all records in it are inserted
        echo "Completed batch of " . count($batch) . " records<br>"; // Optional progress feedback
    }

    mysqli_stmt_close($stmt);
    echo "All $totalRecords records inserted successfully!";
}
?>

Key Improvements & Notes #

• Batch Splitting: array_chunk() breaks your large dataset into smaller groups, keeping each POST payload and SQL operation well under size limits.
• Prepared Statements: Reusing one prepared statement for all records is faster than building a giant concatenated SQL string, and eliminates SQL injection risks more reliably than manual escaping.
• Transactions: Wrapping each batch in a transaction ensures no partial inserts—if any record in the batch fails, the whole batch is rolled back. You can switch to a single transaction for all batches if you need full atomicity.
• Performance Tweaks: Moving set_time_limit(0) outside the loop avoids redundant calls, and reusing the prepared statement cuts down on database overhead.

Critical Configuration Fix #

Your current max_allowed_packet=100000 (~100KB) is likely too small even for batches of 5000 records. Increase this in your MySQL config (my.cnf/my.ini) to at least 64M:

max_allowed_packet=64M

Restart MySQL after making this change to apply it.

内容的提问来源于stack exchange，提问作者David Mukoro