How to Generate an Integer Sequence in MySQL Without Creating a Table?

Generating Integer Sequences in MySQL

Question: How can I generate an integer sequence, inclusive of the start and end values, for use in joins without manually creating a table?

Answer:

The MySQL programming language provides a versatile solution for generating an integer sequence using a single query. The following code snippet demonstrates this approach:

SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r
LIMIT m-n+1 OFFSET n-1;

This query accomplishes the task by:

• Setting the variable @row to 0 initially.
• Using a subquery to increment @row by 1 for each row in the some_table.
• Applying a limit clause to specify the desired range of integers (n to m).

This solution is efficient and straightforward, providing the desired integer sequence without the need for a separate table.

The above is the detailed content of How to Generate an Integer Sequence in MySQL Without Creating a Table?. For more information, please follow other related articles on the PHP Chinese website!