using php insert huge amount of data into mysql

Question

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.

I am a beginer in php, so I don't know whether I should use prepared statement or raw query....what do you suggest? — bingjie2680, Dec 05 '11 at 20:51
Just use a 'normal' query, there is no security hazard in this — abcde123483, Dec 05 '11 at 20:52
but using prepared statement should be faster??? I have googled preparaed statment, it seems they are not reusing the statement. — bingjie2680, Dec 05 '11 at 20:53
If you're a beginner, I would recommend reading up on PDO. It's a much better way to communicate with databases in PHP. http://php.net/manual/en/book.pdo.php — Kenaniah, Dec 05 '11 at 20:54
1 million rows is not very much. I can't imagine you would spend much time waiting either way. — abcde123483, Dec 05 '11 at 20:54
actually, I am not waiting, this is my first time writing such things, so I don't have a fast way. — bingjie2680, Dec 05 '11 at 20:56

score 1 · Answer 1 · answered Dec 05 '11 at 20:52

1

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

answered Dec 05 '11 at 20:52

Kenaniah

5,171
24
27

score 1 · Accepted Answer · answered Dec 05 '11 at 21:03

Two approaches:

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

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 );
}

score 0 · Answer 3 · edited May 23 '17 at 10:33

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

using php insert huge amount of data into mysql

3 Answers3