MySQL原子插入
在MySQL中，我正在使用包含唯一名称和这些名称的ID的InnoDB表。 客户需要自动检查现有名称，如果不存在，则插入一个新名称并获取该ID。 该ID是一个AUTO_INCREMENT值，在检查现有值时不能增加失控，而不管“ innodb_autoinc_lock_mode ”的设置如何。 这是因为很多时候会检查相同的名字（例如“ Alice ”），并且偶尔会出现一些新名字（例如“ Bob ”）。
根据“ innodb_autoinc_lock_mode ”，“ INSERT...ON DUPLICATE KEY UPDATE ”语句会导致AUTO_INCREMENT增加，即使在重复键情况下也是如此，因此无法接受。 该ID将用作外键约束（在另一个表中）的目标，因此更改现有ID是不行的。 无论操作如何交错，客户端在执行此操作时都不能发生死锁。
我希望在服务器端而不是客户端进行原子操作期间的处理（例如检查现有的ID并决定是否执行插入操作），以便其他会话试图进行的延迟同时做同样的事情是最小的，不需要等待客户端处理。
我的测试表来证明这是名为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;
这使用“ LOCK TABLES...WRITE ”按照MySQL“Table Locking and Transactions的交互作用”文档中给出的说明来正确锁定InnoDB表。 该解决方案要求用户拥有“ LOCK TABLES ”特权。
如果我使用@myName="Alice"运行上面的查询，我会获得一个新的ID，然后继续获取相同的ID，无论我运行多少次。 如果我以@myName="Bob"运行，则会得到具有下一个AUTO_INCREMENT值的另一个ID，依此类推。 检查已存在的名称不会增加表的AUTO_INCREMENT值。
我想知道是否有更好的解决方案来实现这一点，也许不需要“ LOCK TABLES ”/“ UNLOCK TABLES ”命令，并且可以将更多的“基本”命令（例如“ INSERT ”和“ SELECT ”）组合成更多聪明的方式？ 或者，这是MySQL目前提供的最好的方法吗？
编辑
这不是MySQL中“如何插入”如果不存在的重复内容？“。 这个问题并没有涉及我所说的所有标准。 保持AUTO_INCREMENT值稳定的问题在那里没有解决（它仅在顺便提及）。
许多答案没有解决获取现有/插入记录的ID，某些答案不提供原子操作，而某些答案的逻辑是在客户端而不是服务器端完成的。 许多答案改变了现有的记录，这不是我正在寻找的。 我要求有一个更好的方法来满足所有的标准，或者确认我的解决方案是现有MySQL支持的最佳解决方案。
这个问题实际上是关于如果您希望重复数据时如何规范化数据。 然后避免“燃烧”ID。
http://mysql.rjweb.org/doc.php/staging_table#normalization讨论了两步过程，并且旨在通过高速摄取行来实现批量更新。 它退化为单行，但仍需要2个步骤。
第1步INSERTs任何新行，创建新的auto_inc标识。
第2步大幅撤回IDS。
请注意，最好使用autocommit = ON并在加载数据的主事务之外完成该工作。 这避免了燃烧ID的额外原因，即潜在的回滚。
链接地址: http://www.djcxy.com/p/58669.html上一篇: MySQL atomic insert