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

I have always used this -

insert into rand_numbers ( number ) select rand() from (
    select 0 as i
    union select 1 union select 2 union select 3
    union select 4 union select 5 union select 6
    union select 7 union select 8 union select 9
) as t1, (
    select 0 as i
    union select 1 union select 2 union select 3
    union select 4 union select 5 union select 6
    union select 7 union select 8 union select 9
) as t2, (
    select 0 as i
    union select 1 union select 2 union select 3
    union select 4 union select 5 union select 6
    union select 7 union select 8 union select 9
) as t3;

Inserts 1000 random numbers. On-the-fly tables t1, t2, t3 are cross joined so we get 10x10x10 rows.

So, for like a million rows, just add 3 more of (select 0 as i union select 1 ...) as statements. This seems convenient to me, since there's not much effort copy-pasting a few lines a bunch of times.

Hope this helps,

The task can be done also this way:

-- scale from 0 to MAX

UPDATE `table` SET `column` = 1000 * RAND() WHERE 1;

-- scale from MIN to MAX

UPDATE `table` SET `column` = MIN + (MAX - MIN) * RAND() WHERE 1;

You can also use math function like FLOOR(), CEIL(), etc. in the expression..

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

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.