MySQL Sequences

Overview

Sequences are ordered lists of numbers typically used for generating unique identifiers. MySQL doesn't have a native SEQUENCE object, but you can implement sequences using AUTO_INCREMENT or alternative methods.

Sequence Methods

• AUTO_INCREMENT: Primary key auto-increment
• Custom Sequence Table: User-defined sequence table
• Functions: LAST_INSERT_ID(), etc.
• Triggers: Generate sequence values

AUTO_INCREMENT / AUTO_INCREMENT

Basic AUTO_INCREMENT

-- Create table with auto increment
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255)
);

-- Insert and auto ID assigned
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

-- Get last insert ID
SELECT LAST_INSERT_ID();

AUTO_INCREMENT Options

-- Set starting value
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(50)
) AUTO_INCREMENT = 1000;

-- Modify starting value
ALTER TABLE orders AUTO_INCREMENT = 5000;

-- Reset auto increment
ALTER TABLE orders AUTO_INCREMENT = 1;

-- Check next value
SELECT AUTO_INCREMENT 
FROM information_schema.TABLES 
WHERE table_name = 'orders';

AUTO_INCREMENT Gaps

-- Auto increment creates gaps when records deleted
-- Example:
-- Records: 1, 2, 3, 4, 5
-- Delete 3, 4
-- Next insert: 6 (not 3)

INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com');

-- Get gaps
SELECT id FROM users 
WHERE id NOT IN (
    SELECT MIN(id) FROM users AS u2 WHERE u2.id >= users.id
    AND NOT EXISTS (SELECT 1 FROM users AS u3 WHERE u3.id BETWEEN users.id AND u2.id)
);

Custom Sequence Table

Create Sequence Table

-- Create sequence table
CREATE TABLE sequences (
    sequence_name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT NOT NULL,
    increment_by BIGINT NOT NULL DEFAULT 1,
    INDEX idx_sequence_name (sequence_name)
);

-- Initialize sequences
INSERT INTO sequences VALUES ('user_id', 1, 1);
INSERT INTO sequences VALUES ('order_id', 1000, 1);
INSERT INTO sequences VALUES ('invoice_id', 5000, 1);

Get Next Sequence Value

-- Method 1: UPDATE and SELECT (simple)
-- Get next value
UPDATE sequences 
SET current_value = current_value + increment_by 
WHERE sequence_name = 'user_id';

SELECT current_value FROM sequences 
WHERE sequence_name = 'user_id';

-- Method 2: Transaction-safe method
START TRANSACTION;

SELECT current_value INTO @seq_value
FROM sequences 
WHERE sequence_name = 'user_id' FOR UPDATE;

UPDATE sequences 
SET current_value = @seq_value + increment_by 
WHERE sequence_name = 'user_id';

SELECT @seq_value AS sequence_value;

COMMIT;

Stored Procedure for Sequence

DELIMITER //
CREATE PROCEDURE nextval(IN seq_name VARCHAR(50), OUT next_val BIGINT)
BEGIN
    DECLARE increment_val BIGINT;
    
    SELECT increment_by INTO increment_val
    FROM sequences
    WHERE sequence_name = seq_name
    FOR UPDATE;
    
    UPDATE sequences
    SET current_value = current_value + increment_val
    WHERE sequence_name = seq_name;
    
    SELECT current_value INTO next_val
    FROM sequences
    WHERE sequence_name = seq_name;
END //
DELIMITER ;

-- Use procedure
CALL nextval('user_id', @value);
SELECT @value;

-- Multiple calls
CALL nextval('order_id', @order_num);
CALL nextval('order_id', @order_num);
CALL nextval('order_id', @order_num);

Sequence Functions

LAST_INSERT_ID() / LAST_INSERT_ID()

-- Get last auto increment ID
INSERT INTO users (name) VALUES ('John');
SELECT LAST_INSERT_ID();

-- Per connection
-- Each connection maintains its own value

