Oracle CONNECT BY LEVEL Alternatives in MySQL

In the world of Oracle databases, CONNECT BY LEVEL is a well-known "magic spell" that easily generates numeric sequences or multiple rows of data. However, when migrating code to MySQL, this convenient feature becomes a slight challenge.

In Oracle, generating a sequence of numbers from 1 to 50 is as simple as:

SELECT LEVEL LV FROM dual CONNECT BY LEVEL <= 50;

‍

The execution result is clean and straightforward—a sequence from 1 to 50 appears instantly. This concise syntax makes data generation effortless, whether for test data construction, sequence generation, or data pivoting.

MySQL’s Workarounds: Diverse Solutions
MySQL 8.0+: Elegant Recursive CTE Approach
For users fortunate enough to use MySQL 8.0+, recursive Common Table Expressions (CTEs) provide an elegant solution comparable to Oracle:

WITH RECURSIVE numbers AS (
    SELECT 1 AS lv
    UNION ALL
    SELECT lv + 1
    FROM numbers
    WHERE lv < 50
)
SELECT lv FROM numbers;

‍

This method is not only highly readable but also logically corresponds to Oracle’s original syntax, making it the preferred choice for version-compatible scenarios.

MySQL 5.7: Traditional Workarounds
For those still using MySQL 5.7, the path is slightly more diff but still achievable with several methods:

Method 1: Cartesian Product Join

SELECT @row := @row + 1 AS lv
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
     (SELECT 1 UNION SELECT 2) t3,
     (SELECT @row := 0) r
LIMIT 50;

‍

This approach uses the Cartesian product of multiple subqueries to generate enough rows, then employs variable incrementation to create the sequence. 5×5×2=50, perfectly meeting our needs.

Method 2: Window Function Approach

SELECT (ROW_NUMBER() OVER()) AS lv 
FROM information_schema.columns 
LIMIT 50;

‍

This method leverages existing data in the information_schema system view and uses window functions to generate row numbers—concise and effective.

Practical Applications
These sequence generation techniques are widely used in real-world development, such as:

• Generating date sequences for reports
• Creating test data quickly
• Simulating pagination with page number sequences

Technical Recommendations:

• New projects: Prioritize MySQL 8.0+ to leverage recursive CTEs.
• Legacy system migration: Choose the appropriate workaround based on the MySQL version.
• Performance considerations: Recursive CTEs may have limitations when generating large datasets; testing is advised.

Conclusion
Although MySQL does not directly offer CONNECT BY LEVEL syntax, we can still elegantly solve sequence generation problems through various technical combinations. This reflects the常态 of the programming world: there are many ways to reach the goal, and the key is choosing the one best suited to the current environment.

Mastering these cross-database migration techniques not only enhances our technical adaptability but also deepens our understanding of the essence of SQL!