MySQL 5.7 – Return Row N Times Based on Column Value

mysql-5.7

Say I have a given table with the following data:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |

id is the primary key.

pid,email has a unique index.

entries indicates the number of entries the person has into a prize draw.

How can I query the above data to get the following result:

| id | pid | email           | entries |
|----|-----|-----------------|---------|
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 1  | 1   | foo@example.com | 3       |
| 2  | 1   | bar@example.com | 1       |
| 3  | 1   | baz@example.com | 2       |
| 3  | 1   | baz@example.com | 2       |

where each row is returned n times based on the entries column a row has?

The reason is to hand the result to a randomized name drawer where the more entries you have the more chances you have of winning.

Currently, we have the setup where each entry creates a new row in the table but this starts having performance issues when certain entrants make a thousand entries in one go. This generates thousands of inserts at a time and when we have a popular prize we are running into performance issues where inserts start taking too long as there are so many.

If we could restructure each how we store the data we can then just have 1 upsert query when, for example, someone gets 1000 entries in one go rather than generating 1000 individual inserts.

Best Answer

For version 8+

WITH 
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num+1 FROM cte WHERE num < ( SELECT MAX(entries) FROM table)
       )
SELECT table.*
FROM table, cte
WHERE table.entries <= cte.num

For version 5.7 we must generate numbers table in the query

SELECT table.*
FROM table, 
     ( SELECT 100*t1.num+10*t2.num+t3.num+1 num
       FROM (SELECT 0 num 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) t1,
            (SELECT 0 num 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) t2,
            (SELECT 0 num 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) t3
     ) cte
WHERE table.entries >= cte.num

The number of tX pseudo-tables is dependent by max possible entries value. In the code above this max value assumed to be 1000.

Or you can generate numbers table using user-defined variable based on some (the same or another) table (if such table exists) for which the records count is over max possible entries value.

Or you can create static numbers table in this or system database and use it.

Related Question