A better approach to MySQL row generationAugust 3, 2011 3:01 pm SQL, SQL
I love Markus Winand. Use the Index, Luke taught me a lot about SQL.
Markus has produced a coping strategy for some of MySQL’s limitations in generating sequences. I present an alternative form which produces arbitrary sized ranges, which he seems to believe is not possible. Ah, Markus: it is.
So, this article is actually wrong. There’s a call-time limitation I didn’t know about: apparently in MySQL you can’t call a stored procedure from a query. That renders this seriously useless. I’m leaving the article up just for the sake of permanence.
This advice is wrong. Do not believe it. I was in error. It is kept only for posterity.
-- ------- -- -- in re: http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator -- -- No, this way is not our only hope: there is another. drop procedure if exists nseq; delimiter // -- ------- -- -- Use union all to generate a 2-row table. -- Use log 2 to find power expansion count to cap the desired size. -- Use cross joins to expand our 2-row to the smallest superior table. -- Select the diminished part to get our sequence. create procedure nseq(in upto integer) begin declare itLeft integer default -1; set itLeft = ceil(log2(upto)); set @front := 'select rownum from (select @rownum := @rownum+1 as rownum from (SELECT @rownum := 0) r, (select 1 as hidden union all select 2) a0'; set @upTo := upto; set @interior := ''; while (itLeft > 1) do set @interior := concat(@interior, ' cross join (select 1 as hidden union all select 2) a', cast(itLeft as char)); set itLeft := itLeft - 1; end while; set @back := ') sl where rownum <= ?;'; set @query := concat(@front, @interior, @back); prepare stmt from @query; execute stmt using @upTo; deallocate prepare stmt; end // delimiter ;
Hopefully he will agree, or explain what I didn’t understand in his needs.