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?
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With