CODEWITHSUNDEEP
phpmysql
bingjie2680
bingjie2680

Reputation: 7773

using php insert huge amount of data into mysql

I would like to generate random strings and insert into database, the insertion should be efficient and robust. I have a generating strings function working like this:

public function getRandString(){
    $string= //generats a random string
    return $string;
}

db connection:

$con = mysql_connect($host, $user, $pass)
or die ( " Not able to connect to server ");
mysql_select_db($db_name);

Now I want to create a method that insert the random strings into database 1 millon rows at once with each row contains 2 column of random strings, how should I go about this?

thanks for help in advance.

Upvotes: 0

Views: 2533

Answers (3)

biziclop
biziclop

Reputation: 14596

We can combine two tricks to generate a table of junk:

Create a table of single digits: https://stackoverflow.com/a/273703/260007

CREATE TABLE num (i int);
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

Generate random strings:

INSERT INTO junktable
SELECT
  (SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6)) AS aa,
  (SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6)) AS bb
FROM       num AS l1
INNER JOIN num AS l2
INNER JOIN num AS l3
INNER JOIN num AS l4
INNER JOIN num AS l5
INNER JOIN num AS l6
LIMIT 100; -- number of wanted rows

Upvotes: 0

Dan Heberden
Dan Heberden

Reputation: 11068

Two approaches:

  1. Use php to create a sql file to import via the command line, phpmyadmin, sequelpro, tomcat, etc

    • open a file
    • append the INSERT INTO whatever VALUES ( 'randomshit', 'randomshit' ); however many times you want
    • close the file, use it to populate the db

  2. Use the connection you mentioned above:

    • create the INSERT INTO whatever VALUES( 'randomshit', 'randomshit' ); in batches of 25000 

      $howMany = 40;
// do this 40 times (40x25000 ) = 1,000,000
while( --$howmany ) {
  $random = "";
  $randomMany = 25000;
  while( --$randomMany ) {
    $random += sprintf( "INSERT INTO whatever VALUES ('%s', '%s'); ", rand_text(), rand_text() );
  }
  // you'll now have a big list (25000 ) of insert queries to insert
  mysql_query( $random );
}

Upvotes: 1

Kenaniah
Kenaniah

Reputation: 5201

You would be better off creating your random string function database-side and running an INSERT ... SELECT query once to generate your rows.

Upvotes: 1

Related Questions