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?

Disentitle

Add the 'values' part of `insert into table (columns) values (values) – Harvey

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

Councilman

Wow, now that was what I was looking for. I don't understand the code yet, but I get that it works. Thanks so much! – Disentitle

Ok this is driving me crazy. This code is exactly what I need. It works in phpmyadmin and in navicat, but when I bring it into dreamweaver's sql editor it tells me there is a mysql error. When I leave the code for the query in (despite the error) and try to test the web page, the page returns: 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 'CREATE OR REPLACE VIEW generator_256 AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n ' at line 1 What am I doing wrong? – Disentitle

And when I tried to run the query to insert the rows into a numbers table in phpmyadmin, (I can't fit all the code in the comments box), I got: #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 'CREATE OR REPLACE VIEW generator_256 AS SELECT ( ( hi.n &lt;&lt; 4 ) | lo.n ) AS n ' at line 9 – Disentitle

Ok despite the errors, the views somehow were created and seem to be working. Problem appears solved for the time being. Thanks! – Disentitle

Byte shifting and bitwise OR. Now that's binary magic. Nice! Was just looking this up myself. – Weinberg

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

Woodwork

In case it's not obvious, "any real table" needs to have over 64,000 rows to get this result, you will get the lower of the actual row count or the number specified using this. – Remarque

Answer 3

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

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

Ferroelectric

Answer 4

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

Foreignism

Answer 5

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.

Tameratamerlane