Creating a "Numbers Table" in MySQL

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

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

Tags:

Mysql

Sql