1. Introduction — What is a Sequence?

Imagine you’re at a movie theatre ticket counter. Each customer gets a unique ticket number — 1, 2, 3, 4… This numbering happens automatically and ensures no two customers have the same ticket number.

In databases, a sequence works exactly like that.
It generates unique, ordered numbers that can be used for:

Order IDs

Invoice Numbers

Ticket Numbers

Customer IDs

Any field that needs unique incremental values

2. MySQL and Sequences — The Basics

Before MySQL 8.0.18

MySQL did not have a dedicated sequence object. Developers relied on:

AUTO_INCREMENT columns

A manual sequence table with custom logic

From MySQL 8.0.18 Onwards

MySQL supports a CREATE SEQUENCE feature, similar to Oracle or PostgreSQL, allowing a shared, independent number generator.

3. AUTO_INCREMENT vs SEQUENCE

Feature	AUTO_INCREMENT	SEQUENCE
Bound to a specific table	✅ Yes	❌ No (independent object)
Can be shared across multiple tables	❌ No	✅ Yes
Manual fetch possible	❌ No	✅ Yes (`NEXT VALUE FOR`)
Flexibility in restart/increment	Limited	Full control

4. Real-Time Use Case

In an eForm2290 trucking application, you may have:

Orders Table

Invoices Table

Receipts Table

All three need unique IDs that are consistent across the system.

Using AUTO_INCREMENT, each table starts its own count — messy for cross-referencing.
Using SEQUENCE, you have one number generator shared across all tables.

5. Step-by-Step MySQL Implementation

Step 1 — Create a Sequence

sqlCopyEditCREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999999
    NOCYCLE
    CACHE 10;

For Non-Tech:
This is like setting up a ticket machine starting from ticket #1000, increasing by 1 each time.

Step 2 — Create a Table Using the Sequence

sqlCopyEditCREATE TABLE orders (
    order_id BIGINT PRIMARY KEY DEFAULT (NEXT VALUE FOR order_seq),
    customer_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

For Non-Tech:
This tells MySQL:

“If you don’t provide an order number, pick the next ticket from the ticket machine (order_seq).”

Step 3 — Insert Data Without Providing IDs

sqlCopyEditINSERT INTO orders (customer_name, amount)
VALUES ('John Doe', 150.75),
       ('Jane Smith', 299.99);

Result:

order_id	customer_name	amount	order_date
1000	John Doe	150.75	2025-08-12 12:30:00
1001	Jane Smith	299.99	2025-08-12 12:31:05

Step 4 — Use Same Sequence in Multiple Tables

sqlCopyEditCREATE TABLE invoices (
    invoice_no BIGINT PRIMARY KEY DEFAULT (NEXT VALUE FOR order_seq),
    description VARCHAR(200)
);

CREATE TABLE receipts (
    receipt_no BIGINT PRIMARY KEY DEFAULT (NEXT VALUE FOR order_seq),
    payment_method VARCHAR(50)
);

Now:

orders ID = 1000, 1001…

invoices ID continues = 1002, 1003…

receipts ID continues = 1004, 1005…

Step 5 — Reset the Sequence

sqlCopyEditALTER SEQUENCE order_seq RESTART WITH 5000;

Next generated number will be 5000.

6. Java Integration with MySQL Sequence

6.1 Fetching Sequence Value in Java

javaCopyEditimport java.sql.*;

public class SequenceExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "root";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "SELECT NEXT VALUE FOR order_seq AS next_id";
            try (PreparedStatement ps = conn.prepareStatement(sql);
                 ResultSet rs = ps.executeQuery()) {

                if (rs.next()) {
                    long nextId = rs.getLong("next_id");
                    System.out.println("Next Sequence Value: " + nextId);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

📌 What this does:

Connects to MySQL

Fetches the next sequence number

Can be used in Java before inserting a record

6.2 Inserting Directly Using Sequence in Java

javaCopyEditimport java.sql.*;

public class InsertWithSequence {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_db";
        String user = "root";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "INSERT INTO orders (order_id, customer_name, amount) " +
                         "VALUES (NEXT VALUE FOR order_seq, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                ps.setString(1, "Michael Scott");
                ps.setBigDecimal(2, new java.math.BigDecimal("500.00"));
                ps.executeUpdate();
                System.out.println("Order inserted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

📌 What this does:

Uses NEXT VALUE FOR order_seq directly in the insert query

No need to fetch sequence value separately

7. For Older MySQL Versions (<8.0.18)

If you don’t have CREATE SEQUENCE, use a sequence table:

sqlCopyEditCREATE TABLE sequence_table (
    name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT
);

INSERT INTO sequence_table VALUES ('order_seq', 999);

UPDATE sequence_table
SET current_value = LAST_INSERT_ID(current_value + 1)
WHERE name = 'order_seq';

SELECT LAST_INSERT_ID();

This must be wrapped in transactions in Java to avoid duplicate numbers.

8. Benefits of Using Sequences

✅ Centralized numbering across multiple tables
✅ Easy to reset or adjust
✅ Avoids duplication
✅ Useful in distributed/microservices systems

9. Final Thoughts

If you’re on MySQL 8.0.18+, always use CREATE SEQUENCE for flexibility

For single-table IDs, AUTO_INCREMENT is still simpler

In Java, you can either fetch the sequence first or use it directly in insert queries

Sequences make your system’s numbering clean, unique, and future-proof