在MySQL中，ON DUPLICATE KEY + AUTO INCREMENT问题

Question

我有这样的表结构：

当我向表中插入行时，我使用以下查询语句：

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

当我首次插入数据时，未触发ON DUPLICATE KEY，id增量正常：

但是当ON DUPLICATE KEY触发且我插入新行时，id对我来说看起来很奇怪：

如何在触发ON DUPLICATE KEY时保持自动递增，并使其正确递增？

- newbie

---

这里大多数关于AUTO_INCREMENT序列中间存在空缺的问题，都是因为将其用于错误的任务而导致的。如果只是出于好奇，那么没问题 :) - Álvaro González

@ÁlvaroG.Vicario 我是否错误地使用了 AUTO_INCREMENT？ - newbie

可能是重复的问题：MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement - Álvaro González

我不知道。间隙是个问题吗？ - Álvaro González

@ÁlvaroG.Vicario，不是真的，但我只是希望它能正确递增，因为这可能在未来的开发中很有用。 - newbie

Answer 1

这种行为已经记录在案（括号中的段落）：

如果你使用了 ON DUPLICATE KEY UPDATE，而插入的一行数据会导致在 UNIQUE index 或 PRIMARY KEY 中出现重复值，MySQL 会执行对旧行的 UPDATE 操作。例如，如果列 a 被声明为 UNIQUE 并包含值 1，下面两个语句将有相似的效果：

    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;

这里有一个简单的解释。MySQL尝试首先执行插入操作，这时ID会自动递增。一旦递增，它就会保持不变。然后检测到重复项并执行更新操作。但是该值被忽略了。

您不应该依赖于auto_increment没有间隙。如果这是一个要求，则更新和插入的开销要大得多。基本上，您需要在整个表上放置锁，并重新编号需要重新编号的所有内容，通常使用触发器。更好的解决方案是在输出时计算增量值。

- Gordon Linoff

---

说得好。感谢您包含实际的文件。;-) - ghoti

如果auto_increment不是必需的，您认为间隔是否可以接受？ - newbie

@新手...使用auto_increment的目的是在插入行时提供递增序列的值。如果您需要其他内容，请使用适当的机制，例如在输出时计算值或使用触发器。 - Gordon Linoff

这是关于MySQL的一个愚蠢问题。好吧，我不指望它没有间隙，但今天我达到了16000000+行（MEDIUMINT）的限制，而我只有大约500000条记录经常被更新。问题是，我还有其他表与这个表相关联，总大小超过250GB，如果要将其更新为INT（完全不需要时），需要花费数天时间，并且会失去存储空间。 - lapkritinis

我不理解这个设计背后的逻辑。虽然我不介意有空缺，但我看不出为什么要在 ID 从未存在的情况下自动递增，这只是无谓地创建了许多垃圾 ID。 - jsmars

我经常使用ON DUPLICATE KEY UPDATE来定期更新多个记录。我不关心间隔，但我担心会达到最大列值。在每个批量更新后定期使用ALTER TABLE table_name AUTO_INCREMENT=1（将自动增量重置为最后一行的id+1）会是一种不好的做法吗？ - David Avikasis

Answer 2

这个问题比较老了，但是我还是回答一下，也许能帮助到某些人解决自增问题。在插入或更新之前，请使用以下代码来解决它，并将它们一起执行：

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL; 

在一起并作为一个陈述，格式应该类似于以下内容：

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL; 
INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";

- ali asghar tofighian

---

所有像“不要担心空隙”这样的答案都是垃圾，这应该是被接受的答案。我的ID号码快用完了，我使用了上面的代码，对于一次添加多个值的INSERT操作非常有效。谢谢;) - Dariusz Chowański

我用php写了另一个答案。但是我看到这是纯sql？你能解释一下它是如何工作的吗？在你的NEW_AI变量中，AI部分是什么意思？我不知道concat是什么，你正在设置自增值吗？我也不理解Prepeare或Execute命令。如果你可以在每行之间加上注释来解释步骤，这可能有助于其他人理解你的答案。然而，这显然是比我的建议更好的答案。 - Rosski

在这一天，你可以使用DB builder中的insertOrIgnore函数。 - undefined

@Kawsar 你说得没错，但是当你在非常受限制的表格中使用它时，会产生一些严重的副作用，因为不会生成错误。 - undefined

@Ali 问题在于“更新”会触发自动递增。我的意思是，对于新记录来说增加自动递增值是可以的，但如果更新会触发它，那么就会产生空缺。我的解决方案是分开插入和更新，先进行插入，然后计算当前的自动递增值，然后进行更新，最后将自动递增值重置为旧值。 - undefined

Answer 3

我曾经也遇到自动增量的空缺问题，但我找到了一个避免这种情况的方法。

关于之前提到的“开销”问题。当我第一次编写我的数据库查询代码时，它进行了很多单独的查询，耗时5个小时。一旦我添加了“ON DUPLICATE KEY UPDATE”，时间就减少到了约50秒。太神奇了！无论如何，我解决问题的方式是使用2个查询。虽然运行时间加倍到了2分钟，但仍然可以接受。

首先，我执行了一个SQL查询以写入所有数据（更新和插入），但我在第一个查询中包含了“IGNORE”，这样就可以跳过更新并只插入新内容。因此，假设您的自动增量之前没有空缺，那么它仍然没有空缺，因为它只有新记录。我认为是更新导致了空缺。因此，对于插入操作：

