Generate series of random size data in MySQL

Question

I need to populate table with random size of data for each dependency record.

First table is table of products (named products):

id || name
=== + =====
1  || name1
2  || name2
3  || name3

And the second table is table that contains displays of that product (named product_displays). Each display is another row:

id_product || date
=========== + ====
1          || d1
1          || d1
1          || d1
1          || d1
2          || d1
2          || d1
3          || d1
3          || d1
3          || d1

Like you can see date will always be the same value, but the number of rows that I need to insert should be random. And range for that number of returned rows I would like to specify in the query.

I except something like this:

INSERT INTO product_displays (id_product, date)
SELECT
p.id,
'2019-07-06'
FROM products p
JOIN table_with_random_num_of_rows_between_x_and_y t

For each product number of table_with_random_num_of_rows_between_x_and_y rows should be random.

GMB · Accepted Answer · 2019-12-14T21:21:18.287

1

If I followed you correctly, one simple solution is to join the products table with a table of numbers, using a random number as join condition, like:

insert into product_displays (id_product, pdate)
select p.id, '2019-07-06'
from products p
inner join (
    select 1 n union all select 2 union all select 3 union all select 4 union all select 5
) t on t.n <= floor(1 + rand() * 5)

This will produce a random number of records (between 1 and 5) for each product. You can expand the inline table to increase the maximum number of records per product. Alternatively, to manage a great (maximum) number of records, you can use a number table, or use row_number() and an already populated database table with a sufficient number of rows.

Demo on DB Fiddle (you can run the query several times to see the randomness in action):

id_product | pdate     
---------: | :---------
         1 | 2019-07-06
         1 | 2019-07-06
         1 | 2019-07-06
         1 | 2019-07-06
         1 | 2019-07-06
         2 | 2019-07-06
         2 | 2019-07-06
         2 | 2019-07-06
         3 | 2019-07-06
         3 | 2019-07-06

edited Dec 14 '19 at 21:21

answered Dec 14 '19 at 21:14

GMB

216,147
25
84
135

That's quite correct, thanks. But what if I need like 900-1000 rows in return? Expand the union query? That's a lot copy and paste. – b4rt3kk Dec 14 '19 at 21:40
@b4rt3kk: there are other options to generate a table of numbers, that I suggested in my answer: create a fill a table, use `row_number()` against a large table, `cross join` several lists of numbers, ... You can have a look at [this SO question](https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql), [this other one](https://stackoverflow.com/questions/186756/generating-a-range-of-numbers-in-mysql) (and many more). – GMB Dec 14 '19 at 22:01
1

I've created temporary table with records of 1-1000 and joined with RAND on condition. Thanks, your response resolved my problem. – b4rt3kk Dec 14 '19 at 22:19

Generate series of random size data in MySQL

1 Answers1