Faking Auto Increment Increment on a Table in MySQ

Question

I have a content table in my MySQL database (a Drupal content table, for what it's worth), which has an auto incremented primary key, nid. I want to be able to implement an odd and even id solution, where content created on production has even ids and content created on dev has odd ids, to avoid id conflicts when I sync. Unfortunately, MySQL doesn't support sequences, or per-table auto increment increment values (i.e. increment by 2 only for db.node, rather than 1).

The best solution I can think of, is to have a BEFORE INSERT and AFTER INSERT trigger which sets the session value of auto_increment_increment to 2 in the BEFORE INSERT trigger, and then resets it to 1 in the AFTER INSERT trigger. Since it only sets the session variable, it shouldn't have any effect on other processes, and since it's a Drupal CMS table and nothing complicated is happening, it seems safe, even though it feels wrong.

However, I'm an intermediate MySQL Admin (at best :) ) and as I said it certainly feels hackish, so I thought I'd put this out there and see if anyone has any strong negative reactions to this, perhaps some issue I'm not foreseeing. ( And I suppose if no one does then maybe someone else will find this useful).

Answer 1

Here's a simple example of what you want to do - assuming there is an integer column 'seq' in the 'my_table_name' table:

DROP trigger my_trigger_name;  

CREATE TRIGGER my_trigger_name
BEFORE INSERT ON my_table_name
FOR EACH ROW
SET NEW.seq = (select ifnull(max(seq)+1,1) from source_table_name);