32

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.

Community
  • 1
  • 1
drug_user841417
  • 797
  • 1
  • 7
  • 12

5 Answers5

44

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);
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • 2
    So basically it is not possible to populate a column with random numbers in one query? W/o `PROCEDURE` or other hacking – Green Sep 17 '17 at 09:44
39

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.

Community
  • 1
  • 1
rodnaph
  • 1,237
  • 10
  • 12
  • 2
    this was surprisingly useful - and quick - took less than a minute on the cli to add 2 million records. exponential ftw – Darragh Enright May 01 '14 at 10:26
  • 1
    very useful and clever tip: based on exponential evolution of the number of rows, but some times you fell on a duplicate when you reach larger number of inserts – Mohamed23gharbi Jul 27 '15 at 23:08
  • 1
    @Mohamed23gharbi better use `INSERT IGNORE` to skip duplicates. – Mindwin Remember Monica Feb 12 '16 at 20:43
  • You should change your code from `SELECT number * rand()` to `SELECT var_int * rand()` - Otherwise you constantly reduce the pool of numbers the random int is generated from. Set `var_int` to the maximum value of your desired random int. – Julius S. Feb 11 '17 at 09:08
  • The standard & some DBMSs don't specify that a function is called more than once in a query, so this only works if a DBMS says it does. MySQL doesn't say it does. – philipxy Jun 01 '19 at 02:00
20

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..

potame
  • 7,597
  • 4
  • 26
  • 33
user4898949
  • 201
  • 2
  • 2
  • What does `WHERE 1` mean? – Green Sep 17 '17 at 09:45
  • 3
    This really works. This is much simpler and better than accepted answer with long `procedure` thing. – Green Sep 17 '17 at 11:22
  • @Green The update affects all the rows that achieve the condition in `WHERE` then as `WHERE 1` means true all the rows will be affected. – Victor Aguilar Jan 17 '18 at 18:28
  • The standard & some DBMSs don't specify that a function is called more than once in a query, so this only works if a DBMS says it does. MySQL doesn't say it does. – philipxy Jun 01 '19 at 02:01
  • Still works after all these years! :-) Yes, this should be the accepted answer. – Chiwda Aug 30 '21 at 16:22
3

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,

Optimus
  • 2,716
  • 4
  • 29
  • 49
3

If you are lazy and you have the query for creating the table, try http://filldb.info//

Yushan ZHANG
  • 523
  • 5
  • 18