基于2列和部分唯一布尔值的自动递增模式

kninwzqo  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(325)

有3个表:user,thing,thinguse。
事情可以是 active 以及 inactive . 一个用户可以拥有很多东西,但只有一个可以 active 当时。一旦事情变得 inactive 无法再次激活。用户可以使用 active 应该导致用自己独特的事物创造事物的事物。我可能应该注意到,最终会有数百万,如果不是数十亿的东西

示例: user1(id: 1) 创建

thing_1(id: 1, owner: 1, active: true) user1 使用 thing_1 结果是
thing_use_1(id: 1, thingId: 1, useId: 1) user1 使用 thing_1 结果是
thing_use_2(id: 2, thingId: 1, useId: 2) user1 创建 thing_2(id: 2, owner: 1, active: true) ,现在 thing_1 应该变成 thing_1(id: 1, owner: 1, active: false) 从现在开始 thing_1 未激活,无法再使用/重新激活 user1 创建 thing_use_3(id: 3, thingId: 2, useId: 1) <-注意 useId 已设置为1 user1 创建
thing_use_4(id: 4, thingId: 2, useId: 2) user2(id: 2) 创建
thing_3(id: 3, owner: 2, active: true) user2 创建 thing_use_5(id: 5, thingId: 3, useId: 1) <-注意 useId 已设置为1
此时,db应如下所示:

user_1(id: 1)
    thing_1(id: 1, owner: 1, active: false)
        thing_use_1(id: 1, thingId: 1, useId: 1)
        thing_use_2(id: 2, thingId: 1, useId: 2)
    thing_2(id: 2, owner: 1, active: true)
        thing_use_3(id: 3, thingId: 2, useId: 1)
        thing_use_4(id: 4, thingId: 2, useId: 2)

user_2(id: 2)
    thing_3(id: 3, owner: 2, active: true)
        thing_use_5(id: 5, thingId: 3, useId: 1)

其他注意事项:
单个事物中的每个thinguse都应该有唯一的(自动递增的)useid。
如果从上面的例子中看不出这一点的话,那么用户对事物和事物对事物之间的关系都是一对多的。
所以基本上有两个问题:

问题a:

单个用户只能有1个 active 当时的事情。据我所知,在mysql中不可能创建允许单个 TRUE 多个 FALSES . 然而,我想出了两个“变通方法”。它们看起来都有点老套,所以也许有更好的解决方案:
创建 uniqueIndex(owner, active) 论物与用 true / null 作为 active / inactive 标志,因为mysql不将空值视为重复值。
第二个想法是将最新的用户对象视为 active . 这种情况下的问题是,对正在发生的事情不是100%直接的,我假设编写这样的查询很难/很慢 find all active things across all users .
(在@edit1上添加)第三个想法是删除 active 从thing和add列 activeThingId 列到用户表。这样一来,单个用户就不可能同时拥有多个活动内容,而且很容易查询“所有活动内容”。

问题b:

一个事物不能有多个具有相同useid的thinguse。为了实现这个目标,我可以 uniqueIndex(thingId, useId) 在thinguse上。这将防止意外的useid复制。但是,在获取正确的下一个useid/设置它时仍然存在问题。我可以从技术上在应用程序级别编写这个逻辑,但如果可能的话,我宁愿让db为我处理它。
(添加到@edit2)
谢谢,@jairsnow,根据你的程序,我用触发器成功地部分解决了问题b:

CREATE TRIGGER triggerName
    BEFORE INSERT ON ThingUse
    FOR EACH ROW BEGIN
        SET @actual_value = (SELECT COUNT(*) FROM ThingUse WHERE thingId=NEW.thingId);
        IF (@actual_value IS NULL) THEN
            SET @actual_value = 0;
        END IF;
        SET NEW.useId=@actual_value;
    END

然而,我不得不使用 COUNT(*) 而不是 MAX(useId) 因为后一种原因我被复制了 useId .
现在唯一的问题是如果我加上 ThingUse 在不等待其他/以前的查询完成的情况下,我得到了一个可怕的死锁错误: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction .
@第1版:增加了a.3。
@edit2:部分解决问题b。

ars1skjm

ars1skjm1#

因为在mysql中触发器不能让您编辑被触发的同一个表,所以我建议使用一些存储过程来插入数据并执行您需要的操作。
尽管您只需使用存储过程,而不必使用查询来手动向表中添加或更新记录,但仍然可以通过此方法(如果需要,您最终可以使用一些触发器来限制添加/更新查询)
对于一个问题,你可以这样做:

DELIMITER $$
CREATE PROCEDURE `add_new_thing`(IN `owner` INT)
begin
    INSERT INTO Thing(owner, active) VALUES(owner, 1);
    UPDATE Thing SET active=false WHERE owner=owner AND active=1 AND id != LAST_INSERT_ID();
end $$
DELIMITER ;

对于问题b,你可以这样做:

DELIMITER $$
CREATE PROCEDURE `use_thing`(IN `thing_id` INT)
begin
    SET @actual_value = (SELECT max(useId) FROM ThingUse WHERE thingId=thing_id);
    IF (@actual_value IS NULL) THEN 
        SET @actual_value = 0; 
    END IF;
    INSERT INTO ThingUse(thingId, useId) VALUES(thing_id, @actual_value+1);
end $$
DELIMITER ;

注意:对于问题b的解决方案,请注意数字总是唯一的(因为如果您取最大值并执行a+1,则逻辑上是唯一的),但不是自动递增的(如果您创建了一个新记录并且useid为17,则删除该新记录并创建另一个记录,最后一个记录的useid将再次为17)
@编辑:问题a的优化查询

相关问题