MySQL Sequences & Auto Increment – Generating Unique IDs in MySQL

Introduction – Why Use Sequences and Auto-Increment?

In relational databases, every record often needs a unique identifier—for referencing, indexing, and joining. In MySQL:

• AUTO_INCREMENT generates a monotonically increasing value
• MySQL lacks native SEQUENCE objects (like Oracle or PostgreSQL) but provides workarounds
• These tools are essential for creating primary keys, invoice numbers, order IDs, and more

In this guide, you’ll learn:

• How AUTO_INCREMENT works in MySQL
• How to simulate sequences
• Use cases, best practices, and caveats
• Alternatives using stored procedures or custom tables

1. AUTO_INCREMENT – Native Way to Generate IDs

Syntax

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

Example – Insert Rows

INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

id will be auto-assigned as 1, 2, 3…

Retrieve Last Generated ID

SELECT LAST_INSERT_ID();

Returns the last auto-incremented ID for the current session.

Customize Starting Value

ALTER TABLE users AUTO_INCREMENT = 1000;

Useful for starting from a specific offset (e.g., ORD-1000).

AUTO_INCREMENT Caveats

2. Simulating Sequences in MySQL

MySQL doesn’t support CREATE SEQUENCE, but you can simulate it using a custom table or stored procedure.

A. Sequence Table Workaround

CREATE TABLE sequence (
  name VARCHAR(50) PRIMARY KEY,
  current_value BIGINT
);

INSERT INTO sequence VALUES ('invoice', 1000);

Fetch Next Value

UPDATE sequence SET current_value = current_value + 1 WHERE name = 'invoice';
SELECT current_value FROM sequence WHERE name = 'invoice';

Use inside a stored procedure for controlled ID generation.

B. Custom Stored Procedure Sequence

DELIMITER //

CREATE PROCEDURE get_next_invoice_id(OUT next_id INT)
BEGIN
  UPDATE sequence SET current_value = current_value + 1 WHERE name = 'invoice';
  SELECT current_value INTO next_id FROM sequence WHERE name = 'invoice';
END //

DELIMITER ;

Call Example:

CALL get_next_invoice_id(@next);
SELECT @next;

3. AUTO_INCREMENT vs SEQUENCE Table – Comparison

Best Practices

Real-World Use Cases

Summary – Recap & Next Steps

MySQL supports AUTO_INCREMENT for table-specific counters and allows custom sequence logic via manual tables and stored routines for more control.

Key Takeaways

• Use AUTO_INCREMENT for simple primary key generation
• Use LAST_INSERT_ID() to retrieve the new value after insert
• Simulate sequences using a control table or stored procedure
• Gaps are normal—don’t rely on strict numeric continuity
• Use transactions when manually managing sequence values

Real-World Relevance

Used in accounting systems, order processing, inventory control, and ticketing platforms where ID generation and integrity are essential.

FAQ – MySQL Sequences & Auto Increment

Does MySQL support the CREATE SEQUENCE statement?

No. MySQL (even 8.0+) doesn’t support native SEQUENCE objects like PostgreSQL.

Can I reset an auto-increment column?

Yes:

ALTER TABLE users AUTO_INCREMENT = 1;

Or, truncate the table:

TRUNCATE TABLE users;

How do I get the ID generated in a multi-user environment?

Use:

SELECT LAST_INSERT_ID();

It returns the correct value per session.

Can two tables share the same auto-increment sequence?

Not with AUTO_INCREMENT, but possible with a shared sequence table.

Can I create a sequence that increments by 10?

Yes, manually:

UPDATE sequence SET current_value = current_value + 10 WHERE name = 'receipt';