MySQL INSERT ....ON DUPLICATE UPDATE - 给自增字段加一

Question

我通过简单的点击计数器追踪所有访问我的网站的http_user_agent。下面的代码将http_user_agent插入数据库。该字段不区分大小写且唯一。因此，当我们尝试插入它时，如果找到重复键，则会将hits字段加1。

问题是，即使我们没有插入任何字段，自动增量字段仍然会增加。如何防止这种情况发生？

$sql = "INSERT INTO `db_agency_cloud`.`tblRefHttpUsersAgent` SET `http_users_agent` = :UsersAgent, `created_ts` = NOW() ON DUPLICATE KEY UPDATE `hits` = `hits` + 1";

以下是表格结构：

CREATE TABLE `tblRefHttpUsersAgent`
(
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`http_users_agent` varchar(255) NOT NULL,
`hits` int(20) unsigned NOT NULL DEFAULT '1',
`created_ts` datetime NOT NULL,
`activity_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `http_users_agent` (`http_users_agent`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

- M. of CA

---

可能相关：https://dev59.com/tXE85IYBdhLWcg3wvGKm - Lightness Races in Orbit

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE 被称为 InnoDB 中用于处理 AUTO_INCREMENT 的“混合模式插入”。混合模式插入基本上是指需要的AUTO_INCREMENT值的最大数量已知，但实际需要的数量未知。

按照默认设置，混合模式插入被特别处理，正如MySQL文档所述：

......对于“混合模式插入”……InnoDB将分配超过要插入行数的AUTO_INCREMENT值。然而，所有自动分配的值都是连续生成的（因此高于）之前最近执行的语句生成的自增值。多余的数字会丢失。

如果您使用InnoDB，您有以下选择：

1. 避免使用 INSERT ... ON DUPLICATE KEY UPDATE。
2. 将innodb_autoinc_lock_mode参数设置为0，即传统的自增锁定模式，可以保证所有INSERT语句都为AUTO_INCREMENT列分配连续的值。然而，这是通过在语句期间进行锁定来实现的，因此与此设置相关联的性能损失。
3. (建议) 忽略 AUTO_INCREMENT 列中的间隔。

注意：MyISAM 下的 AUTO_INCREMENT 处理方式完全不同，不会表现出这种行为。

- John Flatness

---

尝试在运行时设置innodb_autoinc_lock_mode时，我遇到了错误：SQLSTATE[HY000]: General error: 1238 Variable 'innodb_autoinc_lock_mode' is a read only variable'。考虑到我需要用它来进行批量UPSERT操作，因为单个插入操作非常慢。我不能使用选项1、2或3，因为这会导致自增ID产生大量的间隔。你能给出其他的解决方案吗？ - Timo Huovinen

如果我使用PostgreSQL，是否会观察到类似的自动递增字段间隙？ - W.M.

非常感谢您的详细解释。虽然您推荐了第三个选项，但在我的情况下不适用，因为我们尝试在同时进行的不同事务中执行多个“INSERT ... ON DUPLICATE KEY UPDATE”，这会导致不断发生死锁。这只是我的一些建议，请记住。 - recepinanc

忽略空白是一回事，但每天必须将我的ID列更改为BIGINT，以便能够运行数千个“INSERT ... ON DUPLICATE KEY UPDATE”查询，这最多是令人烦恼的。 - twhitney

Answer 2

在插入行之前，存储引擎必须递增AUTO_INCREMENT值。此时，它并不知道插入是否会失败。它无法简单地回滚增量，因为可能会同时发生其他连接上的插入。这是正常行为，不应该（也无法）更改。 AUTO_INCREMENT的目的是提供唯一标识符，而不是连续的数字序列。

- Dan Grossman

---

是的，但我不想对数据库进行两次调用。我希望有一个跳过这个步骤的解决方案。我在考虑使用if语句，在数据库层面检查是否应该插入或更新记录。 - M. of CA

+1：很有趣！好的，我明白了为什么会发生这种情况（感谢你的回答）；尽管如此，它似乎非常不幸。这一点根本不直观，我甚至认为这是一个错误（无论是在SQL标准中还是在MySQL中）......即使它总是会被解决为“WONTFIX”。 - Lightness Races in Orbit

嗯。如果您使用INSERT IGNORE并且该行被忽略，则AUTO_INCREMENT计数器不会递增，并且LAST_INSERT_ID()返回0，这反映没有插入任何行。它在那里进行管理。有差异吗？ - Lightness Races in Orbit

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html 让我有点困惑。它似乎没有提到任何与更新AUTO_INCREMENT计数器或其他相关的内容，但是，最后一部分关于使LAST_INSERT_ID()有用的内容让我完全无法理解。我不知道它在说什么。 - Lightness Races in Orbit

@Tomalak：这个东西讲的是当你向 LAST_INSERT_ID() 传递参数时它所执行的操作：它返回那个值，并设置那个值作为下一次调用 LAST_INSERT_ID() 应该返回的值。因此，包含在查询中的 LAST_INSERT_ID() 调用强制随后的 LAST_INSERT_ID() 调用返回语句影响行的 ID，无论是插入新行（这已经可以正常工作）还是更新旧行。 - John Flatness

@DanGrossman：尽管如此，这仍然很烦人并且感觉有点凌乱。 直觉是“呃，我没有使用所有这些可怜的ID，真是浪费”。 - Lightness Races in Orbit

Answer 3

如果您不想影响自增ID，则解决方案很遗憾地在应用程序级别上。首先执行一个SELECT，然后计算结果行数。如果结果是0，则INSERT数据。如果大于0，则UPDATE该行。

- Eliot

---

是的，但我不想对数据库进行两次调用。我希望有一个跳过这个步骤的解决方案。我在考虑使用if语句，在数据库层面检查是否应该插入或更新记录。 - M. of CA

Answer 4

你可以先计算可插入行的最大数量，然后再加1。

(SELECT MAX(`id`)+1 FROM `tblRefHttpUsersAgent`)

然后使用一些变量 SET @NEW_ID 和 PREPARE / EXECUTE 语句来更改表格的 AUTO_INCREMENT。

这里 是同样问题的一个简单解决方案，如果您喜欢自己特定问题的解决方案，请参考下面的完成版本：

    $sql = 'SET @NEW_AI = (SELECT MAX(`id`)+1 FROM `tblRefHttpUsersAgent`);
                    SET @ALTER_SQL = CONCAT("ALTER TABLE `tblRefHttpUsersAgent` AUTO_INCREMENT =", @NEW_AI);
                    PREPARE NEWSQL FROM @ALTER_SQL;
                    EXECUTE NEWSQL;';

    $sql .= 'INSERT INTO `db_agency_cloud`.`tblRefHttpUsersAgent` SET `http_users_agent` = :UsersAgent, `created_ts` = NOW() ON DUPLICATE KEY UPDATE `hits` = `hits` + 1';

- ali asghar tofighian