MySQL INSERT.... ON DUPLICATE UPDATE - добавляет один к автоинкременту

Я отслеживаю все http_user_agents, которые посещают меня, с простым счетчиком. Ниже вставьте http_user_agent в БД, это поле нечувствительно к регистру и является уникальным. Поэтому, когда мы пытаемся вставить его, и он находит DUPLICATE KEY, он добавляет 1 к полю хитов.

Проблема в том, что мое поле Auto Increment все еще увеличивается, даже если мы не вставляем поле. как я могу предотвратить это?

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

Ответ 1

INSERT ... ON DUPLICATE KEY UPDATE описывается как "вставка смешанного режима" для обработки InnoDB AUTO_INCREMENT. Вставки смешанного режима - это в основном те, в которых известно максимальное количество требуемых значений AUTO_INCREMENT, но количество, которое действительно будет необходимо, не является.

Вставки смешанного режима обрабатываются специально по умолчанию, как описано в docs:

... для "вставок смешанного режима"... InnoDB будет выделять больше значений автоинкремента, чем количество строк, которые должны быть вставлено. Однако все автоматически назначенные значения являются последовательно (и, следовательно, выше) значение автоинкремента, генерируемое последний выполненный предыдущий оператор. Номера "Избыток" потеряны.

Если вы используете InnoDB, ваши альтернативы:

Избегайте INSERT ... ON DUPLICATE KEY UPDATE.
Установите параметр innodb_autoinc_lock_mode в 0, для "традиционного" режима автоинкрементной блокировки, который гарантирует, что все операторы INSERT будут назначить последовательные значения для столбцов AUTO_INCREMENT. Тем не менее, это достигается путем блокировки во время инструкции, поэтому есть потеря производительности, связанная с этим параметром.
(рекомендуется) Игнорируйте пробелы в столбце AUTO_INCREMENT.

Примечание: обработка AUTO_INCREMENT полностью отличается от MyISAM, которая не проявляет этого поведения.

Ответ 2

Механизм хранения должен увеличивать значение AUTO_INCREMENT перед вставкой строки. Он не знает, не встанет ли вставка в этот момент. Он не может просто отменить приращение, потому что одновременно могут быть другие вставки на других соединениях. Это нормальное поведение, а не то, что вы должны (или можете) изменить. Цель AUTO_INCREMENT - предоставить уникальные идентификаторы, а не непрерывную последовательность чисел.

Ответ 3

К сожалению, решение находится на уровне приложения, если вы не хотите влиять на идентификаторы auto-increment. Сначала выполните SELECT и подсчитайте строки результатов. Если 0 результатов, INSERT данных. Если больше 0, UPDATE эта строка.