MySQL INSERT ….ON DUPLICATE UPDATE – Fügt dem Autoinkrement eins hinzu

Ich verfolge alle http_user_agents, die mich besuchen, mit einem einfachen Zugriffszähler. Fügen Sie unten den http_user_agent in die DB ein, dieses Feld unterscheidet nicht zwischen Groß- und Kleinschreibung und ist eindeutig. Wenn wir also versuchen, es einzufügen und es findet einen DUPLICATE KEY, fügt es 1 zum Trefferfeld hinzu.

Das Problem ist, dass mein Auto Increment-Feld immer noch zunimmt, obwohl wir kein Feld eingefügt haben. wie kann ich das verhindern?

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

Hier ist die Tabellenstruktur:

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 wird für die Zwecke von InnoDB als “Mixed-Mode-Insert” beschrieben AUTO_INCREMENT Handhabung. Mixed-Mode-Einfügungen sind im Grunde solche, bei denen die maximal Anzahl erforderlich AUTO_INCREMENT Werte ist bekannt, aber die Menge, die wird eigentlich gebraucht werden ist nicht.

Einfügungen im gemischten Modus werden standardmäßig speziell behandelt, wie in beschrieben MySQL-Dokumente:

…für „mixed-mode inserts“… InnoDB weist mehr Auto-Increment-Werte zu als die Anzahl der einzufügenden Zeilen. Alle automatisch zugewiesenen Werte werden jedoch nacheinander generiert (und sind daher höher als) der Autoinkrementwert, der von der zuletzt ausgeführten vorherigen Anweisung generiert wurde. „Überschüssige“ Nummern gehen verloren.

Wenn Sie InnoDB verwenden, sind Ihre Alternativen:

1. Vermeiden INSERT ... ON DUPLICATE KEY UPDATE.
2. Stellen Sie die ein innodb_autoinc_lock_mode Parameter zu 0für den “traditionellen” Autoinkrement-Sperrmodus, der das alles garantiert INSERT Anweisungen weisen fortlaufende Werte für zu AUTO_INCREMENT Säulen. Dies wird jedoch durch Sperren während der Anweisung erreicht, sodass mit dieser Einstellung ein Leistungsverlust verbunden ist.
3. (Empfohlen) Ignorieren Sie die Lücken in der AUTO_INCREMENT Säule.

Notiz: AUTO_INCREMENT Unter MyISAM, das dieses Verhalten nicht zeigt, ist die Handhabung völlig anders.

Die Speicher-Engine muss die inkrementieren AUTO_INCREMENT Wert vor dem Einfügen einer Zeile. Es weiß noch nicht, ob die Einfügung zu diesem Zeitpunkt fehlschlagen wird. Es kann das Inkrement nicht einfach rückgängig machen, da andere Einfügungen gleichzeitig auf anderen Verbindungen stattfinden können. Dies ist ein normales Verhalten und nichts, was Sie ändern sollten (oder können). Der Zweck von AUTO_INCREMENT ist die Bereitstellung eindeutiger Kennungen, nicht einer ununterbrochenen Zahlenfolge.

Leider befindet sich die Lösung auf Anwendungsebene, wenn Sie die Auto-Increment-IDs nicht beeinflussen möchten. Mach ein SELECT zuerst und zähle die Ergebniszeilen. Wenn 0 ergibt, INSERT die Daten. Wenn mehr als 0, UPDATE diese Reihe.

Sie können zuerst die maximale Anzahl der eingefügten Zeilen berechnen und 1 dazu addieren,

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

Dann ändern Sie die Tabelle AUTO_INCREMENT mit einigen Variablen SET @NEW_ID und PREPARE / EXECUTE Aussagen.

Hier ist eine einfache Lösung für dasselbe Problem und unten ist die fertige Version, wenn Ihnen die Lösung Ihres eigenen spezifischen Problems gefällt:

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