增量表ID字段按位计数 [英] Increment Table ID Field with Bitwise Counting

问题描述

我工作的一个项目，我需要我的ID列是2的幂（ 1,2,4,8,16 .. ）。我知道，我们不能抵消 AUTO_INCREMENT 但对于简单的加法/减法的my.cnf 。

I am working on a project where I need my ID column to be a power of 2 (1,2,4,8,16..). I know that we cannot offset the auto_increment but for simple addition/subtraction in my.cnf.

例如：

id
----
1
2
4
8
16
32
64
128
etc

其中一个想法我是使用自动递增功能的基础上，再创建一个触发器来应用功率的2和更新新的ID，但不幸的是，它不工作：

One of the ideas I had was to use the auto increment functionality as the base, and then create a trigger to apply the power of 2 and update the new ID, but unfortunately, it is not working:

DELIMITER $$
CREATE TRIGGER testbitcompatid BEFORE INSERT ON Table
    FOR EACH ROW 
    BEGIN
        SET NEW.id = pow(NEW.id, 2)
    END;
$$
DELIMITER ;

由于该 BEFORE INSERT 还没有产生的 AUTO_INCREMENT ID 的 AUTO_INCREMENT 总是返回 0 ，实质上造成列上没有任何变化。

Because the BEFORE INSERT has not yet generated the AUTO_INCREMENT id, the AUTO_INCREMENT will always return 0, essentially causing no change on the columns.

我也试过 AFTER INSERT ：

DELIMITER $$
CREATE TRIGGER testbitcompatid AFTER INSERT ON Table
    FOR EACH ROW 
    BEGIN
        SET Table.id = pow(NEW.id, 2) WHERE id = NEW.id;
    END;
$$
DELIMITER ;

但这种失败，因为你不能改变它的触发期间 AFTER INSERT 申请表中的值。

抓我的头，但我相信其他人有实现这一点的一个好方法。

Scratching my head, but I am sure someone else has a great way of accomplishing this.

推荐答案

要解决所有问题上面，我是能够建立伟大的工程下！

To work around all the issues above, I was able to construct the following which works great!

DELIMITER $$
CREATE TRIGGER testbitcompatid BEFORE INSERT ON Table
    FOR EACH ROW 
    BEGIN
        SET @LAST_ROW = (SELECT MAX(id) FROM Table);
        SET NEW.id = CASE WHEN @LAST_ROW IS NULL THEN 1 ELSE @LAST_ROW * 2 END;

    END;
$$
DELIMITER ;

pretty多，我们取最高 ID ，抢日志（2）它这为我们提供了相应的 AUTO_INCREMENT ID 。然后，我们添加 1 和电源，高达 2 。

Pretty much, we take the highest id, grab the log(2) of it which gives us the corresponding AUTO_INCREMENT id. We then add 1, and power that up to 2.

我希望这有助于prevent有些头疼下来别人的道路。

I hope this helps prevent some headache down the road for others.

这篇关于增量表ID字段按位计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！