How do I make a row generator in MySQL?

by

Hate to say this, but MySQL is the only RDBMS of the big four that doesn’t have this feature.

In Oracle:

SELECT  *
FROM    dual
CONNECT BY
        level < n

In MS SQL (up to 100 rows):

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < n
        )
SELECT  *
FROM    hier

or using hint up to 32768

WITH hier(row) AS
        (
        SELECT  1
        UNION ALL
        SELECT  row + 1
        FROM    hier
        WHERE   row < 32768
        )
SELECT  *
FROM    hier
OPTION (MAXRECURSION 32767) -- 32767 is the maximum value of the hint

In PostgreSQL:

SELECT  *
FROM    generate_series (1, n)

In MySQL, nothing.

More Related Contents:

  1. MySQL Insert query doesn’t work with WHERE clause
  2. Delete from two tables in one query
  3. Why would an IN condition be slower than “=” in sql?
  4. PostgreSQL GROUP BY different from MySQL?
  5. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  6. MySQL dump by query
  7. SQLite – ORDER BY RAND()
  8. Delete sql rows where IDs do not have a match from another table
  9. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  10. What does DELIMITER // do in a Trigger?
  11. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  12. MySQL equivalent of DECODE function in Oracle
  13. How do you version your database schema? [closed]
  14. MySQL, Get users rank
  15. Mysql: how to select groups having certain values?
  16. How to fill date gaps in MySQL?
  17. Adding a new SQL column with a default value
  18. Boolean vs tinyint(1) for boolean values in MySQL
  19. MySQL full text search for words with three or less letters
  20. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  21. Function for week of the month in mysql
  22. MySQL’s alternative to T-SQL’s WITH TIES
  23. Find rows that have the same value on a column in MySQL
  24. what is the difference between GROUP BY and ORDER BY in sql
  25. How to transform vertical data into horizontal data with SQL?
  26. How important are lookup tables?
  27. How to find all IDs of children recursively?
  28. how to use LIKE with column name
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment