mysql - 如何更新一行并在不存在时插入一行，而不会错误地提高 auto_increment

Question

我有一个带有主自动递增 id、唯一键（水果）和属性字符串（颜色）的表

+-------------+-----------+--------+
| id          | fruit     | color  |
+-------------+-----------+--------+
| 1           | apple     | green  | 
| 2           | banana    | yellow |
+-------------+-----------+--------+

现在，我想将项目添加到我的颜色数据库中。如果水果在我的数据库中，我想更新现有颜色，如果不是，我想添加一个新行。默认方法是使用INSERT ... ON DUPLICATE KEY UPDATE ...，但这会对 ID 列产生不希望的副作用：

INSERT into table(fruit,color) VALUES ('apple','red') ON DUPLICATE KEY UPDATE color='red' INSERT into table(fruit,color) VALUES ('kiwi','pink') ON DUPLICATE KEY UPDATE color='pink'

这导致了一个表

+-------------+-----------+--------+
| id          | fruit     | color  |
+-------------+-----------+--------+
| 1           | apple     | red    | 
| 2           | banana    | yellow |
| 4           | kiwi      | pink   |
+-------------+-----------+--------+

因此在 auto_increment 中有一个间隙。我知道这是为什么（首先插入触发器，引发计数器，然后在失败时调用更新）但我的问题是我可以使用什么来代替我的 id 保持连续？

我已经尝试在每条语句之后更新我的计数器，SET @m=(SELECT MAX(id)+1 FROM table);SET @s=CONCAT('ALTER TABLE table AUTO_INCREMENT=',@m);PREPARE st FROM @s;EXECUTE st;DEALLOCATE PREPARE st;但是虽然这具有预期的效果，但在每条语句之后都这样做确实不利于性能。

我在INSERT IGNORE这里找到了一个相当不错的解决方案：https ://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/所以我我希望INSERT ... ON DUPLICATE KEY UPDATE存在类似的东西。

标签： mysqlsqlmariadbauto-incrementon-duplicate-key