mysql:如何同时在两个表中放置唯一的两列 [英] mysql: how put two columns unique in two tables at once

问题描述

嗨在 MySQL 中，我想将两个(唯一)字段放在两个不同的表中，在我的示例中，我有三个表、登录表、教授和用户.我希望字段 (id_user) 和字段 (id_prof) 不重复，以便在字段 (num) 的登录表中(唯一)之后插入它们例如:id_user 1, 3, 6 ...id_prof: 2, 4, 5, 7 ...数量: 1 ,2, 3, 4, 5, 6, 7...

解决方案

这是有两个触发器的版本.

但也有可能发生，并发条目没有唯一编号，在重型服务器上也会失败.

所以你必须使用唯一的登录 id 并在插入失败时进行捕获，然后尝试获取新的 id

<块引用>

CREATE TABLE prof(id BIGINT, name varchar(10));

<块引用>

CREATE TABLE user(id BIGINT, name varchar(10))

<块引用>

CREATE TABLE login(id BIGINT, name varchar(10))

<块引用>

CREATE TRIGGER before_prof_insert插入前每一行教授开始声明 prof_ BIGINT;声明 user_BIGINT;声明 res_ BIGINT;SELECT MAX(id) into prof_FROm prof;SELECT MAX(id) into user_ FROM user;如果 user_ 为 NULL 且 @prof 为 NULL THEN设置 res_ := 1;别的如果用户_ >prof_ THENSET res_ := user_ + 1;别的设置 res_ := prof_ + 1;万一;万一;SET NEW.id = res_;插入登录值 (res_,NEW.name);结尾

<块引用>

CREATE TRIGGER before_user_insert插入前每一行的用户开始声明 prof_ BIGINT;声明 user_BIGINT;声明 res_ BIGINT;SELECT MAX(id) into prof_FROm prof;SELECT MAX(id) into user_ FROM user;IF user_ IS NULL AND prof_ IS NULL THEN设置 res_ := 1;别的如果用户_ >prof_ THENSET res_ := user_ + 1;别的设置 res_ := prof_ + 1;万一;万一;SET NEW.id = res_;插入登录值 (res_,NEW.name);结尾

<块引用>

INSERT INTO prof VALUES (0,'profa');)

<块引用>

INSERT INTO user VALUES (0,'usera');

<块引用>

INSERT INTO prof VALUES (0,'profb');

<块引用>

INSERT INTO prof VALUES (0,'profc');

<块引用>

INSERT INTO user VALUES (0,'userb');

<块引用>

INSERT INTO prof VALUES (0,'profd');

<块引用>

SELECT * FROM prof;

<前>身份证 |名称-: |:----1 |教授3 |教授4 |教授6 |专业

<块引用>

SELECT * FROM user;

<前>身份证 |名称-: |:----2 |用户名5 |用户b

<块引用>

SELECT * FROM login;

<前>身份证 |名称-: |:----1 |教授2 |用户名3 |教授4 |教授5 |用户b6 |专业

db<>fiddle 这里

会更容易

INSERT INTO user VALUES (uuidv4(),'usera');INSERT INTO prof VALUES (uuidv4(),'profa');

这将保证每个表的 id 都是唯一的.

hi in MySQL i want to put two (unique) fields in two different table, in my example i have three table, login table, prof and user. I want the field (id_user) and the field (id_prof) not to be repeated to insert them also after (unique) in the login table in the field (num) e.g: id_user 1, 3, 6 ... id_prof: 2, 4, 5, 7 ... num: 1 ,2, 3, 4, 5, 6, 7...

解决方案

This is the version with two Triggers.

But it can happen, that concurrent entries will have not unique numbers, also on heavy duty servers this also fails.

So you have to take login id unique and catch when the insert fails and then try to get anew id

CREATE TABLE prof(id BIGINT, name varchar(10));

CREATE TABLE user(id BIGINT, name varchar(10))

CREATE TABLE login(id BIGINT, name varchar(10))

CREATE TRIGGER before_prof_insert
BEFORE INSERT
ON prof FOR EACH ROW
BEGIN
    DECLARE prof_ BIGINT;
    DECLARE user_ BIGINT;
    DECLARE res_ BIGINT;
    SELECT MAX(id) into prof_ FROm prof;
    SELECT MAX(id) into user_ FROm user;
    
    IF user_ IS NULL AND @prof IS NULL THEN
        SET res_ := 1;
    ELSE
        IF user_ > prof_  THEN
            SET res_ := user_ + 1;
        ELSE
            SET res_ := prof_ + 1;
        END IF;
    END if;
    SET NEW.id = res_;
    
    INSERT INTO login VALUES (res_,NEW.name);

END

CREATE TRIGGER before_user_insert
BEFORE INSERT
ON user FOR EACH ROW
BEGIN
    DECLARE prof_ BIGINT;
    DECLARE user_ BIGINT;
    DECLARE res_ BIGINT;
    SELECT MAX(id) into prof_ FROm prof;
    SELECT MAX(id) into user_ FROm user;
    
    IF user_ IS NULL AND prof_ IS NULL THEN
        SET res_ := 1;
    ELSE
        IF user_ > prof_  THEN
            SET res_ := user_ + 1;
        ELSE
            SET res_ := prof_ + 1;
        END IF;
    END if;
    SET NEW.id = res_;
    
    INSERT INTO login VALUES (res_,NEW.name);

END

INSERT INTO prof VALUES (0,'profa');)

INSERT INTO user VALUES (0,'usera');

INSERT INTO prof VALUES (0,'profb');

INSERT INTO prof VALUES (0,'profc');

INSERT INTO user VALUES (0,'userb');

INSERT INTO prof VALUES (0,'profd');

SELECT * FROM prof;

id | name 
-: | :----
 1 | profa
 3 | profb
 4 | profc
 6 | profd

SELECT * FROM user;

id | name 
-: | :----
 2 | usera
 5 | userb

SELECT * FROM login;

id | name 
-: | :----
 1 | profa
 2 | usera
 3 | profb
 4 | profc
 5 | userb
 6 | profd

db<>fiddle here

Much easier it would be

INSERT INTO user VALUES (uuidv4(),'usera');
INSERT INTO prof VALUES (uuidv4(),'profa');

This would guarantee that the ids are unique for every table.

这篇关于mysql:如何同时在两个表中放置唯一的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！