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
– bingjie2680, Commented Dec 5, 2011 at 20:51
Just use a 'normal' query, there is no security hazard in this — abcde123483
– abcde123483, Commented Dec 5, 2011 at 20:52
but using prepared statement should be faster??? I have googled preparaed statment, it seems they are not reusing the statement. — bingjie2680
– bingjie2680, Commented Dec 5, 2011 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. php.net/manual/en/book.pdo.php — Kenaniah
– Kenaniah, Commented Dec 5, 2011 at 20:54
1 million rows is not very much. I can't imagine you would spend much time waiting either way. — abcde123483
– abcde123483, Commented Dec 5, 2011 at 20:54

Dan Heberden · Accepted Answer · 2011-12-05 21:03:16Z

1

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

answered Dec 5, 2011 at 21:03

Dan Heberden

11.1k3 gold badges35 silver badges29 bronze badges

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

Comments

Kenaniah · Accepted Answer · 2011-12-05 20:52:53Z

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 5, 2011 at 20:52

Kenaniah

5,20126 silver badges28 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:33:58Z

0

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

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Dec 5, 2011 at 21:15

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

Collectives™ on Stack Overflow

using php insert huge amount of data into mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related