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

Question

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.

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