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?

What about derived tables? Also can we assume the existence of an arbitrary table with at least 100 rows? — Martin Smith, Sep 22 '11 at 11:31
@MartinSmith, Clever, I would prefer a solution without that assumption, so NO all tables have just 1 row in them. — Johan, Sep 22 '11 at 11:43
@Johan: If all your tables have 1 row in them, you don't need a db, do you? — ypercubeᵀᴹ, Sep 22 '11 at 11:44
@ypercube, I tagged it with `trick` because I need to use this trick to populate the DB with random data fast. Would you like me to write a 100 million rows? — Johan, Sep 22 '11 at 11:48
I was just teasing you. But yeah, mentioning that you want this to be used with very large sequences, would be good. — ypercubeᵀᴹ, Sep 22 '11 at 11:49
With the `without a temp table` restriction, I would use a `cross join` on derived tables. — ypercubeᵀᴹ, Sep 22 '11 at 11:51

Martin Smith · Accepted Answer · 2011-09-22T11:56:08.767

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

I think if you replace the `limit` with a `where` it will run even faster. Great answer, because you don't need to pre-create a number generator. — Johan, Sep 22 '11 at 12:01
Just one question if I may, can I expand this to 100,000,000 rows? — Johan, Sep 22 '11 at 12:14
@Johan - You could stick it into a View and cross join multiple times. If you did the multiple cross join trick in separate views it would only need 3 views for `10`, `100`, `10,000` then cross join on the last one to get `100,000,000` — Martin Smith, Sep 22 '11 at 12:17

score 4 · Answer 2 · answered Sep 22 '11 at 11:58

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.

Ah, I was lamenting the lack of CTEs in MySQL but of course you can just use a view (+1) not familiar enough with MySQL to know the downsides of `LIMIT`! — Martin Smith, Sep 22 '11 at 12:03
@MartinSmith, Limit works by just selecting all rows, storing those in a temp table (on disk or in mem) and only feeding you the first x rows. — Johan, Sep 22 '11 at 12:11
Well my second version doesn't need either. BTW Every positive integer is the sum of some powers of 2 so maybe you could create views for every power of 2 up till some number then cross join on a union all that adds up to the correct number? Or would that get materialised into some intermediate result set in MySQL? — Martin Smith, Sep 22 '11 at 12:14

score 1 · Answer 3 · edited May 23 '17 at 12:03

1

There is nothing in MYSql that is equal to generate_series. And after reading the below link it seems everything SQL does except MySQL.

Confirmed Here

It seems you will have to take a more complicated route, but I am not experienced enough to give an answer, the link and answers there may lead you in the right directiom.

Side Note:

You can use LIMIT 100 to only return 100 rows, but it seems you want something slightly different.

edited May 23 '17 at 12:03

Community

1
1

answered Sep 22 '11 at 11:35

sealz

5,348
5
40
70

+1, that link is golden. The solution is at the bottom of the article. – Johan Sep 22 '11 at 11:59
Awesome, I wasn't sure I only knew that generate wasn't an option. Enjoy! – sealz Sep 22 '11 at 12:00

score 0 · Answer 4 · answered Sep 22 '11 at 11:34

0

Johan,

You use this form:

SELECT max(t1.id)+1, @rownum:=@rownum+1 rownum
FROM (SELECT @rownum:=0) r, t1;

Test this sql I think the code was resolve.

answered Sep 22 '11 at 11:34

Bruno Arueira

314
1
4
17

You're missing a `LIMIT` but `max(t1.id)+1` will only return 1 row anyway so you need to expand this result somehow. – Martin Smith Sep 22 '11 at 11:36
You are doing a cross join on two resultsets with just one row each. That will just generate 1 row. I feel you are moving in the right direction, but this is not it. – Johan Sep 22 '11 at 11:46

How to select a single row a 100 million x

4 Answers4

Linked