MySQL原子插入(如果不存在)具有稳定的自动增量 [英] MySQL atomic insert-if-not-exists with stable autoincrement

问题描述

在MySQL中，我正在使用一个InnoDB表，该表包含唯一的名称以及这些名称的ID.客户需要从原子上检查现有名称，如果不存在则插入一个新名称，并获取ID. ID是一个AUTO_INCREMENT值，并且在检查现有值时，无论innodb_autoinc_lock_mode"的设置；这是因为通常会检查相同的名称(例如"Alice")，并且时常会出现一些新名称(例如"Bob").

In MySQL, I am using an InnoDB table that contains unique names, and IDs for those names. Clients need to atomically check for an existing name, insert a new one if it does not exist, and get the ID. The ID is an AUTO_INCREMENT value, and it must not increment out-of-control when checking for existing values regardless of the setting of "innodb_autoinc_lock_mode"; this is because very often the same name will be checked (e.g. "Alice"), and every now and then some new name will come along (e.g. "Bob").

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

The "INSERT...ON DUPLICATE KEY UPDATE" statement causes an AUTO_INCREMENT increase even in the duplicate-key case, depending on "innodb_autoinc_lock_mode", and is thus unacceptable. The ID will be used as the target of a Foreign-Key Constraint (in another table), and thus it is not okay to change existing IDs. Clients must not deadlock when they do this action concurrently, regardless of how the operations might be interleaved.

我希望在原子操作期间的处理(例如，检查现有的ID并确定是否执行插入操作)在服务器端而不是客户端端进行，这样其他操作的延迟尝试同时执行相同操作的会话是最少的，不需要等待客户端处理.

I would like the processing during the atomic operation (e.g. checking for the existing ID and deciding whether or not to do the insert) to be done on the server-side rather than the client-side, so that the delay for other sessions attempting to do the same thing simultaneously is minimal and does not need to wait for client-side processing.

我用来证明这一点的测试表名为FirstNames:

My test table to demonstrate this is named 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;

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

The best solution that I have come up with thus far is as follows:

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;

按照LOCK TABLES...WRITE"="nofollow noreferrer"> MySQL表锁定和事务的交互"文档，提供了锁定InnoDB表的正确方法.此解决方案要求用户具有"LOCK TABLES"特权.

This uses "LOCK TABLES...WRITE" following the instructions given in the MySQL "Interaction of Table Locking and Transactions" documentation for the correct way to lock InnoDB tables. This solution requires the user to have the "LOCK TABLES" privilege.

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

If I run the above query with @myName="Alice", I obtain a new ID and then continue to obtain the same ID no matter how many times I run it. If I then run with @myName="Bob", I get another ID with the next AUTO_INCREMENT value, and so on. Checking for a name that already exists does not increase the table's AUTO_INCREMENT value.

我想知道是否有更好的解决方案，也许不需要"LOCK TABLES"/"UNLOCK TABLES"命令并结合更多基本"命令(例如"INSERT"和""SELECT")以更巧妙的方式?还是这是MySQL当前必须提供的最佳方法?

I am wondering if there is a better solution to accomplish this, perhaps one that does not require the "LOCK TABLES"/"UNLOCK TABLES" commands and combines more "rudimentary" commands (e.g. "INSERT" and "SELECT") in a more clever way? Or is this the best methodology that MySQL currently has to offer?

这不是"如何如果不插入"的副本是否存在于MySQL中?".这个问题不能解决我所说的所有标准.保持AUTO_INCREMENT值稳定的问题在那里没有得到解决(仅在顺便提及).

This is not a duplicate of "How to 'insert if not exists' in MySQL?". That question does not address all of the criteria that I stated. The issue of keeping the AUTO_INCREMENT value stable is not resolved there (it is only mentioned in passing).

许多答案未解决获取现有/插入记录的ID的问题，某些答案未提供原子操作，并且某些答案的逻辑是在客户端而非服务器上完成的-边.许多答案会更改现有记录，这不是我想要的.我要求的是一种更好的方法来满足上述所有条件，或者是要确认我的解决方案是具有现有MySQL支持的最佳解决方案.

Many of the answers do not address getting the ID of the existing/inserted record, some of the answers do not provide an atomic operation, and some of the answers have the logic being done on the client-side rather than the server-side. A number of the answers change an existing record, which is not what I'm looking for. I am asking for either a better method to meet all of the criteria stated, or confirmation that my solution is the optimal one with existing MySQL support.

推荐答案

问题实际上是关于当您期望有重复项时如何规范化数据.然后避免燃烧" ID.

The question is really about how to normalize data when you expect there to be duplicates. And then avoid "burning" ids.

http://mysql.rjweb.org/doc.php/staging_table#normalization 讨论了一个两步过程，其目的是由于高速提取行而进行批量更新.它退化为单行，但仍需要2个步骤.

http://mysql.rjweb.org/doc.php/staging_table#normalization discusses a 2-step process and is aimed at mass updates due to high-speed ingestion of rows. It degenerates to a single row, but still requires the 2 steps.

步骤1 INSERTs任何 new 行，创建新的auto_inc ID.

Step 1 INSERTs any new rows, creating new auto_inc ids.

第2步将所有ID撤回.

Step 2 pulls back the ids en masse.

请注意，最好使用autocommit = ON并在正在加载数据的主事务之外完成工作.这样可以避免产生ID烧录的额外原因，即潜在的回滚.

Note that the work is best done with autocommit=ON and outside the main transaction that is loading the data. This avoids an extra cause for burning ids, namely potential rollbacks.

这篇关于MySQL原子插入(如果不存在)具有稳定的自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！