Creating a “Numbers Table” in MySQL

by

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

More Related Contents:

  1. If two columns are 1 then make new column
  2. How can I return pivot table output in MySQL?
  3. Retrieving the last record in each group – MySQL
  4. Cast from VARCHAR to INT – MySQL
  5. Required to join 2 tables with their FKs in a 3rd table
  6. Does COUNT(*) always return a result?
  7. MySQL : isn’t in GROUP BY
  8. MySQL: Can’t create table (errno: 150)
  9. Mysql query to dynamically convert rows to columns
  10. Why does MySQL report a syntax error on FULL OUTER JOIN?
  11. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  12. Mixing ANSI 1992 JOINs and COMMAs in a query
  13. SQL: Creating a Relational table with 2 different auto_increment
  14. How do I add a custom CHECK constraint on a MySQL table?
  15. MySQL variable format for a “NOT IN” list of values
  16. Convert partially non-numeric text into number in MySQL query
  17. Order by COUNT per value
  18. Select from one table where not in another
  19. MySQL Select last 7 days
  20. How to select the most recent set of dated records from a mysql table
  21. Second SELECT query if first SELECT returns 0 rows
  22. Set value to NULL in MySQL
  23. SELECT query return 1 row from each group
  24. MySql Single Table, Select last 7 days and include empty rows
  25. mysql SQL: specific item to be first and then to sort the rest of the items
  26. When is a timestamp (auto) updated?
  27. DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
  28. What happens when I exhaust a bigint generated key? How to handle it?
  29. MySQL update statement to store ranking positions
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment