How can I add selective rows in a Mysql statement

Question

I have a table with items (A,B) and periods. I would like to add rows that contain the missing periods per item (for item A add rows with periods 1 to 4, and for item B add a row with the period 3). If possible in a procedure or without actually changing the original table (in reality I have more than a 1000 items)

Original:

item period
a    0
a    5
a    3
b    2
b    4

Desired:

item period
a    0
a    1
a    2
a    3
a    4
a    5
b    2
b    3
b    4

Thank you

Answer 1

You need a numbers table. Let me assume you have one:

select i.item, n.n as period
from (select item, min(period) as minp, max(period) as maxp
      from items
      group by item
     ) i join
     numbers n
     on n.n between i.minp and i.maxp;

If you don't have such a table, you can generate one:

select i.item, n.n as period
from (select item, min(period) as minp, max(period) as maxp
      from items
      group by item
     ) i join
     (select (@rn := @rn + 1) as n
      from items i cross join
           (select @rn := -1) params
      limit 241
     ) n
     on n.n between i.minp and i.maxp;