Creating a "Numbers Table" in MySQL

Question

I'm trying to generate a big table of consecutive numbers in MySQL.

I just want 2 columns; a primary key and a numbers column with a range of 0-X, where X is very large. Approx. 64,000 rows should do it. I've tried this code with no success:

CREATE TABLE numbers (
   number           INT         NOT NULL
   CONSTRAINT XPKnumbers
      PRIMARY KEY CLUSTERED (number)
    )

INSERT INTO numbers (number) VALUES (0)

DECLARE @i          INT
SET @i = 20

WHILE 0 < @i
   BEGIN
      INSERT INTO numbers (number)
         SELECT number + (SELECT 1 + Max(number) FROM numbers)
            FROM numbers

      SET @i = @i - 1
   END

SELECT * FROM numbers

and I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT XPKnumbers PRIMARY KEY CLUSTERED (number) ) INSERT INTO n' at line 3

Anybody have any suggestions to make this work?

Answer 1

You are missing semicolons, commas, and even after correcting syntax it is still not a good idea to select max from the table every time just to insert one more row in a loop.

Drop that and use generators from http://use-the-index-luke.com/blog/2011-07-30/mysql-row-generator :

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 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   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

And if for whatever reason you really need a table of numbers just do:

INSERT INTO numbers(number)
SELECT n FROM generator_64k WHERE n < 64000

Answer 2

Here's a quick and simple way to generate a list of numbers. Running this query using MySQL produced a list of 64,000 numbers in sequence in 0.561 seconds.

set @i = 0;

SELECT * FROM (
SELECT @i:=@i+1 AS IndexNo FROM my_table -- any real table can be used here
HAVING 
@i < 64000
)x

Answer 3

From MySQL 8.0 you could use RECURSIVE CTE to generate tally table:

SET @@cte_max_recursion_depth  = 5000;
WITH RECURSIVE cte AS
(
   SELECT 1 AS i
   UNION ALL
   SELECT i+1
   FROM cte
   WHERE i < 3000
)
SELECT *
FROM cte;

DBFiddle Demo

Answer 4

I needed to create a numbers table in MySQL today but didn't have permissions to create a view object so decided to modify the earlier answer by @piotrm.

So instead of a persisted object, a CTE with a bit of "fun", binary-style math" can do the job:

WITH n (number) 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
)
SELECT (d.number * Pow(8, 3))
     + (c.number * Pow(8, 2))
     + (b.number * Pow(8, 1))
     + (a.number * Pow(8, 0)) AS number
FROM   n AS a
 CROSS
  JOIN n AS b
 CROSS
  JOIN n AS c
 CROSS
  JOIN n AS d
;

This will generate you 4,096 numbers and the pattern can be easily extended to give you more as required.

P.S.: the bit-shift magic in @piotrm's answer is cool, but I find it harder to follow... hence why I've swapped to some "simple" math.

Answer 5

You're missing a comma between the column and constraint declaration:

CREATE TABLE numbers (
   number           INT         NOT NULL,
   CONSTRAINT XPKnumbers
      PRIMARY KEY CLUSTERED (number)
    )