Evitar el incremento automático en la inserción duplicada de MySQL

Usando MySQL 5.1.49, estoy tratando de implementar un sistema de etiquetado, el problema que tengo es con una tabla con dos columnas: id(autoincrement) , tag(unique varchar) (InnoDB)

Al utilizar la consulta, INSERT IGNORE INTO tablename SET tag="whatever" , el valor del id incremento automático aumenta incluso si se ignoró el inserto.

Normalmente, esto no sería un problema, pero espero muchos bashs posibles de insertar duplicados para esta tabla en particular, lo que significa que mi próximo valor para el campo de id de una nueva fila estará aumentando demasiado.

Por ejemplo, voy a terminar con una tabla con decir 3 filas pero malas id ‘s

 1 | test 8 | testtext 678 | testtextt 

Además, si no lo hago, INSERT IGNORE y solo hacer INSERT INTO regular y manejar el error, el campo de incremento automático aún aumenta, por lo que la siguiente inserción verdadera sigue siendo un incremento automático incorrecto.

¿Hay alguna manera de detener el incremento automático si hay un bash de filas duplicadas INSERT ?

Como entiendo para MySQL 4.1, este valor no se incrementaría, pero lo último que quiero hacer es terminar haciendo muchas declaraciones SELECT por adelantado para verificar si las tags existen, o peor aún, rebajar mi versión de MySQL.

Puede modificar su INSERT para que sea algo como esto:

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

Donde $tag es la etiqueta (correctamente cotizada o como un marcador de posición del curso) que desea agregar si aún no está allí. Este enfoque ni siquiera activará un INSERT (y el posterior desperdicio de autoincrement) si la etiqueta ya está allí. Probablemente podrías encontrar SQL más agradable que eso, pero lo anterior debería ser el truco.

Si su tabla está indexada correctamente, la SELECCIÓN extra para la verificación de existencia será rápida y la base de datos tendrá que realizar esa comprobación de todos modos.

Sin embargo, este enfoque no funcionará para la primera etiqueta. Puedes sembrar tu tabla de tags con una etiqueta que creas que siempre terminará usándose o podrías hacer una verificación por separado para una tabla vacía.

La documentación de MySQL para 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." 

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

Desde la versión 5.1, InnoDB tiene locking de auto-incremento configurable. Ver también http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc

Solución alternativa: use la opción innodb_autoinc_lock_mode = 0 (tradicional).

Acabo de encontrar esta joya …

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 ) 

Si afectadoRows = 1, entonces se insertó; de lo contrario, si afectadoRows = 0 hubiera un duplicado.

Encontré que la respuesta de mu es demasiado corta, pero limitante porque no hace inserciones en una tabla vacía. Encontré una simple modificación que hizo el truco:

 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 

Reemplazar la tabla en la cláusula from con una tabla “falsa” (select 1) as a permitido permite que esa parte devuelva un registro que permitió que el inserto tuviera lugar. Estoy ejecutando mysql 5.5.37. Gracias mu por llevarme la mayor parte del camino …

Siempre puede agregar ON DUPLICATE KEY UPDATE Lea aquí (no exactamente, pero al parecer resuelve su problema).

De los comentarios, por @ravi

Si el incremento se produce o no depende de la configuración innodb_autoinc_lock_mode. Si se establece en un valor distinto de cero, el contador de autoincremento boostá incluso si se activa la tecla ON DUPLICATE KEY

Tuve el mismo problema, pero no quería usar innodb_autoinc_lock_mode = 0 ya que sentía que estaba matando una mosca con un obús.

Para resolver este problema, terminé usando una tabla temporal.

 create temporary table mytable_temp like mytable; 

Luego inserté los valores con:

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

Después de eso, simplemente haga otro inserto pero use “no en” para ignorar los duplicados.

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

No he probado esto para el rendimiento, pero hace el trabajo y es fácil de leer. De acuerdo, esto solo era importante porque estaba trabajando con datos que constantemente se actualizaban, así que no podía ignorar las lagunas.

La respuesta aceptada fue útil, sin embargo, me encontré con un problema al usarlo que básicamente, si tu tabla no tenía entradas, no funcionaría ya que la selección estaba usando la tabla dada, así que en lugar de eso se me ocurrió lo siguiente, que se insertará aunque la tabla está en blanco, solo necesita insertar la tabla en 2 lugares y las variables de inserción en 1 lugar, menos para equivocarse.

 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*/ 

Esperamos que te sea útil

Acabo de poner una statement extra después de la consulta de inserción / actualización: ALTER TABLE table_name AUTO_INCREMENT = 1 Y luego recoge automáticamente la Id de la clave prim más alta más 1.

Consulta anterior que aumenta el número de incremento automático:

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

Nueva consulta para verificar si el valor existe para 1 columna:

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

Nueva consulta para verificar si existen valores para 2 o más columnas:

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

Cuando $mysqli->affected_rows devuelve 1, se insertó, de lo contrario hubo un duplicado.