Faking Auto Increment Increment on a Table in MySQL Using Trigger

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).

Have you seen this:? stackoverflow.com/questions/2105719/… — ypercubeᵀᴹ
Thanks for the link, I checked it out and I feel fortunate I'm not (yet) in a replication situation, looks complicated :). I am hoping to avoid changing the auto_increment_increment globally, although that surely would work and be the safest. I'm using Drupal for a front end CMS, but am doing some analytics stuff "behind" Drupal so to speak, and want to keep the standard settings there. It may come to that, but I'm hoping this trigger solution is viable. Thanks again. — Isaac

nori nori · Accepted Answer · 2012-05-02T11:17:20

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);

Faking Auto Increment Increment on a Table in MySQL Using Trigger

1 Answers