MySQL INSERT .... ON重复更新-向自动增量添加1 [英] MySQL INSERT ....ON DUPLICATE UPDATE - Adds one to the autoincrement

问题描述

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

I keep track of all the http_user_agents that visit me, with a simple hit counter. The below insert the http_user_agent in the DB, this field is Case Insensitive and is Unique. So when we try to insert it and it finds a DUPLICATE KEY, it adds 1 to the hits field.

问题是，即使我们没有插入字段，我的自动增量"字段仍会增加.我该如何预防?

The problem is my Auto Increment field still increases even though we did not insert a field. how can i prevent this?

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

为了InnoDB的AUTO_INCREMENT处理目的，

推荐答案

INSERT ... ON DUPLICATE KEY UPDATE被描述为混合模式插入".基本上，混合模式插入是所需AUTO_INCREMENT值的 maximum 个最大数目，但实际上所需的个数目却不是.

INSERT ... ON DUPLICATE KEY UPDATE is described as a "mixed-mode insert" for the purposes of InnoDB's AUTO_INCREMENT handling. Mixed-mode inserts are basically ones where the maximum number of required AUTO_INCREMENT values is known, but the amount that will actually be needed is not.

默认情况下，混合模式插入会特别处理，如 MySQL文档:

Mixed-mode inserts get handled specially by default, as described in the MySQL docs:

...对于混合模式插入" ... InnoDB将 分配的自动增量值多于要增加的行数 已插入.但是，所有自动分配的值都是连续的 生成(因此高于)由生成的自动增量值 最近执行的上一个语句. 多余"的数字是 迷路了.

...for "mixed-mode inserts"... InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. "Excess" numbers are lost.

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

If you're using InnoDB, your alternatives are:

1. 避免使用INSERT ... ON DUPLICATE KEY UPDATE.
2. 设置 innodb_autoinc_lock_mode 参数到0，对于传统"自动增量锁定模式，该模式保证所有INSERT语句将为AUTO_INCREMENT列分配连续的值.但是，这是通过在语句期间锁定来完成的，因此，此设置会导致性能损失.
3. (推荐)忽略AUTO_INCREMENT列中的空白.

1. Avoid INSERT ... ON DUPLICATE KEY UPDATE.
2. Set the innodb_autoinc_lock_mode parameter to 0, for "traditional" autoincrement lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting.
3. (Recommended) Ignore the gaps in the AUTO_INCREMENT column.

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

Note: AUTO_INCREMENT handling is totally different under MyISAM, which does not exhibit this behavior.

这篇关于MySQL INSERT .... ON重复更新-向自动增量添加1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！