MySQL INSERT…ON重复更新-向自动增量添加1

Question

我通过一个简单的计数器来跟踪访问我的所有http_user_agents。下面在数据库中插入http_user_agent，此字段不区分大小写，并且是唯一的。因此，当我们尝试将其插入并找到一个DUPLICATE
KEY时，会将1加到hits字段。

问题是即使我们没有插入字段，我的“自动增量”字段仍会增加。我该如何预防呢？

$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;

Answer 1

INSERT ... ON DUPLICATE KEY UPDATE为了InnoDB的AUTO_INCREMENT处理目的，它被称为“混合模式插入”
。基本上，混合模式插入是已知所需值的 最大 数量的插入AUTO_INCREMENT，但 实际上所需的 数量不是。

混合模式插入默认情况下会特别处理，如MySQL
docs中所述
：

…对于“混合模式插入” …
InnoDB将分配比要插入的行数更多的自动增量值。但是，所有自动分配的值都是连续生成的（因此高于最近执行的先前语句生成的自动增量值）。“多余”的数字丢失。

如果您使用的是InnoDB，则可以选择：

1. 避免INSERT ... ON DUPLICATE KEY UPDATE。
2. 对于“传统”自动增量锁定模式，请将innodb_autoinc_lock_mode参数设置为0，以确保所有INSERT语句将为AUTO_INCREMENT列分配连续的值。但是，这是通过在语句期间锁定来完成的，因此，此设置会导致性能下降。
3. （推荐）忽略AUTO_INCREMENT列中的间隙。

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