Generating Numeric Sequences in MySQL: A Comprehensive Guide

Numeric sequences are fundamental building blocks in database management, serving as unique identifiers, counters, and data generation tools. Whether you need to create sequential IDs, generate test data, or fill gaps in your datasets, MySQL offers several powerful methods for generating numeric sequences. This comprehensive guide explores the most effective approaches, from simple AUTO_INCREMENT columns to advanced recursive Common Table Expressions.

Method 1: Using AUTO_INCREMENT (The Simplest Approach) in MySQL

The easiest way to generate a sequence in MySQL is by adding the AUTO_INCREMENT attribute to a column during table creation. This method is perfect for primary keys and provides automatic sequence generation with minimal setup.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (username, email) VALUES 
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com');

-- The id column will automatically generate values: 1, 2, 3...

Key Benefits:

• Zero configuration required – MySQL handles everything automatically
• Thread-safe – Multiple concurrent inserts work seamlessly
• Performance optimized – Built into MySQL’s core functionality

Limitations:

• Limited to table columns – Cannot generate standalone sequences
• Single sequence per table – Each AUTO_INCREMENT column maintains its own sequence
• Not suitable for temporary sequences or complex sequence logic

Method 2: Generating Sequences with User-Defined Variables in MySQL

For more flexibility in generating numeric ranges, MySQL allows you to create sequences using user-defined variables and joins. This method is particularly useful when you need to generate a specific range of numbers for data analysis or reporting.

-- Generate numbers from 1 to 10
SELECT @row_number := @row_number + 1 AS sequence_number
FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) t1
CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) t2
CROSS JOIN (SELECT @row_number := 0) r
LIMIT 10;

This approach works across all MySQL versions and provides good flexibility for generating custom ranges, though it requires more complex SQL syntax.

Method 3: Leveraging Recursive Common Table Expressions (MySQL 8.0+)

Recursive CTEs represent the most powerful and elegant solution for generating numeric sequences in modern MySQL versions. This method closely mimics PostgreSQL’s generate_series() function and offers unmatched flexibility.

-- Generate a sequence from 1 to 100
WITH RECURSIVE sequence_cte AS (
    -- Anchor member: starting point
    SELECT 1 AS n

    UNION ALL

    -- Recursive member: increment logic
    SELECT n + 1
    FROM sequence_cte
    WHERE n < 100
)
SELECT n FROM sequence_cte;

Advanced Example with Custom Step:

-- Generate even numbers from 2 to 20
WITH RECURSIVE even_sequence AS (
    SELECT 2 AS n

    UNION ALL

    SELECT n + 2
    FROM even_sequence
    WHERE n < 20
)
SELECT n FROM even_sequence;

Why Recursive CTEs Excel:

• Highly readable syntax – Clear separation of initialization and increment logic
• Flexible parameters – Easy to modify start, end, and step values
• Standard SQL compliance – Similar to other database systems
• No temporary tables required – Everything happens in memory

Method 4: Creating a Dedicated Sequence Table

For applications requiring multiple independent sequences or compatibility with older MySQL versions, creating a dedicated sequence table remains a viable option.

-- Create a sequence table
CREATE TABLE sequences (
    name VARCHAR(50) PRIMARY KEY,
    current_value INT NOT NULL DEFAULT 0
);

-- Initialize sequences
INSERT INTO sequences (name, current_value) VALUES 
('order_sequence', 1000),
('invoice_sequence', 5000);

-- Function to get next sequence value
DELIMITER //
CREATE FUNCTION get_next_sequence(seq_name VARCHAR(50)) 
RETURNS INT
READS SQL DATA
MODIFIES SQL DATA
BEGIN
    DECLARE next_val INT;

    UPDATE sequences 
    SET current_value = current_value + 1 
    WHERE name = seq_name;

    SELECT current_value INTO next_val 
    FROM sequences 
    WHERE name = seq_name;

    RETURN next_val;
END //
DELIMITER ;

Further Reading

• Best Practices for Managing MongoDB Log Files and System Resources
• An Overview of DDL Algorithms in MySQL 8: Enhancing Schema Changes
• MongoDB Wire Protocol: Structure, Evolution, and Advantages
• PostgreSQL 16 for DBAs: Essential Features and Practical Implementation Guide
• PostgreSQL DELETE vs TRUNCATE: A Complete Guide to Data Removal Commands
• Generating a range of numbers in MySQL