numero - mysql insert auto_increment

¿MySQL reutilizará las ID eliminadas cuando se aplique Auto Incremento?

Question

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Ese documento que estoy leyendo parece decir algo como:

"En este caso (cuando la columna AUTO_INCREMENT forma parte de un índice de varias columnas), los valores de AUTO_INCREMENT se reutilizan si elimina la fila con el mayor valor de AUTO_INCREMENT en cualquier grupo".

Realmente no entiendo lo que se dice allí. ¿No se supone que los valores se reutilizarán automáticamente?

Gracias por adelantado...

Answer 1

Como se describe con el motor InnoDB, la última PK utilizada se reutilizará si la fila se elimina antes del reinicio, ya que no se almacena en caché. Si esta PK es una clave externa (FK) en otra tabla, pueden surgir problemas (infierno FK). Así es como descubrí el problema cuando una viejecita disparó hasta 195 cm (!) Cuando se recogieron los datos adicionales de la persona eliminada. La solución para esto es implementar ''ON DELETE CASCADE'' para las tablas secundarias o (no es mi opción preferida) para codificar este problema.

Answer 2

Como se mencionó en la answer :

InnoDB restablece el campo auto_increment cuando reinicia la base de datos.

Cuando InnoDB se reinicia, encuentra el valor más alto en la columna y luego comienza desde allí.

y este comportamiento podría llevar a problemas de clave externa.

Afortunadamente desde MySQL 8.0.0 será arreglado. Más información:

Error # 199 Innodb estadísticas de autoincremento en reinicio

WL # 6204: Valor máx. Persistente de InnoDB para columnas autoinc.

Ver BUG # 199 en errores de MySQL.

Actualmente, InnoDB hace lo siguiente cuando se abre una tabla: SELECT MAX (c) FROM t; donde c es el nombre de la columna AUTOINC. Este paso se usa para inicializar el siguiente valor de autoinc de la columna y la asignación de los valores de autoinc comienza desde este punto. InnoDB también hace esto cuando ejecuta ''ALTER TABLE AUTO_INCREMENT = N;''.

...

InnoDB debe realizar un seguimiento del valor máximo y, al reiniciar, debe conservar ese valor máximo y comenzar desde allí.

Answer 3

Cuando tiene un campo de clave principal que también es auto_increment, no importa cuántas filas (o en qué orden) se eliminen, los identificadores que ya no existen no se utilizan nuevamente, y el campo se incrementa continuamente para cada fila.

Sin embargo, cuando su clave principal consta de varios campos (por ejemplo, un campo auto_increment y otro campo), el campo auto_increment se reutiliza, y solo cuando la ID eliminada es la última ID. Lo que esto significa es que si tiene valores como 1, 2, 3, 4 y 5, y elimina la fila con el valor de campo de 5, la siguiente fila tendrá un ID de 5. Sin embargo, si elimina la fila con el id de 2, esto, de nuevo, no se utilizará, y la siguiente fila tendrá un id de 6.

Answer 4

InnoDB restablece el campo auto_increment cuando reinicia la base de datos.

Cuando InnoDB se reinicia, encuentra el valor más alto en la columna y luego comienza desde allí.

Esto no sucederá en MyISAM porque almacena en caché la última identificación incrementada.

Actualizar

Esta característica / error ha existido desde 2003 y puede llevar a problemas graves. Tomemos el ejemplo de abajo,

1. La tabla t1 tiene una clave primaria auto-inc.

2. La tabla t2 tiene una columna para la clave primaria en t1 sin una "clave" de clave externa. En otras palabras, cuando se elimina una fila en t1, las filas correspondientes en t2 quedan huérfanas.

3. Como sabemos con el reinicio de InnoDB, se puede volver a emitir una ID. Por lo tanto, las filas huérfanas en t2 se pueden vincular falsamente a nuevas filas en t1.

Este error finalmente se solucionó en MySQL 8.0.0 WL # 6204 - Valor máximo persistente InnoDB para columnas autoinc .

InnoDB hará un seguimiento del valor máximo y al reiniciarlo conservará ese valor máximo y comenzará desde allí.

Answer 5

Si la tabla es de tipo InnoDB, los identificadores eliminados se reutilizan en ciertos escenarios. Esto es lo que hice para probarlo:

1. El usuario de la tabla tiene una columna de ID como clave principal, con auto_increment set

   CREAR TABLA user ( id bigint (20) NOT NULL AUTO_INCREMENT, CLAVE PRIMARIA ( id ),) MOTOR = InnoDB AUTO_INCREMENT = 2108 CHARLA DEFAULT = latin1;

2. Mi llamada a la API createUser, crea una entrada en la tabla de usuarios. Digamos que el usuario con id = 1 fue creado.

3. Llame a la API deleteUser que solo elimina la fila de la tabla de usuarios.

4. Detener e iniciar la base de datos.

5. Llame al createUserAPI call nuevamente. Crea usuario con la misma id (id = 1).

Por lo tanto, utiliza la ID eliminada, si se reinicia el servidor. Obtuve este problema porque tenía otra tabla llamada user_rules que almacenaba user_id en una columna, pero no tenía una referencia de FK a la tabla de usuario (que era incorrecta). Otro problema fue que cuando el usuario se eliminaba, la entrada de la tabla user_rules no se eliminaba y, como no tenía un conjunto de FK, db tampoco se quejó. ¡Y cuando el servidor se reinició, las reglas de usuario se desordenaron!