1

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);
Omar Esenov
  • 13
  • 1
  • 3
  • Weird, who gives you such kinda conditions? – Praveen Kumar Purushothaman Apr 23 '19 at 20:58
  • Do them in batches of a thousand at a time. You also need to take the MySQL `max_allowed_packet` setting into account. – Barmar Apr 23 '19 at 20:59
  • 3
    Can you write the data to a file and then use `LOAD DATA INFILE`? – Barmar Apr 23 '19 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 Apr 23 '19 at 21:13
  • 1
    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 Apr 23 '19 at 21:18
  • PDO also has a very handy `execute()` function that takes an associative array. `mysqli` has a clunky `bind_param` then `execute` combination where `bind_param` cannot take an array, it requires discrete arguments. – tadman Apr 23 '19 at 21:19
  • you could use a stored procedure https://stackoverflow.com/questions/3766282/fill-database-tables-with-a-large-amount-of-test-data –  Apr 23 '19 at 21:34

1 Answers1

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).

Barmar
  • 741,623
  • 53
  • 500
  • 612