Prevenir el incremento automático en MySQL duplicado insertar

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 uso de la consulta, INSERT IGNORE INTO tablename SET tag="whatever", el incremento automático id valor aumenta incluso si el inserto fue ignorado.

Normalmente esto no sería un problema, pero me espera una gran cantidad de intentos posibles para insertar duplicados para esta tabla en particular lo que significa que mi siguiente valor para id campo de una nueva fila será saltar demasiado.

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

1   | test
8   | testtext
678 | testtextt

También, si no hago INSERT IGNORE y no regular INSERT INTO y controlar el error, el incremento automático de campo aumenta así que la próxima verdadero insertar todavía es un mal de incremento automático.

Hay una forma de detener el incremento automático si hay un INSERT de filas duplicadas intento?

Como yo lo entiendo para MySQL 4.1, este valor no se incremente, pero la última cosa que quiero hacer es terminar haciendo un montón de SELECT declaraciones con antelación para comprobar si las etiquetas de existir, o, peor aún, la rebaja de mi versión de MySQL.

  • Está usted usando InnoDB? Si es así consulte stackoverflow.com/questions/2787910/…
  • ah, sí. acabo de hacer una edición rápida. voy a comprobar el enlace tnx
  • sólo un seguimiento sobre el enlace. lamentablemente no soluciona realmente el problema, sino los intentos de un bigint para el campo id para evitar que la tabla de volar. Gracias
InformationsquelleAutor robert | 2011-05-07

