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.

caution

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.

danger

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

Print 1 to 1001 Using 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.

Print 1 to 10000 Using Cross Joins
-- 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;

Solution: Recursive CTE​

Solution: Cross Join and Window Function​

Solution: Recursive CTE

Solution: Cross Join and Window Function