-- Use in transactions
START TRANSACTION;
INSERT INTO orders (user_id) VALUES (LAST_INSERT_ID());
COMMIT;

Generating Sequences

-- Generate sequence in query
SELECT id, name
FROM (
    SELECT 1 AS id, 'Item 1' AS name
    UNION SELECT 2, 'Item 2'
    UNION SELECT 3, 'Item 3'
    UNION SELECT 4, 'Item 4'
) AS items;

-- Generate sequence with variables
SET @row_num = 0;
SELECT @row_num := @row_num + 1 AS row_number, name
FROM products
ORDER BY id;

Advanced Sequences

Composite Sequences

-- Create sequence with multiple components
CREATE TABLE composite_sequences (
    sequence_name VARCHAR(50),
    sequence_date DATE,
    current_value INT,
    PRIMARY KEY (sequence_name, sequence_date)
);

-- Initialize daily sequences
INSERT INTO composite_sequences 
SELECT 'order_id', CURDATE(), 1000;

-- Get today's sequence
UPDATE composite_sequences 
SET current_value = current_value + 1 
WHERE sequence_name = 'order_id' 
  AND sequence_date = CURDATE();

SELECT current_value 
FROM composite_sequences 
WHERE sequence_name = 'order_id' 
  AND sequence_date = CURDATE();

Sequence with Prefix

-- Combine sequence with prefix
SELECT 
    CONCAT('ORD-', LPAD(@order_num, 6, '0')) AS order_number;

-- Store as string
CREATE TABLE orders (
    order_number VARCHAR(20) PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
);

-- Insert with sequence
CALL nextval('order_id', @seq);
INSERT INTO orders (order_number, customer_id, total)
VALUES (
    CONCAT('ORD-', LPAD(@seq, 6, '0')),
    1,
    99.99
);

Sequence Performance

Optimizing Sequences

-- Use appropriate data types
-- INT: Up to 2 billion
-- BIGINT: For very large sequences

CREATE TABLE sequences (
    sequence_name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT,
    INDEX idx_seq_name (sequence_name)
);

-- Use LOCK IN SHARE MODE
SELECT current_value INTO @val
FROM sequences 
WHERE sequence_name = 'user_id' 
FOR UPDATE;

-- Minimize lock time
UPDATE sequences 
SET current_value = current_value + 1 
WHERE sequence_name = 'user_id';

Sequence Caching

-- Cache multiple sequence values
CREATE TABLE sequence_cache (
    cache_name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT,
    cached_values INT,
    cache_size INT DEFAULT 100
);

-- Get cached values
START TRANSACTION;

SELECT current_value, cache_size INTO @start_val, @cache_size
FROM sequence_cache
WHERE cache_name = 'user_id' FOR UPDATE;

-- Update to next cache start
UPDATE sequence_cache
SET current_value = current_value + cache_size,
    cached_values = cache_size
WHERE cache_name = 'user_id';

COMMIT;

-- Use cached values in application
-- @start_val to @start_val + @cache_size - 1

Practical Examples

Example 1: Order Numbers

-- Generate formatted order numbers
CREATE PROCEDURE create_order(
    IN customer_id INT,
    IN total DECIMAL(10,2),
    OUT order_number VARCHAR(20)
)
BEGIN
    DECLARE seq_val BIGINT;
    
    -- Get sequence
    CALL nextval('order_id', seq_val);
    
    -- Format: ORD-YYYYMM-XXXXX
    SET order_number = CONCAT(
        'ORD-',
        DATE_FORMAT(CURDATE(), '%Y%m'),
        '-',
        LPAD(seq_val % 100000, 5, '0')
    );
    
    -- Insert order
    INSERT INTO orders (order_number, customer_id, total)
    VALUES (order_number, customer_id, total);
    
    SELECT order_number;
END //
DELIMITER ;

-- Use procedure
CALL create_order(1, 199.99, @order_num);
SELECT @order_num;

Example 2: Invoice Numbers

-- Year-based sequence
CREATE TABLE yearly_sequences (
    sequence_name VARCHAR(50),
    year INT,
    current_value INT,
    PRIMARY KEY (sequence_name, year)
);

-- Initialize for current year
INSERT INTO yearly_sequences 
SELECT 'invoice_id', YEAR(CURDATE()), 1000;

-- Generate invoice number
SELECT 
    CONCAT('INV-', year, '-', LPAD(current_value, 6, '0')) AS invoice_number
FROM yearly_sequences 
WHERE sequence_name = 'invoice_id' AND year = YEAR(CURDATE());

Example 3: Batch Sequence Generation

-- Generate multiple sequence values
CREATE TEMPORARY TABLE temp_sequence (
    id INT,
    generated_id INT
);

-- Generate sequence for 100 items
INSERT INTO temp_sequence
SELECT 
    id,
    (SELECT current_value + id 
     FROM sequences 
     WHERE sequence_name = 'user_id') AS generated_id
FROM (
    SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -- ... up to 100
) AS numbers;

-- Update sequence
UPDATE sequences
SET current_value = current_value + 100
WHERE sequence_name = 'user_id';

-- Use generated sequence
INSERT INTO items (id, name)
SELECT generated_id, CONCAT('Item ', id) 
FROM temp_sequence;

Sequence Triggers

Auto-Increment with Triggers

-- Create custom sequence table
CREATE TABLE custom_sequence (
    name VARCHAR(50) PRIMARY KEY,
    value BIGINT NOT NULL
);

INSERT INTO custom_sequence VALUES ('custom_id', 1);

-- Create table without AUTO_INCREMENT
CREATE TABLE items (
    id BIGINT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

-- Trigger to generate ID
DELIMITER //
CREATE TRIGGER before_items_insert
BEFORE INSERT ON items
FOR EACH ROW
BEGIN
    DECLARE next_val BIGINT;
    
    SELECT value + 1 INTO next_val
    FROM custom_sequence
    WHERE name = 'custom_id';
    
    UPDATE custom_sequence
    SET value = next_val
    WHERE name = 'custom_id';
    
    SET NEW.id = next_val;
END //
DELIMITER ;

-- Insert items
INSERT INTO items (name) VALUES ('Item 1');
INSERT INTO items (name) VALUES ('Item 2');

Sequence Comparison

AUTO_INCREMENT vs Custom Sequence

| Feature |----------------|---------------|-----------------| | Simplicity | Gaps | Multiple tables | Transactions | Performance

Best Practices

Choosing Sequence Method

-- Use AUTO_INCREMENT when:
-- - Simple sequential IDs needed
-- - Each table has own sequence
-- - Gaps are acceptable

-- Use custom sequence when:
-- - Shared sequence across tables
-- - Need specific format (ORD-XXXX)
-- - Need to control gaps
-- - Need year/month-based sequences

Error Handling

-- Safe sequence procedure
DELIMITER //
CREATE PROCEDURE safe_nextval(
    IN seq_name VARCHAR(50),
    OUT next_val BIGINT
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET next_val = NULL;
    END;
    
    START TRANSACTION;
    
    SELECT current_value INTO next_val
    FROM sequences
    WHERE sequence_name = seq_name FOR UPDATE;
    
    IF next_val IS NOT NULL THEN
        UPDATE sequences
        SET current_value = current_value + increment_by
        WHERE sequence_name = seq_name;
        
        COMMIT;
    END IF;
END //
DELIMITER ;

Summary

MySQL sequences provide:

• AUTO_INCREMENT: Simple, per-table sequences
• Custom Sequences: Flexible, shared across tables
• Functions: LAST_INSERT_ID() for auto-increment
• Procedures: Encapsulate sequence logic
• Triggers: Auto-generate IDs

Choose appropriate method based on your requirements for unique identifiers.

---

Previous: Metadata

Next: Handle Duplicates