8 Kommentare

  1. 22

    Puede modificar su INSERCIÓN a ser 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 (debidamente citado o como un marcador de posición de curso) a la que desea agregar si no está ya allí. Este enfoque no incluso desencadenar una INSERCIÓN (y el subsiguiente incremento automático desperdicio) si la etiqueta ya está ahí. Usted probablemente podría venir para arriba con mejor SQL que esto, pero el de arriba debe hacer el truco.

    Si la tabla se indexe correctamente, a continuación, el extra que se SELECCIONE para la comprobación de existencia será rápido y la base de datos va a tener que realizar la verificación de todos modos.

    Este enfoque no funciona para la primera etiqueta, aunque. Usted podría semilla de su tabla de etiquetas con una etiqueta que usted piensa que siempre va a terminar siendo utilizado o se podría hacer un cheque por separado para una mesa vacía.

    • estaba tratando de evitar que, pero tengo una idea. ejecute la siguiente instrucción sql después de cada inserción. restablece el incremento automático de identificación para el último realmente utilizado. ALTER TABLE etiquetas AUTO_INCREMENT = 1 posibles problemas con esto. parece provocar una actualización para todas las filas, pero los identificadores no son cambiadas. devuelve filas afectadas xx que es el número de registros en la tabla. aparte de esto puedo ver que tiene una tabla ficticia almacenar el número de etiquetas y el contador será actualizado cuando las nuevas etiquetas, se añade. Así que en lugar de utilizar autoincrement , voy a ser la gestión de mi propio id.
    • Usted se encontrará con problemas de concurrencia si intenta administrar su propio Id. O usted tendrá que hacer un montón de bloqueo de tabla para simular el acceso restringido a la auto-incrementar hace por sí mismo. Por qué estás tratando de evitar la comprobación de duplicados antes de la INSERCIÓN? Has comprobado que no hay un real impacto en el rendimiento?
    • no supongo nada demasiado serio , simplemente parecía una mala práctica. Hablando de lo cual, el tiempo de ejecución para MODIFICAR la TABLA de etiquetas AUTO_INCREMENT = 1 es peor.
    • Gran solución, tuvo algunos problemas para llegar a insertar una fila inicialmente. He encontrado que si la tabla que se selecciona es el vacío, la inserción no va a suceder.
    • Tienes razón, una tabla vacía puede ser un problema. Creo que la cosa más fácil de hacer es agregar manualmente una etiqueta en la mesa en el momento de crear, algo que sabes que va a utilizar. Por lo general ir a la opción «agregar una restricción unique, ciegamente inserción de etiquetas, captura/ignore los errores esperados», la ruta y no preocuparse por los valores de incremento automático.
    • Buena sugerencia. Pero ¿por qué dice que no va a insertar en una tabla vacía? Lo he probado y funciona. (Llegado a este post mientras estaba buscando la misma consulta).
    • El problema es el primer from tablename. Si la tabla está vacía, a continuación, que le da «inexistente» en lugar de un conjunto vacío. No podía ser de versión de MySQL problemas, yo estoy comprobando con 5.5.
    • Tiene su punto. He intentado esto con 5.5, Percona construir y no la cara.
    • los insertos fueron fallando para mí si la mesa estaba vacía, he modificado ligeramente la consulta de inserción y publicado como respuesta a continuación, darle una oportunidad si usted experimenta el fracaso en una tabla vacía situación
    • El LIMIT 1 es necesario porque la subconsulta genera una tabla temporal que contiene tantas filas como el original de la misma tabla. No esta problemática, o, al menos, una mala práctica? Por ejemplo, la tabla temporal se contienen millones de filas, si la tabla original contiene millones de filas.
    • Hace MySQL generar una tabla temporal o es lo suficientemente inteligente como para saber lo que «existe» significa?
    • ¿Por qué EXISTS o NOT EXISTS tiene impacto alguno sobre si una tabla temporal se crea?
    • Depende de qué tan inteligente es el optimizador de consultas. ¿Por qué ir al esfuerzo de la generación de una tabla temporal cuando todo lo que realmente importa es si o no que algo iba a estar en ella?
    • En realidad, después de una investigación, la LIMIT 1 se aplica a la subconsulta (SELECT declaración), no a la consulta externa (INSERT declaración). Por lo tanto, no hay necesidad de agregar un adicional de LIMIT 1 a la subconsulta, puesto que ya está allí.

  2. 14

    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 configurable de Incremento Automático de Bloqueo. Véase también http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc

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

    • Curiosamente, no tradicionales, que todavía incrementos para mí, usar INSERT..ON DUPLICATE KEY UPDATE para MySQL 5.5.41
    • Si usted usa INSERT IGNORE y la fila es ignorada, el LAST_INSERT_ID() permanece invariable desde el valor actual (o se devuelve 0 si la conexión no se ha realizado una exitosa INSERCIÓN) devolverá 0 si la acción no preformado
  3. 13

    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 affectedRows = 1, a continuación, se inserta; de lo contrario, si affectedRows = 0 no era un duplicado.

    • De niza. Básicamente el mismo que Landon solución, pero más elegante usando el DOBLE falso de tabla.
  4. 12

    He encontrado mu es demasiado corta respuesta útil, pero limitando porque no hacer inserciones en una tabla vacía. Me encontré con 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

    Colocación de la tabla en la cláusula from, con un «falso» tabla (select 1) as a permitido que parte para devolver un registro que permite la inserción a tener lugar. Estoy ejecutando mysql 5.5.37. Gracias mu para llegar a más de mí de la manera allí ….

    • NO, no. Fue mi culpa: «etiqueta» es el nombre de la columna y «$etiqueta de» el valor de la columna 🙂
    • Muy bonito, gracias por eso. No podía entender por un tiempo ¿por qué mi código no funciona con mesas vacías.
  5. 3

    Usted puede agregar siempre ON DUPLICATE KEY UPDATE Leer aquí (no exactamente, pero resuelve el problema parece).

    De los comentarios, por @ravi

    Si el incremento se produce o no depende de la
    innodb_autoinc_lock_mode configuración. Si se establece en un valor distinto de cero, el
    auto-inc contador aumentará incluso si la CLAVE DUPLICADA incendios

    • si usted tiene una clave única en esa tabla, e intenta insertar con …EN DUPLICADO… y la misma clave única, no hay incremento, si lo hace, ya sea entendido mal el uno del otro, o su MySQL tiene un error?
    • nope. hace incremento. si la siguiente instrucción se ha duplicado —– INSERTAR EN las etiquetas (tag_text) VALORES (cur_string) ON DUPLICATE KEY UPDATE tag_text=cur_string ———– a continuación, el siguiente reales de inserción que no es un duplicado tendrá id como se describe en el problema. Creo que es más como una característica de mysql parte, más que un error. un poco extraño, aunque
    • Muy interesante, estoy usando mucho esto, ¿qué versión de MySQL (si se puede, una exacta) estás usando?
    • mysql 5.1.49 y la tabla innodb. Usted podría estar usando mysql 4.1 que creo que es la versión en la que el no aumento de incremento automático se considera realmente un error. O. o
    • no, yo soy un sabor de 5.1, voy a ver de nuevo, podría ser que estoy equivocado.
    • Estoy teniendo el mismo problema. bugs.mysql.com/bug.php?id=28781 parece ser todavía activo.
    • Si el incremento se produce o no depende de la innodb_autoinc_lock_mode configuración. Si se establece en un valor distinto de cero, el auto-inc contador aumentará incluso si la CLAVE DUPLICADA incendios.

  6. 1

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

    Para resolver este problema terminé usando una tabla temporal.

    create temporary table mytable_temp like mytable;

    A continuación, insertar los valores con:

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

    Después de que usted simplemente no hacen otra inserte pero el uso de «no» 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);

    Yo no he probado esto para el rendimiento, pero hace el trabajo y es fácil de leer. Concedido esto sólo fue importante porque estaba trabajando con datos que se actualiza constantemente por lo que no podía ignorar las lagunas.

  7. 1

    La aceptada respuesta fue útil, sin embargo me encontré con un problema al usar el que, básicamente, si la tabla no tenía entradas que no iba a funcionar como seleccionar estaba usando la tabla de la base, en lugar de eso, se me ocurrió la siguiente, que va a insertar incluso si la tabla está vacía, sólo necesita insertar la tabla en 2 lugares y la inserción de variables en 1 lugar, menos a 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*/

    Espero que les sea útil

  8. -1

    Acabo de poner un extra de instrucción después de la inserción/actualización de la consulta:
    ALTER TABLE table_name AUTO_INCREMENT = 1
    Y, a continuación, se selecciona automáticamente la mayor prim clave de identificación de más de 1.

    • eso no es una buena solución. He intentado eso y es muy lento

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea