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

共有3张桌子: 用户 我是说, 事情 我是说, 语言 是的。

事情 可以是 active 和 inactive 是的。 单曲 用户 可以有很多东西但是只有一个可以 积极的 当时。一旦事情变成 不活动的 无法再次激活。 用户可以使用 积极的 应该导致 语言 有自己独特的用途。 我应该注意到最终会有数百万甚至数十亿 语言 的

例子:

1. user1(id: 1) 创建 thing_1(id: 1, owner: 1, active: true)
2. user1 使用 thing_1 结果是 thing_use_1(id: 1, thingId: 1, useId: 1)
3. 用户1 使用 事情1 结果是 thing_use_2(id: 2, thingId: 1, useId: 2)
4. 用户1 创建 thing_2(id: 2, owner: 1, active: true) ,现在 事情1 应该变成 thing_1(id: 1, owner: 1, active: false)
5. 从现在开始 事情1 未激活,无法再使用/重新激活
6. 用户1 创建 thing_use_3(id: 3, thingId: 2, useId: 1 ) <-注意 useId 已被设定为 1个
7. 用户1 创建 thing_use_4(id: 4, thingId: 2, useId: 2)
8. user2(id: 2) 创建 thing_3(id: 3, owner: 2, active: true)
9. user2 创建 thing_use_5(id: 5, thingId: 3, useId: 1 ) <-注意 使用ID 已设置为 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)

其他说明:

• 每 语言 在单个 事情 应该有唯一的(自动递增的)useid。
• 如果从上面的例子看不出这一点 用户 到 事情 和 事情 到 语言 都是一对多。

所以基本上有两个问题:

问题A:

单身 用户 只能有一个 积极的 事情 当时。据我所知,在mysql中不可能创建一个特殊的布尔行,它允许 TRUE 和多重 FALSES 是的。然而,我想出了两个“解决办法”。它们看起来都有点老套,所以也许有更好的解决方案:

1. 创造 uniqueIndex(owner, active) 在 事情 使用 true / null 作为 积极的 / 不活动 自mysql以来的标志不会将空值视为重复值。
2. 第二个想法是处理最近的用户 事情 作为 积极的 是的。这种情况下的问题是,它并不是100%直接地知道发生了什么+我假设编写这样的查询会很难/很慢 find all active things across all users 是的。
3. (添加于 @编辑1 )第三个想法是移除 积极的 列来自 事情 加上 activeThingId 列到 用户 桌子。这样,单个用户就不可能同时拥有多个活动对象,而且查询“所有活动对象”也很容易。

问题B:

单曲 事情 不能有多个 语言 使用相同的useid。为了达到我能设定的目标 uniqueIndex(thingId, useId) 在 语言 是的。这将防止意外的useid重复。但是,获取正确的下一个useid/设置仍然存在问题。我可以从技术上在应用程序级别编写这个逻辑,但如果可能的话,我宁愿让数据库为我处理它。

(添加于 @编辑2 )

谢谢,@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) 因为因为某些原因我被复制了 使用ID 是的。

现在唯一的问题是如果我加上 ThingUse 很快,在不等待其他/以前的查询完成的情况下,出现了一个可怕的死锁错误: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction 是的。

@编辑1 :增加了A.3。

@编辑2 :部分解决 问题B 是的。