Strategies for Handling non auto incrementing IDs

Question

Someone recently suggested that I remove an auto incrementing table used solely for storing IDs. I have not yet gone with this, I'm just exploring whether it is in fact a better solution than what I currently have. This would leave me with a table like this:

create table tag_translations (
  tag_id int not null,
  language_id int not null,
  tag_name varchar(255),
  primary key (tag_id, language_id)
);

I'm going to have duplicates for tag_id, storing translations of the tag in other languages.

When adding new tags, I need to forgo using auto increment on the tag_id, and instead assign new ID's manually. Unless it's just a translation of an existing tag, the ID needs to be unique for the new batch of translated inserts.

Can someone explain to me, in plain English, how this is typically done? I thought on this, but it doesn't seem to be any cleaner than my previous approach, if I have the thinking right. Here's what I'm assuming the process is:

• Select tag_id from tag_translations
• Pick the highest number in the result set + 1
• Make a new query (for insertion)
• Define some additional strategy for ensuring that ids for new tag_id records are never duplicated when tags get created at more or less the same microsecond

If this is the process, I think I'm better off sticking with my existing schema of having an additional table to auto increment ids. I still have to do an additional query to first check for a unique id (I'm trading a single join down the road for an insert today). If the headache of keeping my IDs unique when they need to be unique is what I think it will be, I may want to abandon this approach and stick with what I've got. Is my thinking sound?

Answer 1

For generating new tag ids, there is a better option than select max(tag_id) + 1. You can mimic sequences/generators in MySQL with a single field and the use of last_insert_id()'s ability to take an argument. The following creates the sequence:

create table tag_id_seq (id int not null default 0);
insert into tag_id_seq values (0);

After creating the sequence, you could get the next id from it with 2 statements like this:

update tag_id_seq set id = last_insert_id(id + 1);
select last_insert_id();

last_insert_id() is connection-specific, so the first statement basically serves to capture a value for only the connection that executed it, as well as update the sequence. The second statement just retrieves the value. If 2 different connections did the update statement very close to each other, they would both still have different ids tucked away in last_insert_id().

You can wrap this up in a function into which you pass merely the sequence name, then call it whenever you are really creating a new tag. This would work much better than your tags table that has the single auto_increment column.

Instead of starting at 0 with the next id being 1, you can bring it up even with currently used ids:

update tag_id_seq set id = (select coalesc(max(tag_id),0) from tag_translations);

There are also variations on the update statement:

• Some like to have the id field represent what the next id should be, rather than what the last id given was. In that case, you would start id as 1 instead of 0 if it's a new sequence, and use set id = last_insert_id(id) + 1 (with the addition outside).
• Also, some circumstances call for 'reserving' several new ids rather than 1 at a time. In that case you would add however many you need. Based on the above variation, let's say the sequence is at 11, meaning the last id retrieved was 10 and 11 is the next id. If you need 7 new ids, you would use set id = last_insert_id(id) + 7. 11 is retreived with select last_insert_id(), meaning you would use ids 11 to 17 (inclusive). The sequence would be updated to 18, the next id that will be retrieved.

Sequences have advantages in many circumstances, and these are a few:

• Compound keys cannot contain an auto_increment column but you still need a way to generate ids.

• The important thing about this style of doing sequences in MySQL is the field. This means you can have the field containing the current/next sequence value almost anywhere.

  For example, instead of having dozens of tables, 1 for each sequence, you can create a sequences table with 1 column for the sequence name and 1 column for the current/next value field. Dozens of rows in 1 table is much tidier:

  create table sequences (
    seqname varchar(50) primary key,
    id int not null default 0);
  

  (And if the table is InnoDB, row-level locking is used rather than table-locking.)

• auto_increment in InnoDB may not behave in a way you like. On startup, it does the equivalent of select max(id)+1 for resetting the counter. That can have the effect of rewinding and re-using ids that had been used previously.