代码之家 › 专栏 › 技术社区 › ShkiperDesna

如何使“id”由查询自动设置,而不是“AUTO_INCREMENT”[重复]

auto-increment mysql sql

-2

ShkiperDesna · 技术社区 · 1 年前

我有多个表,用于不同类型的对象。然而,每个对象的ID不仅在同一类型的对象之间必须是唯一的,而且在不同类型的所有对象之间也必须是独一无二的。换句话说,ID在不同的表中应该是唯一的。为了实现这个概念,我创建了一个带有单个值计数器的特殊表,并修改了INSERT查询以使用它。现在它看起来像这样:

CREATE TABLE next_id (id INT NOT NULL); -- Table-counter
INSERT INTO next_id VALUES (1);         -- The only value that points to the id for the next object

CREATE TABLE object1 (              -- One of object kinds
    id INT NOT NULL PRIMARY KEY,
    mass INT NOT NULL               -- ... Another one specialized for object1 fields
);

CREATE TABLE object2 (              -- Other object kind
    id INT NOT NULL PRIMARY KEY,
    angles INT NOT NULL             -- ... Another one specialized for object2 fields
);

INSERT INTO object1 (id, mass) VALUES (   -- Inserting object1
    (
        SELECT id                         -- Setting it's id by getting it from the counter
        FROM next_id
    ), 5);
UPDATE next_id SET id=id+1;         -- Changing the counter so that the next object has a different id

INSERT INTO object2 (id, angles) VALUES ( -- The same thing, but for other object kind
    (
        SELECT id
        FROM next_id
    ), 3);
UPDATE next_id SET id=id+1;

但它看起来很奇怪,我担心UPDATE语句可能会在以后被规划者执行,从而导致ID冲突和其他问题。可以更改此设置,使ID的行为类似于AUTO_INCREMENT吗?我的意思是,自动设置ID,如下所示:

INSERT INTO object1 (mass) VALUES (5); -- Id is set automatically

1 回复 | 直到 1 年前

-2

Hamdallah 1 年前

您可以使用 Triggers 这样地

首先,我们需要创建一个表 next_id

CREATE TABLE next_id (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

初始化ID表

INSERT INTO next_id VALUES (NULL);

现在为每个表创建一个触发器

object1

CREATE TRIGGER before_insert_object1
BEFORE INSERT ON object1
FOR EACH ROW
BEGIN
    DECLARE new_id INT;
    SET new_id = (SELECT id FROM next_id FOR UPDATE);
    SET NEW.id = new_id;
    UPDATE next_id SET id = id + 1;
END;

object2

CREATE TRIGGER before_insert_object2
BEFORE INSERT ON object2
FOR EACH ROW
BEGIN
    DECLARE new_id INT;
    SET new_id = (SELECT id FROM next_id FOR UPDATE);
    SET NEW.id = new_id;
    UPDATE next_id SET id = id + 1;
END;

或者,您可以这样使用UUID

CREATE TABLE object1 (
    id CHAR(36) NOT NULL PRIMARY KEY,
    mass INT NOT NULL
);

CREATE TABLE object2 (
    id CHAR(36) NOT NULL PRIMARY KEY,
    angles INT NOT NULL
);

使用UUID插入数据

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