Bulk Insert Update into MySQL using PHP

Sometimes we need to insert large data set into database table. There are several ways to bulk insert data into MySQL table.

Using one INSERT statement per row is quite inefficient. Instead of this, we can assemble one INSERT statement with multiple rows.

Today, I am going to show how to perform bulk insert into MySQL table using PHP.

MySQL query for inserting multiple rows

INSERT statements that use VALUES syntax can insert multiple rows. In order to do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

The following query inserts multiple rows.

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Source

Bulk Insert Using PHP

You can use PHP to prepare MySQL statement to insert multiple rows as well as run this query.

PHP’s concatenation can be used to build query. Here’s a simple snippet.

<?php
  
  $total_rows = 1000000;
  $batch_id = 1;

  $query = "INSERT INTO tbl_coupons (batch_id, code) VALUES ";
  $values = '';
  
  for($i = 1; $i <= $total_rows; $i++){
    // Create string with random characters
    $coupon_code = echo chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)); 
    $values .= "('$batch_id', '$coupon_code'),";
  }

  $values = substr($values, 0, strlen($values) - 1);

  $this->db->query($query);
  
?>

The above snippet inserts multiple rows. Alternatively, you can also use implode() function to avoid string concatenation.

However, there is a serious flaw with this snippet. The real problem occurs when there are lot of columns in the table and the query length exceeds mysql’s max_allowed_packet value. You might encounter warning message as follows:

Warning: Error while sending QUERY packet. PID=33735

To check ‘max_allowed_packet’, you can simple run the following statement.

SHOW VARIABLES LIKE 'max_allowed_packet';

In order to avoid this, you can build an inner loop to run query.  Here’s how to do this:

<?php
  
  $total_rows = 1000000;
  $batch_id = 1;
  $insert_limit = 1000; 

  $query = "INSERT INTO tbl_coupons (batch_id, code) VALUES ";
  $values = '';
  
  for($i = 1; $i <= $total_rows; $i++){
    // Create string with random characters
    $coupon_code = echo chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)) . chr(rand(65,90)); 
    $values .= "('$batch_id', '$coupon_code'),";
    
    if($i % $insert_limit == 0) {
      $values = substr($values, 0, strlen($values) - 1);
      $insert_query = $query . $values;
      $this->db->query($insert_query);
      $values = '';  
    }
  }

  $values = substr($values, 0, strlen($values) - 1);

  $this->db->query($query);
  
?>

So far, we have learnt how to bulk insert using bunch of INSERT statements. However, this is not the best way to insert bulk data.

If you don’t need to manipulate the data before inserting it, you can always use mysql’ LOAD DATA. Even if you need to manipulate data before inserting, you can generate csv file after manipulation and insert using LOAD DATA.

Bulk Update

For Bulk Update, you can use ON DUPLICATE KEY UPDATE construct. The query would look like as follows:

INSERT INTO `table_name` (id, col)
VALUES 
    (1, 'val1'),
    (2, 'val2'),
    (3, 'val3')
ON DUPLICATE KEY UPDATE col = VALUES(col);
 

We can easily construct similar type of query  using PHP. Lets use array containing key value pair and implode statement to generate query. Here’s the snippet.

<?php

  $coupons = array(
      1 => 'ABCDE',
      2 => 'GHIJK',
      3 => 'EFGHI',
      4 => 'RSTUV',
      5 => 'VWXYZ',
    );
  
  $data = array();	
  
  foreach ($coupons AS $key => $value) {
    $data[] = "($key, '$value')";
  }

  $query = "INSERT INTO `tbl_coupons` (id, code) VALUES " . implode(', ', $data) . " ON DUPLICATE KEY UPDATE code = VALUES(code)";

  $this->db->query($query);

?>

Alternatively, you can use CASE construct in UPDATE statement. Then the query would be something like:

UPDATE tbl_coupons
SET code = (CASE id WHEN 1 THEN 'ABCDE'
                 WHEN 2 THEN 'GHIJK'
                 WHEN 3 THEN 'EFGHI'
         END)
WHERE id IN(1, 2 ,3);

In this way, we can insert and update multiple rows into MySQL table using PHP.

You may like: Multiple files upload using HTML and PHP