¿Cómo completar los “agujeros” en los campos auto-incremenet?

He leído algunas publicaciones al respecto, pero ninguna cubre este tema.

Supongo que no es posible, pero lo preguntaré de todos modos.

Tengo una mesa con más de 50,000 registros. Es una tabla antigua donde se han llevado a cabo varias operaciones de inserción / eliminación.

Dicho esto, hay varios “agujeros” algunos de alrededor de 300 registros. Es decir: …, 1340, 1341, 1660, 1661, 1662, …

La pregunta es. ¿Hay alguna manera simple / fácil de hacer que las nuevas inserciones llenen estos ‘agujeros’?

thx Paulo Bueno

¿Cuál es el motivo por el que necesita esta funcionalidad? Su DB debe estar bien con las brechas, y si se aproxima al tamaño máximo de su clave, simplemente haga que no esté firmado o cambie el tipo de campo.

Estoy de acuerdo con @Aaron Digulla y @Shane N. Las lagunas no tienen sentido. Si realmente significan algo, es un diseño de base de datos defectuoso. Período.

Dicho esto, si usted NECESITA por completo llenar estos huecos Y ejecuta al menos MySQL 3.23, puede utilizar una TABLA TEMPORAL para crear un nuevo conjunto de ID. La idea aquí es que va a seleccionar todos sus ID actuales, en orden, en una tabla temporal como tal:

 CREATE TEMPORARY TABLE NewIDs ( NewID INT UNSIGNED AUTO INCREMENT, OldID INT UNSIGNED ) INSERT INTO NewIDs (OldId) SELECT Id FROM OldTable ORDER BY Id ASC 

Esto le dará una tabla que correlaciona su Id anterior con un ID nuevo que será de naturaleza secuencial, debido a la propiedad AUTO INCREMENT de la columna NewId.

Una vez hecho esto, debe actualizar cualquier otra referencia a Id en “OldTable” y cualquier clave externa que utilice. Para hacer esto, es probable que necesite DEJAR caer cualquier restricción de clave externa que tenga, actualice cualquier referencia en las tablas de OldId a NewId, y luego restablezca sus restricciones de clave externa.

Sin embargo, yo argumentaría que usted no debe hacer NINGUNO de esto, y solo entender que su campo Id existe con el único propósito de hacer referencia a un registro, y NO debe tener ninguna relevancia específica.

ACTUALIZACIÓN: Agregar un ejemplo de actualización de los Id.

Por ejemplo:

Supongamos que tiene los siguientes 2 esquemas de tabla:

 CREATE TABLE Parent ( ParentId INT UNSIGNED AUTO INCREMENT, Value INT UNSIGNED, PRIMARY KEY (ParentId) ) CREATE TABLE Child ( ChildId INT UNSIGNED AUTO INCREMENT, ParentId INT UNSIGNED, PRIMARY KEY(ChildId), FOREIGN KEY(ParentId) REFERENCES Parent(ParentId) ) 

Ahora, las brechas están apareciendo en su tabla Parent.

Para actualizar sus valores en Principal y secundario, primero crea una tabla temporal con las asignaciones:

 CREATE TEMPORARY TABLE NewIDs ( Id INT UNSIGNED AUTO INCREMENT, ParentID INT UNSIGNED ) INSERT INTO NewIDs (ParentId) SELECT ParentId FROM Parent ORDER BY ParentId ASC 

A continuación, debemos decirle a MySQL que ignore la restricción de clave externa para que podamos ACTUALIZAR correctamente nuestros valores. Utilizaremos esta syntax:

 SET foreign_key_checks = 0; 

Esto hace que MySQL ignore las comprobaciones de claves externas al actualizar los valores, pero aún hará que se aplique el tipo de valor correcto (consulte la referencia de MySQL para obtener más información).

A continuación, debemos actualizar nuestras tablas Parent y Child con los nuevos valores. Utilizaremos la siguiente instrucción UPDATE para esto:

 UPDATE Parent, Child, NewIds SET Parent.ParentId = NewIds.Id, Child.ParentId = NewIds.Id WHERE Parent.ParentId = NewIds.ParentId AND Child.ParentId = NewIds.ParentId 

