How to insert million rows into MySQL database using php script?

Question

I`m looking for php script to generate random data and insert 1 million rows into the mysql database.

It`s a test case i received as part of my homework.

Here is a piece of code i`ve tried but it didnt work as the allowed memory size exhausted.

P.S. Cant change memory_limit as it`s prohibited and counts as cheating :)

$query = "INSERT INTO book(firstname, lastname, phone_number, address) VALUES ";
   $query_parts = array();
   for($i=0; $i<1000000; $i++){
     $firstname = 'name' . $i;
     $lastname = 'lastname' . $i;
     $phone_number = mt_rand();
     $address = 'address' . mt_rand();
     $query_parts[] = "('" . $firstname . "', '" . $lastname . "', '" . $phone_number . "', '" . $address . "')";
   }
   $query .= implode(',', $query_parts);

   $create_record_query = mysqli_query($connection, $query);

Do them in batches of a thousand at a time. You also need to take the MySQL max_allowed_packet setting into account. — Barmar
– Barmar, Commented Apr 23, 2019 at 20:59
Can you write the data to a file and then use LOAD DATA INFILE? — Barmar
– Barmar, Commented Apr 23, 2019 at 20:59
You will need to chunk the data into smaller requests and process. This is actually a pretty common when handling large amounts of data. @Barmar If that is allowed I agree that is definitely the best solution. — Derek Gutierrez
– Derek Gutierrez, Commented Apr 23, 2019 at 21:13
Can you use PDO instead of mysqli? It's easier to create a PDO prepared statement with a thousands ? and put all the values in an array. — Barmar
– Barmar, Commented Apr 23, 2019 at 21:18

Barmar · Accepted Answer · 2021-07-10 12:58:00Z

1

Do it in batches, e.g. 1000 rows at a time.

Use PDO rather than mysqli, as it allows you to execute a prepared statement with an array to supply the values.

$batch_size = 1000;

$sql = "INSERT INTO book(firstname, lastname, phone_number, address) VALUES ";
// Add 1,000 value lists
$sql .= str_repeat("(?,?,?,?),", $batch_size-1);
$sql .= "(?,?,?,?)";
$stmt = $pdo->prepare($sql);
for ($i = 0; $i < (1000000/$batch_size); $i++) {
    $vals = array();
    for ($j = 0; $j < $batch_size; $j++) {
        array_push($vals, "name$j", "address$j", mt_rand(), "address" . mt_rand());
    }
    $stmt->execute($vals);
}

You might need to increase the MySQL server's max_allowed_packet setting to handle 1000 rows, or decrease the batch size. Make sure that $batch_size evenly divides into 1 million -- the code above doesn't handle a last batch that's not the same size (consider fixing that as an exercise for the reader).

edited Jul 10, 2021 at 12:58

answered Apr 23, 2019 at 21:29

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

smolo Over a year ago

$sql .= "(?,?,?,?),"; is supposed to be $sql .= "(?,?,?,?)";

Collectives™ on Stack Overflow

How to insert million rows into MySQL database using php script?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related