具有稳定自动增量的 MySQL 原子插入-if-not-exists

Question

在 MySQL 中，我使用的是包含唯一名称和这些名称的 ID 的 InnoDB 表。客户端需要自动检查现有名称，如果不存在则插入一个新名称，并获取 ID。 ID 是一个 AUTO_INCREMENT 值，无论 the setting of "innodb_autoinc_lock_mode" 如何检查现有值，它都不能失控地递增;这是因为经常会检查相同的名字(例如“Alice”)，并且时不时会出现一些新名字(例如“Bob”)。

即使在重复键的情况下，“INSERT...ON DUPLICATE KEY UPDATE”语句也会导致 AUTO_INCREMENT 增加，具体取决于“innodb_autoinc_lock_mode”，因此是 Not Acceptable 。 ID 将用作外键约束的目标(在另一个表中)，因此更改现有 ID 是不行的。客户端在并发执行此操作时不得死锁，无论这些操作如何交错。

我希望原子操作期间的处理(例如检查现有 ID 并决定是否进行插入)在服务器端而不是客户端完成，以便其他延迟尝试同时做同一件事的 session 最少，不需要等待客户端处理。

我的测试表名为 FirstNames:

CREATE TABLE `FirstNames` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `FirstName_UNIQUE` (`FirstName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

到目前为止，我想出的最佳解决方案如下:

COMMIT;
SET @myName='Alice';
SET @curId=NULL;
SET autocommit=0;
LOCK TABLES FirstNames WRITE;
SELECT Id INTO @curId FROM FirstNames WHERE FirstName = @myName;
INSERT INTO `FirstNames` (`FirstName`) SELECT @myName FROM DUAL WHERE @curId IS NULL;
COMMIT;
UNLOCK TABLES;
SET @curId=IF(@curId IS NULL, LAST_INSERT_ID(), @curId);
SELECT @curId;

按照 MySQL "Interaction of Table Locking and Transactions" documentation 中给出的说明使用“LOCK TABLES...WRITE”锁定 InnoDB 表的正确方法。此解决方案要求用户拥有“LOCK TABLES”权限。

如果我使用 @myName="Alice" 运行上述查询，我​​将获得一个新 ID，然后无论运行多少次都会继续获得相同的 ID。如果我随后使用 @myName="Bob" 运行，我将获得另一个具有下一个 AUTO_INCREMENT 值的 ID，依此类推。检查已存在的名称不会增加表的 AUTO_INCREMENT 值。

我想知道是否有更好的解决方案来实现这一点，也许不需要“LOCK TABLES”/“UNLOCK TABLES”命令并结合更多以更聪明的方式“基本”命令(例如“INSERT”和“SELECT”)？或者这是 MySQL 目前提供的最佳方法？

编辑

这不是“How to 'insert if not exists' in MySQL?”的副本。该问题并未涉及我陈述的所有标准。保持 AUTO_INCREMENT 值稳定的问题在那里没有解决(只是顺便提到)。

许多答案没有解决获取现有/插入记录的 ID，一些答案没有提供原子操作，一些答案的逻辑是在客户端而不是服务器端完成的-边。许多答案会更改现有记录，这不是我要找的。我正在寻求一种更好的方法来满足所有规定的标准，或者确认我的解决方案是现有 MySQL 支持的最佳解决方案。

Answer 1

问题实际上是关于当您预计会有重复数据时如何规范化数据。然后避免“燃烧”ID。

http://mysql.rjweb.org/doc.php/staging_table#normalization讨论了一个两步过程，旨在通过高速摄取行进行大量更新。它退化为单行，但仍需要 2 个步骤。

第 1 步插入任何新行，创建新的 auto_inc id。

第 2 步撤回所有 ID。

请注意，最好使用 autocommit=ON 并在加载数据的主事务之外完成这项工作。这避免了燃烧 ID 的额外原因，即潜在的回滚。