mysql - 处理 select + insert 的正确方法以避免重复错误

Question

你好，我有一个简单的 MySQL InnoDB 表，只有两个字段:

• id - 自动增量主索引
• 名称 - 唯一索引

我正在并行从各种来源导入一些数据，并且我需要确保数据在插入时不重复，因此我正在执行以下操作:

SELECT `id` FROM `table` WHERE `name` = <name>;
 if `id` <= 0
  INSERT INTO `table` SET `name` = "<name>";
  return AUTO_INCREMENT
 else return `id`

这在 99.9999% 的情况下有效，但可能会发生(并且发生在我身上)两个或多个不同的脚本插入相同的数据，因为两个 SELECT 返回 id <=0 所以两者都会这样做一个 INSERT 并且其中之一引发错误。

我想到了两种可能的解决方案，但我不确定哪种最有效。

还有一条信息:最初导入不会在表中找到元素，但随着插入更多元素，找到的可能性会增加。经过粗略计算，最终的表将有大约 7-1000 万条记录:

SELECT `id` FROM `table` WHERE `name` = <name>;
if `id` <= 0
 INSERT IGNORE INTO `table` SET `name` = "<name>";
 get AUTO_INCREMENT
  if AUTO_INCREMENT <=0
   SELECT `id` FROM `table` WHERE `name` = <name>;
   return `id`
  else return AUTO_INCREMENT
else return `id`

或者

INSERT IGNORE INTO `table` SET `name` = "<name>";
get AUTO_INCREMENT
 if AUTO_INCREMENT <=0
  SELECT `id` FROM `table` WHERE `name` = <name>;
  return `id`
 else return AUTO_INCREMENT

Answer 1

您正在达到竞争条件。当您的代码检测到需要进行新的插入时，您的两个客户端就会竞相成为第一个插入该值的人。这是赢家通吃。您需要编写代码来避免这种竞争情况。幸运的是，SQL 是专门设计的，因此可以做到这一点。

这里有几个选择，都特定于 MySQL 的 SQL 方言。

一种是使用内置函数LAST_INSERT_ID()。我相信它达到了您所说的获取 AUTO_INCRMENT 的目的。

另一种方法是使用INSERT ... ON DUPLICATE KEY UPDATE。

看起来你的逻辑旨在做两件事:

1. 确保name 值位于表中，如果尚不存在，请将其放在那里。
2. 返回与名称值关联的 id 值。

你可以这样做。

INSERT IGNORE INTO `table` (name) VALUES (<name>);
SELECT id FROM `table` WHERE name = <name>;

请注意，INSERT IGNORE 操作不会被访问数据库的不同程序之间的竞争条件所捕获，因为它是单个 SQL 语句。

您可以使用LAST_INSERT_ID()来优化它。

INSERT IGNORE INTO `table` (name) VALUES (<name>);
if (LAST_INSERT_ID()=0) then do the select.