Ahora hemos actualizado todos nuestros valores ParentId correctamente a los nuevos Ids ordenados de nuestra tabla temporal. Una vez que esto se complete, podemos restablecer nuestras verificaciones de claves externas para mantener la integridad referencial:

 SET foreign_key_checks = 1; 

Finalmente, dejaremos caer nuestra tabla temporal para limpiar los recursos:

 DROP TABLE NewIds 

Y eso es eso.

Por lo general, no necesita preocuparse por las lagunas. Si está llegando al final del tipo de datos para la ID, debería ser relativamente fácil ALTERAR la tabla para actualizarla al siguiente tipo de int más grande.

Si absolutamente debe comenzar a llenar las lagunas, aquí hay una consulta para devolver la identificación más baja disponible (con suerte, no demasiado lentamente):

 SELECT MIN(table0.id)+1 AS newid FROM table AS table0 LEFT JOIN table AS table1 ON table1.id=table0.id+1 WHERE table1.id IS NULL 

(recuerde usar una transacción y / o atrapar inserciones de claves duplicadas si necesita inserciones simultáneas para trabajar).

 INSERT INTO prueba(id) VALUES ( (SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target)) 

IFNULL para omitir el nulo en el recuento de cero filas

agregar destino para omitir error mysql “cláusula de error FROM)

Hay una manera simple pero no funciona bien: simplemente intente insertar con una identificación y cuando eso falle, intente con la siguiente.

Alternativamente, seleccione una ID y cuando no obtenga un resultado, úselo.

Si está buscando una manera de decirle al DB que llene automáticamente las lagunas, entonces eso no es posible. Además, nunca debería ser necesario. Si cree que lo necesita, está abusando de una clave técnica interna para algo, pero con el único propósito que tiene: permitirle unir mesas.

[EDITAR] Si esta no es una clave principal, puede usar esta statement de actualización:

 update ( select * from table order by reg_id -- this makes sure that the order stays the same ) set reg_id = x.nextval 

donde x es una nueva secuencia que debes crear. Esto volverá a numerar todos los elementos existentes que conservan el orden. Esto fallará si tiene restricciones de clave externa. Y dañará su base de datos si hace referencia a estos ID en cualquier lugar sin restricciones de clave externa.

Tenga en cuenta que durante la próxima inserción, la base de datos creará un espacio enorme a menos que restablezca la columna de identidad.

Como otros han dicho, no importa, y si lo hace, algo está mal en el diseño de su base de datos. Pero, personalmente, ¡me gusta que estén en orden de todos modos!

Aquí hay algunos SQL que recrearán sus ID en el mismo orden, pero sin las brechas.

Primero se realiza en un campo temp_id (que deberá crear), para que pueda ver que todo está bien antes de sobrescribir sus ID anteriores. Reemplace Tbl e id según corresponda.

 SELECT @i:=0; UPDATE Tbl JOIN ( SELECT id FROM Tbl ORDER BY id ) t2 ON Tbl.id = t2.id SET temp_id = @i:=@i+1; 

Ahora tendrá un campo temp_id con todos sus nuevos ID shinys. Puedes hacerlos vivir simplemente:

 UPDATE Tbl SET id = temp_id; 

Y luego soltar tu columna temp_id

Debo admitir que no estoy muy seguro de por qué funciona, ya que esperaba que el motor se quejara de las identificaciones duplicadas, pero no lo hizo cuando lo ejecuté.

Es posible que desee limpiar huecos en una columna de prioridad. El siguiente ejemplo dará un campo de incremento automático para la prioridad. La combinación extra izquierda en la misma tabla se asegurará de que se agregue en el mismo orden que (en este caso) la prioridad

 SET @a:=0; REPLACE INTO footable (id,priority) ( SELECT tbl2.id, @a FROM footable as tbl LEFT JOIN footable as tbl2 ON tbl2.id = tbl.id WHERE (select @a:=@a+1) ORDER BY tbl.priority )