mysql - 具有基于2列和部分唯一 bool 值的自动递增的架构

Question

有三个表：用户表，事物表，语言表。
事情可以是active和inactive。
一个用户可以拥有很多东西，但同时只有一个可以active。一旦事情变得inactive它就不能被再次激活。
用户可以使用active东西，这将导致创建具有自己独特的东西useId的Thing use。
我应该注意到，最终会有数百万甚至数十亿的ThingUse's
例子：
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)已设置为1
useId创建一个
user1创建一个
thing_use_4(id: 4, thingId: 2, useId: 2)创建一个user2(id: 2)<-注意thing_3(id: 3, owner: 2, active: true)已设置为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：
一个用户当时只能有一个 user2东西。据我所知，在MySQL中不可能创建一个特殊的布尔行，它允许单个 thing_use_5(id: 5, thingId: 3, useId: 1)和多个 useId。然而，我想出了两个“解决办法”。它们看起来都有点老套，所以也许有更好的解决方案：
在Thing上创建 active并使用 TRUE/ FALSES作为 uniqueIndex(owner, active)/ true标志，因为MySQL不将空值视为重复值。
第二个想法是将最新的用户内容视为 null。这种情况下的问题是，它不是100%直接的事情发生+我假设它将是硬/慢写一个查询，如 active。
（添加在@Edit1上）第三个想法是从Thing中删除 inactive列并将 active列添加到用户表。这样，单个用户就不可能同时拥有多个活动对象，而且查询“所有活动对象”也很容易。
问题B：
一个事物不能有多个具有相同useId的ThingUse。为了达到这个目的，我可以在ThingUse上设置 find all active things across all users。这将防止意外的useId重复。但是，获取正确的下一个useId/设置仍然存在问题。我可以从技术上在应用程序级别编写这个逻辑，但如果可能的话，我宁愿让数据库为我处理它。
（添加在@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

但是，我不得不使用 active而不是 activeThingId，因为由于某些原因，在后者中，我得到了重复的 uniqueIndex(thingId, useId)。
现在唯一的问题是，如果我快速添加 COUNT(*)，例如，不等待其他/以前的查询完成，就会出现可怕的死锁错误： MAX(useId)。
@编辑1：增加了A.3。
@Edit2：部分解决了问题B。

Answer 1

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

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的解决方案，请注意数字始终是唯一的（因为如果取max number，do a+1按逻辑是唯一的），但不是自动递增的（如果创建新记录且useId为17，则删除该新记录并创建另一条记录，则最后一条记录的useId将再次为17）
@编辑：问题A的优化查询