I now manage a MySQL database that was designed and created by another.

I am confused about a certain setup that the original developer created..

He did not use auto increments for table IDs. Instead, he created a table called system_sequences which has two columns:


Here is an example of a table and how it currently inserts into the table (try my best):

there is a table called customer with an unique ID column.

If I want to insert into customer, currently I have to do the following:

SELECT system_sequences.Next_Value 
FROM system_sequences 
WHERE system_sequences.Table_Name = 'customer'

save the next_value into a variable and use it as the next ID for customer

then I have to:

UPDATE system_sequences 
SET Next_Value = Next_Value + 1 
WHERE system_sequences.Table_Name = 'customer'

I did not design this, I do not like it in any way shape or form ( I actually hate it ) and I want to convert everything carefully to auto_increment

But is there a reason for this or a benefit that I am not aware of? I have never seen anything like it before. And there is no documentation for this database that explains anything.

  • 1
    How do you stop two concurrent transactions both reading the same next value? – Martin Smith Mar 16 '12 at 15:50
  • @MartinSmith I have no idea .. – Jeff Mar 16 '12 at 15:50
  • 1
    In MySQL I really have no idea. In MS SQL Server, one could specify exclusive row locks to be held to the end of the transaction on a table hint on the above SELECT query and no one will be able to read that record until the txn ends. – Fabricio Araujo Mar 26 '12 at 17:39
  • @FabricioAraujo: I'd expect to need a transaction wrapping all the statements that select the next value, insert into the target table, and update the next value. Docs for serializable transactions You can also lock tables in MySQL. – Mike Sherrill 'Cat Recall' Jul 31 '12 at 1:12

The original designer probably had some kind over quick overview of DB resources in mind.

I know Oracle supports a database object called a SEQUENCE that that by just summoning the next value, the auto_increment is performed with the sequence object.

You create it like this:


The first reference to eseq.nextval returns 1.

Again, just call eseq.nextval in your trigger and you are assured a safe number to use each time. MySQL has nothing like this. The original designer may have had this in mind and simply emulated it in light of MySQL not having database objects like an Oracle Sequence.

I can also assume that there is no mechanism or infrastructure for collecting and reusing ids that have been deleted, thus leaving gaps in ids.

Looking at the system resource spec you mentioned, you could guess at how many rows were in a table instead of running SELECT COUNT(*) FROM customers;.

If I had to guess why this was done, my guess would be that the designer did not know about or trust in MySQL having an INFORMATION_SCHEMA database. MySQL has had INFORMATION_SCHEMA database since the inception of MySQL 5.0. All versions prior to MySQL 5.0 do not have an INFORMATION_SCHEMA database.


I don't know about mysql but in SQL server, you would use this type of thing when you can't afford to have gaps in the sequence from rollbacks. Often this is a requirement for invoice numbering for accounting auditing.

  • Yep, IDENTITY columns in SQL Server aren't guaranteed to generate gapless sequences. I bet the same goes for MySQL's auto_increment. – Nick Chammas Mar 16 '12 at 15:25
  • I think that transactions even if rolled back still increment the id by 1, but we don't have anything that would need to be that strict in the database. Maybe he was moving in that direction though for other systems that where planned. – Jeff Mar 16 '12 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.