# MySQL - Generate Numbers Table for a Range of Numbers

If you are in some environments that you do not have permission to create a new numbers table, you can use the Recursive Common Table Expressions (CTE) approach to create a virtual number sequence table. It's like generate_series available in PostgreSQL.

Be aware that by default, `cte_max_recursion_depth`

in **MySQL** has a value of **1000**, causing the CTE to terminate when it recurses past 1000 levels.

In the following **MySQL** snippet, if you set `@stop_number`

variable to **1001** without changing the default value of `cte_max_recursion_depth`

,
you will get an error similar to the one below.

Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

In MySQL, you can work around this by setting `cte_max_recursion_depth`

to a larger value.

In the snippet, we are adjusting the session value based on the number of iterations required to create number sequence.

It will print 1 to 1001.

To print 1 to 100, simply set `@stop_number`

to 100.

## Solution: Recursive CTE

`-- Adjust lower and upper bound of the number sequence to generate`

SET @start_number := 1;

SET @stop_number := 1001;

-- Adjust max recursion depth based on the number of iterations we need.

-- Optional if you will not recurse past 1000 levels

SET SESSION cte_max_recursion_depth = @stop_number - @start_number + 1;

WITH RECURSIVE cte_print (number)

AS (

-- Anchor member returns the start number

SELECT @start_number

UNION ALL

-- Recursive member that references to the CTE name returns 1 + the previous number

SELECT number + 1

FROM cte_print

WHERE number < @stop_number -- Termination condition for recursive call

)

SELECT *

FROM cte_print;

## Solution: Cross Join and Window Function

An alternative option to generate a virtual numbers table is using `Cross Joins`

along with `Row_Number()`

window function.

The following MySQL snippet will support printing a sequence up to **65,536** numbers.
You could reduce the number of cross joins used if you don't need a sequence with that many numbers.

Executing the MySQL query will print 1 to 10,000.

`-- Adjust lower and upper bound of the number sequence to generate`

SET @start_number := 1;

SET @stop_number := 10000;

-- With 5 common table expressions, we could generate number sequence from 1 up to 65,536

WITH CTE01 -- 2 Rows

AS (

SELECT 1

UNION ALL

SELECT 1

)

,CTE02 -- 2 x 2 = 4 Rows

AS (

SELECT 1

FROM CTE01 AS L

JOIN CTE01 AS R

)

,CTE03 -- 4 x 4 = 16 Rows

AS (

SELECT 1

FROM CTE02 AS L

JOIN CTE02 AS R

)

,CTE04 -- 16 x 16 = 256 Rows

AS (

SELECT 1

FROM CTE03 AS L

JOIN CTE03 AS R

)

,CTE05 -- 256 x 256 = 65,536 Rows

AS (

SELECT 1

FROM CTE04 AS L

JOIN CTE04 AS R

)

,Num_Sequence

AS (

SELECT row_number() OVER (

ORDER BY NULL

) AS number

FROM CTE05

)

SELECT *

FROM Num_Sequence

WHERE number BETWEEN @start_number

AND @stop_number;