mysql - 递增和返回值的函数 (MySQL)

Question

假设我在表 t 中有一行 ID 等于 1。

如果我运行以下 sql，我得到的结果等于 1(当 c 列为 0 时):

SELECT NEXT_ID(1)

但是，如果运行这个，我得到的结果是 1，而不是 0(因为表 t 中没有 ID = 2 的行):

SELECT NEXT_ID(2)

NEXT_ID 函数:

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
 BEGIN
  DECLARE counter BIGINT DEFAULT 0;
  UPDATE t SET c = (@counter := c +1) WHERE ID = id;
  return @counter;
 END;

我的目的是创建一个计数器，将值作为原子操作递增。那么，为什么我在 NEXT_ID(2) 上得到一个大于 0 的值？似乎计数器变量存储在 session 中......

这在多线程应用程序中使用安全吗？

Answer 1

如果您DECLARE counter 那么您不应该使用@counter 来引用该变量。在 MySQL 存储函数中，声明的变量没有 @ 标记。带有 @ 标记的变量是 user-defined variables . @counter 是与 counter 不同的变量，尽管它们的拼写相同。

在多线程应用中这样做安全吗？当然，如果线程使用不同的 id 值递增不同的行，它们不会冲突(假设 id 是 t 表的唯一键).

即使多个线程使用相同的 id 值，因此需要递增 t 表中的同一行，也会发生一个先到达那里的情况, 锁定该行，并递增它。第二个线程将等待获得自己的锁，直到第一个线程提交它的事务。然后第二个线程将继续，然后将看到 c 的增量值。

所以它是安全的，但它不允许高吞吐率。争用相同 id 的线程将不得不排队等待锁的当前持有者提交他们的事务。如果您期望多个线程并行完成它们的工作，您会发现这会成为一个瓶颈。

这就是 AUTO_INCREMENT 存在的原因，因为它会短暂锁定计数器以生成新值，但会立即释放锁定，而不是等待调用者的事务完成。这允许并发线程继续工作，而不是互相等待。

你不能用 UPDATE 操作来模拟 AUTO_INCREMENT 的行为，这在事务中是必然的。

---

回复你的评论:

抱歉，我忘记了 := 不适用于局部声明的变量。我对其进行了测试，发现了两种选择:

备选方案1:不用费心声明局部变量，直接使用用户定义的变量。

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
READS SQL DATA
 BEGIN
  UPDATE t SET c = (@counter := c +1) WHERE ID = id;
  RETURN @counter;
 END

备选方案 2:使用局部变量，但将 LAST_INSERT_ID() 设置为增量值。然后将计数器局部变量SET设置为该值。

CREATE FUNCTION NEXT_ID(id INT)
RETURNS VARCHAR(15)
READS SQL DATA
 BEGIN
  DECLARE counter BIGINT DEFAULT 0;
  UPDATE t SET c = LAST_INSERT_ID(c +1) WHERE ID = id;
  SET counter = LAST_INSERT_ID();
  RETURN counter;
 END;

我测试了两种替代方案，它们都有效。