MySql Integer Sequence Generation

01 Jan 2016 • sequences mysql dbms

Generating integer sequences (e.g. all numbers between 20 and 100) in MySql is much harder than it needs to be.
Other DBMS products have had such support for quite awhile.

In PostgresSql, you can do something like so:

SELECT  *
FROM    generate_series(1, 6) number

That will generate a table of numbers from 1 to 6.

Seems like basic functionality right? Not in glorious MySql. You have several options but they are either
hacky or require a fair amount of work.

Use unions in multiple subquery selects in from to manually create the intended sequence:

SELECT @row := @row + 1 as row FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row := 0)

That will generate a cartesian product of 10^4 records (t * t2 * t3 * t4 = 10,000). The outer select assigns the row number which
can be changed in the last from subquery (SELECT @row := 0).

Use records from another table

This is very hacky and is a bad idea. It only works if you have enough records in the table to cover as many as you need.

SELECT @row := @row + 1 as row, records_table.*
FROM table_with_enough_records records_table, (SELECT @row := 0) r

Use a temporary table with a stored procedure

This might be a little slower, but you will know how many records you have.

See my prior post on mysql fizz buzz for an idea or this stackoverflow post

Use a MySql 3rd party implementation of Pgsql's generate_sequence()

See this github repo

Use a different DBMS :)

MariaDb would be an easy, almost drop-in replacement. PostgresSql would be another good choice.