Generating a range of numbers in MySQL

How do I generate a range of consecutive numbers (one per line) from a MySQL query so that I can insert them into a table?

For example:

nr
1
2
3
4
5

I would like to use only MySQL for this (not PHP or other languages).

Here is one way to do it set-based without loops. This can also be made into a view for re-use. The example shows the generation of a sequence from 0 through 999, but of course, it may be modified to suit.

INSERT INTO
    myTable
    (
    nr
    )
SELECT
    SEQ.SeqValue
FROM
(
SELECT
    (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
FROM
    (
    SELECT 0  SeqValue
    UNION ALL
    SELECT 1 SeqValue
    UNION ALL
    SELECT 2 SeqValue
    UNION ALL
    SELECT 3 SeqValue
    UNION ALL
    SELECT 4 SeqValue
    UNION ALL
    SELECT 5 SeqValue
    UNION ALL
    SELECT 6 SeqValue
    UNION ALL
    SELECT 7 SeqValue
    UNION ALL
    SELECT 8 SeqValue
    UNION ALL
    SELECT 9 SeqValue
    ) ONES
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 10 SeqValue
    UNION ALL
    SELECT 20 SeqValue
    UNION ALL
    SELECT 30 SeqValue
    UNION ALL
    SELECT 40 SeqValue
    UNION ALL
    SELECT 50 SeqValue
    UNION ALL
    SELECT 60 SeqValue
    UNION ALL
    SELECT 70 SeqValue
    UNION ALL
    SELECT 80 SeqValue
    UNION ALL
    SELECT 90 SeqValue
    ) TENS
CROSS JOIN
    (
    SELECT 0 SeqValue
    UNION ALL
    SELECT 100 SeqValue
    UNION ALL
    SELECT 200 SeqValue
    UNION ALL
    SELECT 300 SeqValue
    UNION ALL
    SELECT 400 SeqValue
    UNION ALL
    SELECT 500 SeqValue
    UNION ALL
    SELECT 600 SeqValue
    UNION ALL
    SELECT 700 SeqValue
    UNION ALL
    SELECT 800 SeqValue
    UNION ALL
    SELECT 900 SeqValue
    ) HUNDREDS
) SEQ

Here's a hardware engineer's version of Pittsburgh DBA's solution:

SELECT
    (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16;

If you need the records in a table and you want to avoid concurrency issues, here's how to do it.

First you create a table in which to store your records

CREATE TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Secondly create a stored procedure like this:

DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END;;
DELIMITER ;

Lastly call the SP:

CALL dowhile();
SELECT * FROM incr;

Result

Id
1
2
3
4
5

Let's say you want to insert numbers 1 through 100 into your table. As long as you have some other table that has at least that many rows (doesn't matter the content of the table), then this is my preferred method:

INSERT INTO pivot100 
SELECT @ROW := @ROW + 1 AS ROW
 FROM someOtherTable t
 join (SELECT @ROW := 0) t2
 LIMIT 100
;

Want a range that starts with something other than 1? Just change what @ROW gets set to on the join.

DECLARE i INT DEFAULT 0;

WHILE i < 6 DO
  /* insert into table... */
  SET i = i + 1;
END WHILE;

As you all understand, this is rather hacky so use with care

SELECT
  id % 12 + 1 as one_to_twelve
FROM
  any_large_table
GROUP BY
  one_to_twelve
;

Very similar to the accepted response, but using the new WITH syntax for mysql >= 8.0 which makes a lot more legible and the intent is also clearer

WITH DIGITS (N) AS (
  SELECT 0 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)
SELECT 
  UNITS.N + TENS.N*10 + HUNDREDS.N*100 + THOUSANDS.N*1000 
FROM 
  DIGITS AS UNITS, DIGITS AS TENS, DIGITS AS HUNDREDS, DIGITS AS THOUSANDS;

All other answers are good, however they all have speed issues for larger ranges because they force MySQL to generate every number then filter them.

The following only makes MySQL generate the numbers that are needed, and therefore is faster:

set @amount = 55; # How many numbers from zero you want to generate

