主页 / coding / 问题 / 79116137
Accepted
nonagon
Asked: 2024-10-23 09:05:01 +0800 CST

mysql 原子增量/读取以生成唯一 ID

  • 772

我在 MySQL 8.0 中有一个带有 ID 和名称列的表，例如：

ID  | name
----+------
7   | name1
12  | name2
13  | name3

我想自动生成一个新的 ID 并将其分配给 name=name2 的行。我希望执行以下操作：

UPDATE table SET id=MAX(id)+1 WHERE name='name2'

但这是无效的。我在 MySQL 文档中读到，也许我可以使用多表 UPDATE 来实现，但它看起来很复杂，而且我不确定整个语句（即计算 MAX 的部分和更新字段的部分id）是否是原子的。

我还需要检索新生成的 ID。具体来说，我从 Python 调用所有这些，因此我运行的任何语句或事务的结果都应包含新生成的 ID，以便我随后可以在 Python 代码中使用它。我不想更新表，然后发出单独的 SELECT 语句来查询新 ID。

有没有一种简单的方法可以以原子方式执行此操作，以便许多这样的进程可以同时运行，同时保持生成的 ID 唯一？

请注意，我认为自动增量在这里不适用。我不希望新生成的 ID 出现在新行（即插入的行）上。我希望生成一个新的 ID 并将其应用于现有行。

关于如何使上述 UPDATE 语句与其中的 MAX() 一起工作，还有其他几个问题（例如这个问题）。请注意，这个问题完全不同 - 我需要一种原子方法来生成新的唯一 ID 并将其返回到我的代码。目前尚不清楚该 UPDATE 语句是否在其他答案给出的形式中是原子的，我也不认为它的结果可以返回到代码。我只是写出了 UPDATE，因为这是我第一次尝试以原子方式执行此操作（例如在 mongo db 中，这样的事情是可能的）。

mysql

2 个回答

  1. 为了解决这个问题，我最终添加了第二个表，其中有一个自动增加的 ID 字段。

    为了获得一个保证唯一的新 ID，我执行并查看（在我的情况下是通过 Python 进行的）INSERT INTO second_table VALUES()的值来读取新 ID。LAST_INSERT_ID()cursor.lastrowid

    在此之后，我执行了 aROLLBACK以避免在第二个表中积累无用的行（请注意，它在之后保持其自动递增状态，ROLLBACK但在之后不保持TRUNCATE TABLE！）。我仔细研究了一下，认为这ROLLBACK可能（一开始有点违反直觉）比使用 InnoDB 时提交事务更昂贵。在我的情况下，这是可以接受的，但对于性能最高的解决方案来说，aCOMMIT实际上可能更可取。

    我认为接受的答案是对我的问题更直接的解决方案，但我分享这个是为了对任何人都有帮助。我认为这个解决方案在锁定方面更有效，因此在高度并发的环境中它可能是更好的选择。

    • 0
  2. Best Answer

    使用事务使分配 ID 和检索 ID 成为原子操作：

    START TRANSACTION;

UPDATE table AS t1
CROSS JOIN (
    SELECT MAX(ID) AS maxid
    FROM table
) AS t2
SET t1.id = maxid+1
WHERE t1.name = 'name2';

SELECT id
FROM table
WHERE t1.name = 'name2';

COMMIT;
    • -1

相关问题