mysql - 在 InnoDB 中使用 MAX 模拟自动增量的最快、最安全的方法

Question

假设我们有一个具有以下架构的表:

CREATE TABLE Persons (
sid int NOT NULL AUTO_INCREMENT,
groupName varchar(255) NOT NULL,
userIdInGroup int,
PRIMARY KEY (sid));

我们希望为同一组中的每个新用户分配一个基于该组的最后一个 userId 的自动增量 userId，即我们希望在每个组内模拟一个自动增量 userId。

由于我们要插入新的 userId 并考虑 MAX(userIdInGroup)，因此我们需要将 select 和 insert 包装在事务中。像这样的事情:

START TRANSACTION
SET @a = (SELECT MAX(userIdInGroup) FROM Persons WHERE groupName= 'Foo') +1;
INSERT THE NEW ROW USING userIdInGroup = @a
COMMIT 

1. 仅在事务中选择 MAX 是否安全，还是我们需要通过 SELECT FOR UPDATE 来锁定某些内容？
2. 是否有办法绕过事务并仍然保持一致？

Answer 1

我不是 MySql 人员，但我确实有足够的使用其他数据库的经验，知道这是一个非常糟糕的主意。此 UserIdInGroup 可以使用 row_number 轻松计算:

SELECT sid, 
       groupName, 
       ROW_NUMBER() OVER(PARTITION BY groupName ORDER BY sid) AS userIdInGroup
FROM Persons

除了滚动您自己的自动增量通常最终会给出错误的数字(尤其是在多线程环境中)这一事实之外，您可以轻松计算的内容(无论是在代码还是在性能方面)都不应该被存储无论如何。