如何通过查询自动设置跨表唯一ID，替代AUTO_INCREMENT

原来的做法把INSERT和UPDATE拆成两个独立操作，不具备原子性，高并发场景下多个会话会同时读到同一个next_id值，最终必然出现ID重复的问题。要实现像AUTO_INCREMENT那样自动生成ID，核心是把获取ID和更新计数器的操作绑定成原子操作，再用触发器自动填充ID字段，具体步骤如下：

1. 保留计数器表结构 #

计数器表沿用你原来的设计即可，后续通过原子操作来调用它：

CREATE TABLE next_id (id INT NOT NULL);
INSERT INTO next_id VALUES (1);

2. 编写原子获取ID的存储函数 #

创建一个存储函数，将“获取ID”和“更新计数器”合并为原子操作，彻底避免并发冲突：

DELIMITER //
CREATE FUNCTION get_next_global_id() RETURNS INT
BEGIN
    DECLARE new_id INT;
    -- 先更新计数器，再取更新前的数值作为新ID，全程原子执行
    UPDATE next_id SET id = id + 1;
    SELECT id - 1 INTO new_id FROM next_id;
    RETURN new_id;
END //
DELIMITER ;

不同数据库语法有差异，比如PostgreSQL可以直接用UPDATE ... RETURNING一步拿到新ID，写法更简洁。

3. 为每个对象表添加触发器 #

给object1和object2分别创建BEFORE INSERT触发器，插入数据时自动调用函数填充ID字段：

为object1创建触发器： #

DELIMITER //
CREATE TRIGGER trigger_object1_auto_id
BEFORE INSERT ON object1
FOR EACH ROW
BEGIN
    SET NEW.id = get_next_global_id();
END //
DELIMITER ;

为object2创建触发器： #

DELIMITER //
CREATE TRIGGER trigger_object2_auto_id
BEFORE INSERT ON object2
FOR EACH ROW
BEGIN
    SET NEW.id = get_next_global_id();
END //
DELIMITER ;

4. 测试自动插入效果 #

现在就可以像使用自增ID一样插入数据，完全不需要手动指定ID：

INSERT INTO object1 (mass) VALUES (5);
INSERT INTO object2 (angles) VALUES (3);

查询两张表会看到ID连续且无重复：

SELECT * FROM object1; -- 输出 id=1, mass=5
SELECT * FROM object2; -- 输出 id=2, angles=3

更简洁的数据库特定方案 #

如果使用PostgreSQL，直接用全局序列就能实现，比触发器方案更省事：

CREATE SEQUENCE global_id_seq START WITH 1;

CREATE TABLE object1 (
    id INT NOT NULL PRIMARY KEY DEFAULT nextval('global_id_seq'),
    mass INT NOT NULL
);

CREATE TABLE object2 (
    id INT NOT NULL PRIMARY KEY DEFAULT nextval('global_id_seq'),
    angles INT NOT NULL
);

插入时同样无需指定ID，序列会自动生成全局唯一的ID值。

注意事项 #

• 要确保存储函数和触发器的权限配置正确，否则会出现执行失败的情况。
• 高并发场景下，单计数器表可能成为性能瓶颈，这时可以考虑给不同表分配固定ID段，或者用UUID作为全局ID，但UUID的索引性能不如自增ID。

内容的提问来源于stack exchange，提问作者ShkiperDesna