🔁 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

Behavior	Description
Skips on failed inserts	Failed transactions still increment the counter
Not rolled back on ROLLBACK	Values are consumed even on rollback
Gaps between values	Normal and expected (no guarantees on sequence)

---

🔁 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

Feature	AUTO_INCREMENT	Simulated SEQUENCE Table
Native Support	✅ Yes	❌ Requires manual setup
Rollback Friendly	❌ No	✅ Yes (if wrapped in transaction)
Multi-purpose Use	❌ One column per table	✅ Reusable across tables
Custom logic	❌ Limited	✅ Supports custom increments
Gaps on failure	✅ Yes	❌ Can avoid if coded properly

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Use AUTO_INCREMENT for single-column IDs	Simple and efficient for most tables
Use SEQUENCE logic for multi-table counters	Ensures shared counters (e.g., invoice numbering)
Always retrieve LAST_INSERT_ID() in same session	Prevents confusion when multiple inserts occur
Avoid assuming no gaps	Gaps are expected in both auto-increment and sequences
Use transactions with sequence logic	Prevents race conditions and duplicates

---

🚀 Real-World Use Cases

Scenario	Generator Used	Why
User ID assignment	AUTO_INCREMENT	Each user gets a unique integer ID
Order or invoice numbering	SEQUENCE Table	Shared, customized across modules
External reference IDs	Stored Function or UUID()	Unique IDs for APIs or cross-systems
Ticket numbers	AUTO_INCREMENT	Simple, fast auto-numbering

---

📌 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';

---

Share Now :