Expanding a mySQL table using only mySQL

Let's say I have a mySQL table whose structure is like this:

mysql> select * from things_with_stuff;
+----+---------+----------+
| id | counter | quantity |
+----+---------+----------+
|  1 |     101 |        1 |
|  2 |     102 |        2 |
|  3 |     103 |        3 |
+----+---------+----------+

My goal is to "expand" the table so I end up with something like:

mysql> select * from stuff;
+----+---------+
| id | counter |
+----+---------+
|  1 |     101 |
|  2 |     102 |
|  3 |     102 |
|  4 |     103 |
|  5 |     103 |
|  6 |     103 |
+----+---------+

And I want to do this "expansion" using only mysql. Note that I end up with a row per quantity and per counter. Any suggestions? A store开发者_Python百科d procedure is not an option here (I know they offer while loops).

Thanks!

The following will do the trick as long as some_large_table has a length greater than or equal to the largest quantity in things_with_stuff. For example, let some_large_table be a big fact table in a data warehouse.

SELECT @kn:=@kn+1 AS id, counter
FROM (SELECT @kn:=0) k, things_with_stuff ts
INNER JOIN (
    SELECT @rn:=@rn+1 AS num
    FROM (SELECT @rn:=0) t, some_large_table
    ) nums ON num <= ts.quantity;

Assuming there is a maximum value for quantity, you could do:

INSERT INTO things SELECT counter FROM things_with_stuff WHERE quantity > 0;
INSERT INTO things SELECT counter FROM things_with_stuff WHERE quantity > 1;
INSERT INTO things SELECT counter FROM things_with_stuff WHERE quantity > 2;
--... and so on until the max.

It's a bit of a hack but it should do the job.

If the ordering is important you could do a clean up afterwards.

I have sometimes in databases a table named num (number) with a single column i, filled with all integers from 1 to 1000000. It's not hard to make such a table and populate it.

Then you could use this if stuff.id is auto incremented:

INSERT INTO stuff
( counter )
    SELECT ts.counter
    FROM things_with_stuff AS ts
      JOIN num
        ON num.i <= ts.quantity