Creating and Implementing Custom Sequences in MySQL 8

In MySQL, a table-based sequence is used to generate a sequence of numbers, typically for auto-incrementing primary keys. However, unlike some other databases, MySQL does not have a built-in SEQUENCE object. Instead, you can simulate a sequence using a table. Here’s how you can accomplish this in MySQL 8:

Step 1: Create a Sequence Table

First, you need to create a table specifically for maintaining the sequence:

Step 2: Create a Function to Get the Next Value

To simulate getting the next value from the sequence, create a stored function:

This function inserts a new row into my_sequence and returns the LAST_INSERT_ID(), which is the latest auto-increment value generated.

Step 3: Using the Sequence in Your Tables

Whenever you need a new sequence number (for example, for a primary key in another table), you call the get_next_sequence() function:

Advantages and Disadvantages

  • Advantages:
    • Flexibility: You can use this approach for any table where you need a unique sequence.
    • Simplicity: Easy to implement without requiring special database features.
  • Disadvantages:
    • Performance: This method involves an extra table insert, which may be less efficient than a built-in sequence generator.
    • Scalability: The function’s reliance on LAST_INSERT_ID() means it’s not suitable for distributed databases where different nodes need to generate unique IDs independently.

Alternative: Auto-Increment Columns

For many use cases, a simple auto-increment column in your main table might suffice. MySQL ensures that each new row gets a unique, incrementing ID:

Then, when inserting data, omit the id column, and MySQL will automatically fill it:

Conclusion

While MySQL doesn’t have a built-in sequence generator as some other databases do, you can simulate this functionality with a dedicated table and a stored function. This method can be useful for specific use cases but consider its performance and scalability characteristics in your specific context. For simpler needs, the built-in auto-increment functionality is usually sufficient and more efficient.

About Shiv Iyer 452 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.