我用一个简单的点击计数器跟踪所有访问我http_user_agents。下面的在数据库中插入http_user_agent,此字段不区分大小写且唯一。因此,当我们尝试插入它并找到一个重复键时,它会在命中字段中添加 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;
INSERT ... ON DUPLICATE KEY UPDATE
被描述为InnoDB AUTO_INCREMENT
处理目的的"混合模式插入"。混合模式插入基本上是已知所需AUTO_INCREMENT
值的最大数量的插入,但实际需要的数量不是。
默认情况下,混合模式插入会专门处理,如 MySQL 文档中所述:
。对于"混合模式插入"...InnoDB将 分配的自动增量值多于要分配的行数 插入。但是,所有自动分配的值都是连续的 生成(因此高于(由 生成的自动增量值 最近执行的上一个语句。"超额"数字是 失去。
如果您使用的是InnoDB,则替代方案是:
- 避免
INSERT ... ON DUPLICATE KEY UPDATE
。 - 将
innodb_autoinc_lock_mode
参数设置为0
,用于"传统"自动增量锁定模式,这保证所有INSERT
语句都将为AUTO_INCREMENT
列分配连续值。但是,这是通过在语句期间锁定来实现的,因此此设置会产生性能损失。 - (推荐(忽略
AUTO_INCREMENT
列中的间隙。
注意:在MyISAM下AUTO_INCREMENT
处理完全不同,MyISAM不表现出此行为。
在插入行之前,存储引擎必须递增AUTO_INCREMENT
值。它不知道插入是否在此时会失败。它不能简单地回滚增量,因为其他连接上可能会同时发生其他插入。这是正常行为,不是您应该(或可以(更改的。AUTO_INCREMENT
的目的是提供唯一的标识符,而不是不间断的数字序列。
如果您不想影响自动增量 id,则解决方案是在应用程序级别。首先执行SELECT
并计算结果行。如果结果为 0,则INSERT
数据。如果大于 0,则UPDATE
该行。
您可以先计算插入的最大行数并向其加 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';