Previene l’incremento automatico sull’inserto duplicato MySQL

Utilizzando MySQL 5.1.49, sto cercando di implementare un sistema di tagging il problema che ho è con una tabella con due colonne: id(autoincrement) , tag(unique varchar) (InnoDB)

Quando si utilizza la query INSERT IGNORE INTO tablename SET tag="whatever" , il valore id incremento automatico aumenta anche se l’inserimento è stato ignorato.

Normalmente questo non sarebbe un problema, ma mi aspetto un sacco di possibili tentativi di inserire duplicati per questa particolare tabella, il che significa che il mio prossimo valore per il campo id di una nuova riga salterà troppo.

Per esempio, finirò con una tabella con 3 righe, ma con id non validi

 1 | test 8 | testtext 678 | testtextt 

Inoltre, se non faccio INSERT IGNORE e non INSERT INTO regolarmente INSERT INTO e gestisco l’errore, il campo di incremento automatico aumenta ancora, quindi il prossimo vero inserimento è ancora un incremento automatico errato.

C’è un modo per fermare l’incremento automatico se c’è un tentativo di riga duplicata INSERT ?

Come capisco per MySQL 4.1, questo valore non aumenterebbe, ma l’ultima cosa che voglio fare è finire a fare un sacco di istruzioni SELECT in anticipo per verificare se i tag esistono o, peggio ancora, effettuare il downgrade della mia versione di MySQL.

Puoi modificare il tuo INSERT per essere qualcosa del genere:

 INSERT INTO tablename (tag) SELECT $tag FROM tablename WHERE NOT EXISTS( SELECT tag FROM tablename WHERE tag = $tag ) LIMIT 1 

Dove $tag è il tag (correttamente quotato o come segnaposto ovviamente) che si desidera aggiungere se non è già lì. Questo approccio non attiverà nemmeno un INSERT (e il successivo spreco di autoincremento) se il tag è già presente. Probabilmente potresti venire con SQL più carino di quello, ma quanto sopra dovrebbe fare il trucco.

Se la tua tabella è correttamente indicizzata, la SELEZIONE extra per il controllo dell’esistenza sarà veloce e il database dovrà comunque eseguire tale controllo.

Tuttavia, questo approccio non funzionerà per il primo tag. Puoi seminare la tabella dei tag con un tag che a tuo avviso verrà sempre utilizzato o che potresti eseguire un controllo separato per una tabella vuota.

La documentazione di MySQL per v 5.5 dice:

 "If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is **not** incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted." 

Rif: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

Dalla versione 5.1 InnoDB ha il blocco autoincremento configurabile. Vedi anche http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc

Soluzione: utilizzare l’opzione innodb_autoinc_lock_mode = 0 (tradizionale).

Ho appena trovato questa gem …

http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/

 INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL WHERE NOT EXISTS( SELECT [column1] FROM [same table name] WHERE [column1]='[value1]' AND [column2]='[value2]' LIMIT 1 ) 

Se affectedRows = 1, quindi inserito; altrimenti se affectedRows = 0 c’era un duplicato.

Ho trovato che mu è una risposta troppo corta, ma limitante perché non fa inserimenti su un tavolo vuoto. Ho trovato una semplice modifica ha fatto il trucco:

 INSERT INTO tablename (tag) SELECT $tag FROM (select 1) as a #this line is different from the other answer WHERE NOT EXISTS( SELECT tag FROM tablename WHERE tag = $tag ) LIMIT 1 

Sostituire la tabella nella clausola from con una tabella “falsa” (select 1) as a consentita a quella parte di restituire un record che consentiva l’inserimento. Sto eseguendo mysql 5.5.37. Grazie mu per avermi preso la maggior parte del modo lì ….

Puoi sempre aggiungere ON DUPLICATE KEY UPDATE Leggi qui (non esattamente, ma risolve il tuo problema a quanto pare).

Dai commenti, di @ravi

Se l’incremento si verifica o meno dipende dall’impostazione innodb_autoinc_lock_mode. Se impostato su un valore diverso da zero, il contatore di autoincremento verrà incrementato anche se viene triggersto il DUPLICATE KEY ON

Ho avuto lo stesso problema ma non volevo usare innodb_autoinc_lock_mode = 0 poiché mi sembrava di uccidere una mosca con un obice.

Per risolvere questo problema ho finito per usare una tabella temporanea.

 create temporary table mytable_temp like mytable; 

Quindi ho inserito i valori con:

 insert into mytable_temp values (null,'valA'),(null,'valB'),(null,'valC'); 

Dopo di che fai semplicemente un altro inserto ma usa “not in” per ignorare i duplicati.

 insert into mytable (myRow) select mytable_temp.myRow from mytable_temp where mytable_temp.myRow not in (select myRow from mytable); 

Non ho provato questo per le prestazioni, ma fa il lavoro ed è facile da leggere. Certo, era importante solo perché lavoravo con dati che venivano costantemente aggiornati, quindi non potevo ignorare le lacune.

La risposta accettata è stata utile, tuttavia durante l’utilizzo mi sono imbattuto in un problema che fondamentalmente se la tua tabella non aveva voci, non funzionava in quanto la selezione utilizzava la tabella data, quindi ho trovato il seguente, che verrà inserito anche se il tavolo è vuoto, ha solo bisogno di inserire la tabella in 2 punti e le variabili di inserimento in 1 posto, meno per sbagliare.

 INSERT INTO database_name.table_name (a,b,c,d) SELECT i.* FROM (SELECT $a AS a, $b AS b, $c AS c, $d AS d /*variables (properly escaped) to insert*/ ) i LEFT JOIN database_name.table_name o ON ia = oa AND ib = ob /*condition to not insert for*/ WHERE oa IS NULL LIMIT 1 /*Not needed as can only ever be one, just being sure*/ 

Spero che tu lo trovi utile

Ho appena inserito un’istruzione extra dopo la query di inserimento / aggiornamento: ALTER TABLE table_name AUTO_INCREMENT = 1 E quindi recupera automaticamente l’ID della chiave Prim più alto più 1.

Vecchia query che aumenta il numero di incremento automatico:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) VALUES ('[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]'); 

Nuova query per verificare se il valore esiste per 1 colonna:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) SELECT '[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]' FROM DUAL WHERE NOT EXISTS( SELECT [column1] FROM [my_table_name] WHERE [column1]='[col_value1]' LIMIT 1 ); 

Nuova query per verificare se esistono valori per 2 o più colonne:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) SELECT '[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]' FROM DUAL WHERE NOT EXISTS( SELECT [column1], [column2] FROM [my_table_name] WHERE [column1]='[col_value1]' AND [column2]='[col_value2]' LIMIT 1 ); 

Quando $mysqli->affected_rows restituisce 1, allora è stato inserito, altrimenti c’è stato un duplicato.