5

Im new to MySql, and I need to insert into table 10000 random 2-digit numbers. Is there Easy way to to this?

el ninho
  • 4,183
  • 15
  • 56
  • 77

2 Answers2

18

Use RAND() as described here. For generating 2-digit random numbers between 10 and 99, use FLOOR(10 + (RAND() * 90)).

nikhil500
  • 3,458
  • 19
  • 23
4

Try this: 

DELIMITER $$
CREATE PROCEDURE random_fill( IN cnt INT )
BEGIN

    fold: LOOP

         IF cnt < 1 THEN
             LEAVE fold;
         END IF;

        INSERT INTO foo ( bar ) VALUES ( 9 + CEIL( RAND() * 90 ) );

        SET cnt = cnt - 1;
    END LOOP fold;

END$$   
DELIMIMTER ;

To use this:

CALL random_fill(10000);

What you have to change is this line:

INSERT INTO foo ( bar ) VALUES ( CEIL( RAND() * higher ) );

Replace the foo and bar with something that exists in your database. It is possible to create a procedure, where the name of table and row are also supplied as parameters, but that would require to CONCAT the query on fly .. looks hack'ish and ugly.

tereško
  • 58,060
  • 25
  • 98
  • 150
  • 1
    I would recommend putting a transaction in there. Without it, 100K inserts timed out 22k, with it, 100K took 4.047 seconds on my dev machine. – Andrew Grothe Sep 06 '16 at 01:36