registro - mysql español

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

Question

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 intentos 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 intento 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 etiquetas existen, o peor aún, rebajar mi versión de MySQL.

Answer 1

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.

Answer 2

Acabo de poner una declaración 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.

Answer 3

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.

Answer 4

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 ...

Answer 5

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 bloqueo 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).

Answer 6

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 i.a = o.a AND i.b = o.b /*condition to not insert for*/
WHERE
    o.a IS NULL
LIMIT 1 /*Not needed as can only ever be one, just being sure*/

Esperamos que te sea útil

Answer 7

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 etiquetas con una etiqueta que creas que siempre terminará usándose o podrías hacer una verificación por separado para una tabla vacía.

Answer 8

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 aumentará incluso si se activa la tecla ON DUPLICATE KEY

Answer 9

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.