Cómo manejar la fragmentación de la columna de autoincreament ID en MySQL

Tengo una tabla con un campo auto_increment y algunas veces las filas se eliminan, por lo que auto_increment deja huecos. ¿Hay alguna forma de evitar esto o, por lo menos, cómo escribir una consulta SQL que:

  1. Altera el valor auto_increment para que sea el máximo (valor actual) + 1
  2. ¿Devuelve el nuevo valor de auto_increment ?

Sé cómo escribir la parte 1 y 2, pero ¿puedo ponerlos en la misma consulta?

Si eso no es posible:

¿Cómo selecciono (regreso) el valor auto_increment o auto_increment value + 1?

La renumeración causará confusión. Los informes existentes se referirán al registro 99 y, sin embargo, si el sistema se renumera puede volver a numerar ese registro a 98, ahora todos los informes (y las IU pobladas) son incorrectos. Una vez que asigna una identificación única, tiene que permanecer fijo.

Usar campos ID para cualquier cosa que no sea la numeración única simple va a ser problemático. Tener un requisito para “no vacíos” es simplemente inconsistente con el requisito de poder desviarse. Quizás podría marcar los registros como eliminados en lugar de eliminarlos. Entonces, realmente no hay lagunas. Digamos que está produciendo facturas numeradas: tendría una factura cancelada de valor cero con un número en lugar de eliminarlo.

Hay una manera de insertar manualmente la identificación incluso en una tabla autoinc. Todo lo que tendrías que hacer es identificar la identificación que falta.

Sin embargo, no hagas esto . Puede ser muy peligroso si su base de datos es relacional. Es posible que la ID eliminada se haya utilizado en otro lugar. Cuando se elimina, no presentaría un gran problema, tal vez huérfano un registro. Si se reemplaza, presentaría un gran problema porque la relación incorrecta estaría presente.

Considera que tengo una mesa de autos y una mesa de personas

 car carid ownerid name person personid name 

Y que hay algunos datos simples

 car 1 1 Van 2 1 Truck 3 2 Car 4 3 Ferrari 5 4 Pinto person 1 Mike 2 Joe 3 John 4 Steve 

y ahora elimino a la persona John.

 person 1 Mike 2 Joe 4 Steve 

Si agregué a una nueva persona, Jim, a la mesa, y él obtuvo una identificación que llenó el espacio, entonces terminaría obteniendo la identificación 3

 1 Mike 2 Joe 3 Jim 4 Steve 

y por relación, sería el dueño del Ferrari.

En general, estoy de acuerdo con los sabios de esta página (y con las preguntas duplicadas ), ya que desaconseja la reutilización de los ID automáticos. Es un buen consejo, pero no creo que nos corresponde a nosotros decidir los derechos o errores de hacer la pregunta, supongamos que el desarrollador sabe lo que quiere hacer y por qué.

La respuesta es, como lo menciona Travis J, puede reutilizar una identificación de autoincremento al incluir la columna de identificación en una statement de inserción y asignar el valor específico que desea.

Aquí hay un punto para poner en práctica una llave inglesa: MySQL mismo (al menos 5.6 InnoDB) reutilizará una identificación de autoincremento en la siguiente circunstancia:

  • eliminar cualquier número de filas con el mayor ID de incremento automático
  • Detener e iniciar MySQL
  • insertar una nueva fila

La fila insertada tendrá una identificación calculada como max (id) +1, no continúa desde la identificación que se eliminó.

Como djna dijo en su respuesta, no es una buena práctica alterar las tablas de la base de datos de esa manera, tampoco es necesario si has elegido el esquema y los tipos de datos correctos. Por cierto, de acuerdo con su pregunta:

Tengo una tabla con un campo auto_increment y algunas veces las filas se eliminan, por lo que auto_increment deja huecos. Hay alguna manera de evitar esto?

  1. Si su tabla tiene demasiadas lagunas en su columna de incremento automático, probablemente como resultado de tantas consultas INSERT prueba
  2. Y si desea evitar valores de identificación abrumadores eliminando las lagunas
  3. Y también si la columna de id es solo un contador y no tiene relación con ninguna otra columna en su base de datos

, esto puede ser lo que usted (o cualquier otra persona que busque tal cosa) esté buscando:

SOLUCIÓN

  1. eliminar la columna de id original
  2. agréguelo de nuevo usando auto_increment en

Pero si solo quieres restablecer el auto_increment al primer valor disponible:

 ALTER TABLE `table_name` AUTO_INCREMENT=1 

no estoy seguro si esto ayudará, pero en el servidor sql puede resembrar los campos de identidad. Parece que hay una instrucción ALTER TABLE en mySql para lograr esto. Por ejemplo, para configurar el ID para continuar en 59446.

 ALTER TABLE table_name AUTO_INCREMENT = 59446; 

Estoy pensando que debería poder combinar una consulta para obtener el mayor valor del campo auto_increment, y luego usar la tabla alter para actualizar según sea necesario.