"INSERT IGNORE INTO mytablename(stuff,stuff2) VALUES "

接下来，我执行了“ON DUPLICATE KEY UPDATE”变体的SQL查询。它会保持ID不变，因为所有要更新的记录都已经有ID了。唯一打破的是自动增量值，它会在添加（或更新）新记录时递增。所以解决方案是一旦应用了更新，就将这个自动增量值补回到之前的值。

要使用PHP来修补自动增量值，请使用以下SQL：

"ALTER TABLE mytablename AUTO_INCREMENT = " . ($TableCount + 1);

这样做的原因是当你进行更新时，你并没有增加记录数。因此，我们可以使用表格计数来知道下一个ID应该是什么。您将$ TableCount设置为表格计数，然后添加1即可得到下一个自动增量数字。

虽然这种方法看起来简单粗暴，但似乎很有效。不过，如果同时有其他写入数据库的操作，则可能会有问题。

- Rosski

---

使用$TableMax比使用$TableCount更安全，就像@aliasghartofighian上面的回答一样（例如可能已删除某些行）。 - fresher96

好的，我的例子不是纯 SQL，我在使用 PHP。TableMax 和 TableCount 本质上是相同的东西，只是变量名取决于编码人员更容易理解哪个。我来自 VB 语言，所以对我来说计数是指项的数量。实际上，我并不理解他的代码，因为我没有他那么厉害。但也许出于兴趣，我会问他详细说明一下。 - Rosski

Answer 4

将数据库引擎从InnoDB更改为MyIsam将解决您的问题。

- Delickate

Answer 5

我通常通过创建临时表来处理这个问题，记录在临时表中哪些记录是新的，仅对非新记录进行更新，并使用新记录执行插入操作。以下是完整示例：

## THE SETUP

# This is the table we're trying to insert into
DROP TABLE IF EXISTS items;
CREATE TABLE items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE,
  price INT
);

# Put a few rows into the table
INSERT INTO items (name, price) VALUES
  ("Bike", 200),
  ("Basketball", 10),
  ("Fishing rod", 25)
;

## THE INSERT/UPDATE

# Create a temporary table to help with the update
DROP TEMPORARY TABLE IF EXISTS itemUpdates;
CREATE TEMPORARY TABLE itemUpdates (
  name VARCHAR(100) UNIQUE,
  price INT,
  isNew BOOLEAN DEFAULT(true)
);

# Change the price of the Bike and Basketball and add a new Tent item
INSERT INTO itemUpdates (name, price) VALUES
  ("Bike", 150),
  ("Basketball", 8),
  ("Tent", 100)
;

# For items that already exist, set isNew false
UPDATE itemUpdates
JOIN items
ON items.name = itemUpdates.name
SET isNew = false;

# UPDATE the already-existing items
UPDATE items
JOIN itemUpdates
ON items.name = itemUpdates.name
SET items.price = itemUpdates.price
WHERE itemUpdates.isNew = false;

# INSERT the new items
INSERT INTO items (name, price)
SELECT name, price
FROM itemUpdates
WHERE itemUpdates.isNew = true;

# Check the results
SELECT * FROM items;
# Results:
# ID | Name        | Price
# 1  | Bike        | 150
# 2  | Basketball  | 8
# 3  | Fishing rod | 25
# 4  | Tent        | 100
        

"INSERT IGNORE INTO" 的方法更简单，但它会忽略任何错误，这不是我想要的。我同意这是 MySQL 行为上的奇怪之处，但这是我们必须处理的。"

- Aurast

Answer 6

提供更多修复结果的间接答案。

如果您不在应用程序中使用自增字段作为标识字段（实际上您不应该这样做，UUID或类似的东西是更好的实践），当然，如果您没有数十亿行，您可以相当容易地重置自增字段。

SET SQL_SAFE_UPDATES = 0;
SET  @num := 0;
UPDATE my_table SET id = @num := (@num+1);
ALTER TABLE my_table  AUTO_INCREMENT =1;

在MySQL中进行INSERT UPDATE时，我有点讨厌这种情况。

这不是我的代码。我在SO上得到了它，但那是很久以前的事了...

另外需要注意的是，这并不是真正解决这个问题的答案。它更多地是帮助修复一个失控的自增字段。

- MyChickenNinja

Answer 7

我想补充一下，因为我正在尝试找到解决方法。 我无法停止重复警告，并发现原因是因为我将其设置为TINYINT，它只允许127个条目，更改为SMALL/MED/BIGINT可以允许更多。

- Naxia

Answer 8

一个简单的解决方案是使用表格的max(id)来设置id。

INSERT INTO my_table (id, value) VALUES (
 (SELECT MAX(id) FROM my_table x) + 1, 
 'some unique value'
) 
ON DUPLICATE KEY UPDATE email = VALUE(email)

- Yanik Lupien

---

警告：在InnoDB中，这是不合法的，因为您不能从正在插入的相同表中使用子查询SELECT，并且自MySQL 5.5.5发布以来，InnoDB是默认设置。 - hanshenrik

@hanshenrik 我刚刚在 MariaDB 11.0 上尝试了一下，使用了一个 InnoDB 表，它可以正常工作。可能是 MySQL 特定的问题。 - undefined

Answer 9

我认为这不是MySQL 5.6的问题。请参见此示例。

- yyFred

Answer 10

如果有重复的键则更新id=LAST_INSERT_ID(id)

- user2757577