select `t0`.`i`+`t1`.`i`+`t2`.`i`+`t3`.`i` as `offset`
from
(select 0 `i` union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) `t0`,
(select 0 `i` union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90) `t1`,
(select 0 `i` union select 100 union select 200 union select 300 union select 400 union select 500 union select 600 union select 700 union select 800 union select 900) `t2`,
(select 0 `i` union select 1000 union select 2000 union select 3000 union select 4000 union select 5000 union select 6000 union select 7000 union select 8000 union select 9000) `t3`
where `t3`.`i`<@amount
and `t2`.`i`<@amount
and `t1`.`i`<@amount
and `t0`.`i`+`t1`.`i`+`t2`.`i`+`t3`.`i`<@amount;

With the above you can generate upto 10,000 numbers (0 to 9,999) with no slower speed overhead for lower numbers, regardless how low they are.

The "shortest" way i know (in MySQL) to create a table with a long sequence is to (cross) join an existing table with itself. Since any (common) MySQL server has the information_schema.COLUMNS table i would use it:

DROP TABLE IF EXISTS seq;
CREATE TABLE seq (i MEDIUMINT AUTO_INCREMENT PRIMARY KEY)
    SELECT NULL AS i
    FROM information_schema.COLUMNS t1
    JOIN information_schema.COLUMNS t2
    JOIN information_schema.COLUMNS t3
    LIMIT 100000; -- <- set your limit here

Usually one join should be enough to create over 1M rows - But one more join will not hurt :-) - Just don't forget to set a limit.

If you want to include 0, you should "remove" the AUTO_INCEMENT property.

ALTER TABLE seq ALTER i DROP DEFAULT;
ALTER TABLE seq MODIFY i MEDIUMINT;

Now you can insert 0

INSERT INTO seq (i) VALUES (0);

and negative numbers as well

INSERT INTO seq (i) SELECT -i FROM seq WHERE i <> 0;

You can validate the numbers with

SELECT MIN(i), MAX(i), COUNT(*) FROM seq;

This is based on a previous answer (https://stackoverflow.com/a/53125278/2009581), but is compatible with MySQL 5.7. It works for replicas and read-only users:

SELECT x1.N + x10.N*10 + x100.N*100 + x1000.N*1000
  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) x1,
       (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) x10,
       (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) x100,
       (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) x1000
 WHERE x1.N + x10.N*10 + x100.N*100 + x1000.N*1000 <= @max;

It generates integers in the range of [0, @max].

The idea I want to share is not a precise response for the question but can be useful for some so I would like to share it.

If you frequently need only a limited set of numbers then it can be beneficial to create a table with the numbers you may need and just use that table every time. For example:

CREATE TABLE _numbers (num int);
INSERT _numbers VALUES (0), (1), (2), (3), ...;

This can be applied only if you need numbers below a certain reasonable limit, so don't use it for generating sequence 1...1 million but can be used for numbers 1...10k, for example.

If you have this list of numbers in the _numbers table then you can write queries like this, for obtaining the individual characters of a string:

SELECT number, substr(name, num, 1) 
    FROM users
    JOIN _numbers ON num < length(name)
    WHERE user_id = 1234
    ORDER BY num;

If you need larger numbers than 10k then you can join the table to itself:

SELECT n1.num * 10000 + n2.num
    FROM _numbers n1
    JOIN _numbers n2
    WHERE n1 < 100 
    ORDER BY n1.num * 10000 + n2.num; -- or just ORDER BY 1 meaning the first column

Here's a way to do it with json_table if you have MySql 8 and above:

set @noRows = 100;
SELECT tt.rowid - 1 AS value
  FROM JSON_TABLE(CONCAT('[{}', REPEAT(',{}', @noRows - 1), ']'),
                  "$[*]" COLUMNS(rowid FOR ORDINALITY)
       ) AS tt; 

(h/t - https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/)

with recursive cte..

  with recursive rnums as (
  select 1 as n
      union all
  select n+1 as n from rnums
      where n <10
  )
  select * from rnums
  ;

Result would be.. +------+ | n | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +------+ 10 rows in set (0.00 sec)

In MariaDB you can do:

SELECT * FROM seq_i_to_N

For example:

SELECT * FROM seq_0_to_1000

SELECT * FROM seq_1_to_1000000

Reference: https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/