How do I populate a MySQL table with many random numbers?

I'm going to ask a question that has been asked in very abstract terms, with (understandably) no concrete answers provided:

From the MySQL prompt, how do I create and populate a table, rand_numbers, with one column, number INT, and 1111 rows, where the number column holds a random number between 2222 and 5555?

Something like:

CREATE TABLE rand_numbers(number INT);

 #run following line 1111 times
INSERT INTO rand_numbers (number) VALUES (2222 + CEIL( RAND() * 3333));

This question has been asked, but either relies on external languages for the loop or is far too general. I would like to know if it's possible to do something this simple from a typical Linux MySQL prompt.

Solution 1:

To create the table use:

CREATE TABLE rand_numbers (
    number INT NOT NULL
) ENGINE = MYISAM;

Then to populate it with random values, you can define a stored procedure (which supports looping):

DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT, IN MinVal INT, IN MaxVal INT)
    BEGIN
        DECLARE i INT;
        SET i = 1;
        START TRANSACTION;
        WHILE i <= NumRows DO
            INSERT INTO rand_numbers VALUES (MinVal + CEIL(RAND() * (MaxVal - MinVal)));
            SET i = i + 1;
        END WHILE;
        COMMIT;
    END$$
DELIMITER ;

CALL InsertRand(1111, 2222, 5555);

Then you can reuse that procedure to insert more random values based on different parameters.. say 600 rows with random values between 1200 and 8500:

CALL InsertRand(600, 1200, 8500);

Solution 2:

Without creating a stored procedure, one technique I've applied is to use the table itself to add the columns. First seed it with a value...

INSERT INTO rand_numbers ( number ) VALUES ( rand() * 3333 );

Then insert again, selecting from this table to double the rows each time...

INSERT INTO rand_numbers ( number ) SELECT number * rand() FROM rand_numbers; 

You don't need to run the second query that many times to get quite a few random rows. Not as "neat" as using a stored procedure of course, just proposing an alternative.

As pointed out by mohamed23gharbi, you can run into duplicates if your test mass is too large. You can use INSERT IGNORE to skip duplicates if that is a problem.