使用触发器在MySQL中的表上伪造自动增量 [英] Faking Auto Increment Increment on a Table in MySQL Using Trigger

问题描述

我的MySQL数据库中有一个内容表(一个Drupal内容表，它的价值)，它具有一个自动递增的主键nid.我希望能够实现一个奇数和偶数id解决方案，其中在生产环境中创建的内容具有偶数id，而在dev上创建的内容具有奇数id，以避免在我同步时出现id冲突.不幸的是，MySQL不支持序列或每个表的自动增量增量值(即，仅对db.node增量2，而不是1).

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

我能想到的最佳解决方案是拥有一个BEFORE INSERT和AFTER INSERT触发器，该触发器在BEFORE INSERT触发器中将auto_increment_increment的会话值设置为2，然后在AFTER INSERT触发器中将其重置为1.由于它仅设置会话变量，因此不会对其他进程产生任何影响，并且由于它是Drupal CMS表，并且没有发生任何复杂的事情，因此即使感到不妥，它也似乎很安全.

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.

但是，我是一名中级MySQL Admin(充其量是:))，正如我所说的那样，我确实感觉很hacky，所以我想我应该把它放在那里，看看是否有人对此有强烈的负面反应，也许我无法预见的一些问题. (而且我想如果没有人这样做，也许其他人会发现这很有用).

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

推荐答案

下面是您想做的一个简单示例-假设存在一个整数列'seq' 在"my_table_name"表中:

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

这篇关于使用触发器在MySQL中的表上伪造自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！