How to select a single row a 100 million x

Question

I would like to generate the following output, using a single row from a select.

SELECT max(t1.id)+1 as new_id FROM t1;
-> 101

However I want to do

SELECT s.last_id, sequence(1..100000000) as new_id 
FROM (SELECT max(table1.id)+1 as last_id FROM table1) s;
-> 101,1
-> 101,2
-> 101,3 
......
-> 101,100000000

In postgreSQL I can do this using:

SELECT s.last_id, generate_series(1,100000000) 
FROM (SELECT max(table1.id)+1 as last_id FROM table1) s;  -- returns 100,000,000 rows 

How do I do this in MySQL without using a temp-table?

Answer 1

Slight amend to Bruno's solution

SELECT (SELECT COALESCE(max(id),0)+1 FROM table1), 
        @rownum:=@rownum+1 new_id 
FROM 
(SELECT @rownum:=0) r, 
(SELECT 1 UNION ALL SELECT 2) t1,
(SELECT 1 UNION ALL SELECT 2) t2,
(SELECT 1 UNION ALL SELECT 2) t3,
(SELECT 1 UNION ALL SELECT 2) t4,
(SELECT 1 UNION ALL SELECT 2) t5,
(SELECT 1 UNION ALL SELECT 2) t6,
(SELECT 1 UNION ALL SELECT 2) t7
LIMIT 100

Or another version without the variables

SELECT (SELECT Coalesce(MAX(id), 0) + 1
        FROM   table1),
       t1.n * 10 + t2.n + 1 AS new_id
FROM   (SELECT 0 AS n UNION ALL
        SELECT 1      UNION ALL
        SELECT 2      UNION ALL
        SELECT 3      UNION ALL
        SELECT 4      UNION ALL
        SELECT 5      UNION ALL
        SELECT 6      UNION ALL
        SELECT 7      UNION ALL
        SELECT 8      UNION ALL
        SELECT 9) t1,
       (SELECT 0 AS n UNION ALL
        SELECT 1      UNION ALL
        SELECT 2      UNION ALL
        SELECT 3      UNION ALL
        SELECT 4      UNION ALL
        SELECT 5      UNION ALL
        SELECT 6      UNION ALL
        SELECT 7      UNION ALL
        SELECT 8      UNION ALL
        SELECT 9) t2
ORDER  BY new_id  

Answer 2

Thanks to @harper89 I found the answer here:

http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator#mysql_generator_code

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 
     CROSS JOIN generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo
     CROSS JOIN generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo
     CROSS JOIN generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo
     CROSS JOIN generator_16 hi;

Now I can generate the result I want using:

SELECT s.last_id, g.n as new_id FROM (SELECT max(table1.id)+1 as last_id FROM table1) s
CROSS JOIN generator_256 g
WHERE G.N BETWEEN 1 AND 100
ORDER BY g.n ASC;

Using LIMIT is a bad idea, because than up to a million+ rows will be stored in a temp-table. With the where you don't need the temp-storage.