MySQL 中的作用域/复合代理键

Question

以下是我当前数据库的摘录（更改了表名称以便于理解）：

Pet(ownerFK, id, name, age)
Owner(id, name)

其中 id 始终是使用 auto_increment 创建的代理键。

我想让代理键 Pet.id 由 Pet.ownerFK 确定“范围”，或者换句话说，拥有一个复合键 [ownerFk, id]< /code> 作为我的最小键。 我希望表的行为如下：

INSERT Pet(1, ?, "Garfield", 8);
INSERT Pet(1, ?, "Pluto", 12);
INSERT Pet(2, ?, "Mortimer", 1);

SELECT * FROM Pet;
  RESULT:
   Pet(1, 1, "Garfield", 8)
   Pet(1, 2, "Pluto", 12)
   Pet(2, 1, "Mortimer", 1)

我当前正在使用这个 MyISAM 的功能，其中“您可以在多列索引中的辅助列上指定 AUTO_INCRMENT 在这种情况下，为 生成的值。 code>AUTO_INCRMENT 列的计算方式为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix 当您想要将数据放入有序组时，这非常有用。”

然而，由于各种（也许是显而易见的）原因，我想从 MyISAM 切换到 InnoDB，因为我在某些地方需要事务。

有什么办法可以用InnoDB达到这种效果吗？

我发现了一些关于这个问题的帖子，其中很多人建议在插入之前对表进行写锁定。 我对此不是很熟悉，但是表写锁不会对这个进行一点大修吗？ 如果可能的话，我宁愿考虑使用写安全事务（我以前从未这样做过） - 使用 Owner.current_pet_counter 作为辅助字段。

所以另一个可接受的解决方案是......

实际上我不需要“作用域”ID 成为实际密钥的一部分。 我的实际数据库设计使用一个单独的“永久链接”表，该表使用此“功能”。 我目前使用它作为丢失交易的解决方法。 我想到了以下替代方案：

 Pet(id, ownerFK, scopedId, name, age), KEY(id), UNIQUE(ownerFK, scopedId)
 Owner(id, name, current_pet_counter)

 START TRANSACTION WITH CONSISTENT SNAPSHOT;
 SELECT @new=current_pet_counter FROM Owner WHERE id = :owner_id;
 INSERT Pet(?, :owner_id, @new, "Pluto", 21);
 UPDATE Owners SET current_pet_counter = @new + 1 WHERE id = :owner_id;
 COMMIT;

我还没有在 MySQL 中使用过 transactions/transactionvars，所以我不知道这是否会存在严重问题。 注意：我不想重复使用曾经给予宠物的id。 这就是为什么我不使用 MAX() 的原因。 此解决方案有任何注意事项吗？

Answer 1

我不相信是这样。 如果您确实必须拥有该模式，则可以使用事务来选择 MAX(id) WHERE OwnerFK，然后插入。

不过，我非常怀疑这种模式是否有充分的理由； 主键现在也是关于键的事实，这可能会让数据库理论家不高兴。

通常情况下，您希望“id”本身成为一个正确的主键，并使用ownerFK进行分组，如果需要，还可以使用一个单独的“rank”列来将每个所有者的宠物按特定顺序排列，以及一个UNIQUE索引超过（ownerFK，排名）。