una - Aumento automático después de eliminar en MySQL

mysql reset auto_increment after delete

Question

Tengo una tabla MySQL con un campo de clave principal que tiene activado AUTO_INCREMENT. Después de leer otras publicaciones aquí, noté personas con el mismo problema y con respuestas variadas. Algunos recomiendan no usar esta característica, otros dicen que no se puede ''arreglar''.

Yo tengo:

table: course
fields: courseID, courseName

Ejemplo: número de registros en la tabla: 18. Si borro los registros 16, 17 y 18, esperaría que el siguiente registro ingresado tenga el ID de curso de 16, sin embargo, será 19 porque el último ID de curso ingresado fue 18.

Mi conocimiento de SQL no es sorprendente, pero ¿hay alguna forma de actualizar o actualizar este conteo con una consulta (o una configuración en la interfaz phpMyAdmin)?

Esta tabla se relacionará con otros en una base de datos.

Con todos los consejos, he decidido ignorar este ''problema''. Simplemente borraré y agregaré los registros mientras dejo que el incremento automático haga su trabajo. Supongo que realmente no importa cuál es el número, ya que solo se usa como identificador único y no tiene un significado comercial (como se menciona anteriormente).

Para aquellos a quienes pueda haber confundido con mi publicación original: no deseo utilizar este campo para saber cuántos registros tengo. Solo quería que la base de datos se viera ordenada y tuviera un poco más de consistencia.

Answer 1

De hecho, hay una manera de arreglar eso. Primero borre la columna de la clave principal autoincrementada y luego vuelva a agregarla, de esta manera:

ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name ADD column_name int not null auto_increment primary key first;

Answer 2

Definitivamente no es recomendable. Si tiene una base de datos grande con varias tablas, probablemente haya guardado un identificador de usuario como id en la tabla 2. si reorganiza la tabla 1, entonces probablemente la ID de usuario prevista no termine siendo la id prevista de la tabla 2.

Answer 3

Las claves de autoincrementación primaria en la base de datos se utilizan para identificar de forma única una fila determinada y no se le debe dar ningún significado comercial . Deje la clave principal como está y agregue otra columna llamada por ejemplo courseOrder . Luego, cuando elimine un registro de la base de datos, puede enviar una instrucción UPDATE adicional para disminuir la columna courseOrder de todas las filas que tengan courseOrder mayor que la que está eliminando actualmente.

Como nota al margen, nunca se debe modificar el valor de una clave primaria en una base de datos relacional porque podría haber otras tablas que la referencian como una clave externa y modificarla podría violar las restricciones referenciales.

Answer 4

Lo que estás tratando de hacer es muy peligroso. Piensa en esto cuidadosamente. Hay una muy buena razón para el comportamiento predeterminado de incremento automático.

Considera esto:

Se borra un registro en una tabla que tiene una relación con otra tabla. El registro correspondiente en la segunda tabla no se puede eliminar por razones de auditoría. Este registro queda huérfano de la primera tabla. Si se inserta un nuevo registro en la primera tabla y se utiliza una clave primaria secuencial, este registro ahora está vinculado al huérfano. Obviamente, esto es malo. Al usar un PK incrementado automáticamente, siempre se garantiza una identificación que nunca se haya utilizado antes. Esto significa que los huérfanos permanecen huérfanos, lo cual es correcto.

Answer 5

Lo que intenta hacer suena peligroso, ya que ese no es el uso previsto de AUTO_INCREMENT .

Si realmente desea encontrar el valor más bajo de clave no utilizada, no use AUTO_INCREMENT y administre sus claves manualmente. Sin embargo, esta NO es una práctica recomendada.

Dé un paso atrás y pregunte " ¿por qué necesita reciclar los valores clave? " ¿El INT sin firmar (o BIGINT ) no proporciona un espacio clave lo suficientemente grande?

¿Realmente va a tener más de 18,446,744,073,709,551,615 registros únicos a lo largo de la vida útil de su aplicación?

Answer 6

No debe confiar en el ID AUTO_INCREMENT para decirle cuántos registros tiene en la tabla. Deberías usar SELECT COUNT(*) FROM course . Los ID están ahí para identificar de manera única el curso y se pueden usar como referencias en otras tablas, por lo que no debe repetir los ids y no debe intentar restablecer el campo de incremento automático.

Answer 7

Puede pensar en hacer un desencadenador después de eliminar para que pueda actualizar el valor de autoincrement y el valor de ID de todas las filas que no se parece a lo que quería ver.

Entonces puede trabajar con la misma tabla y el incremento automático se fijará automáticamente cada vez que elimine una fila, el desencadenador lo arreglará.

Answer 8

Puede usar su software / script de cliente mysql para especificar desde dónde debe comenzar la clave principal después de eliminar los registros requeridos.

Answer 9

Puedo pensar en muchos escenarios donde podría necesitar hacer esto, particularmente durante un proceso de migración o desarrollo. Por ejemplo, ahora tenía que crear una nueva tabla uniendo dos tablas existentes (como parte de un complejo proceso de configuración), y luego necesitaba agregar una clave principal después del evento. Puede soltar la columna de clave principal existente y luego hacer esto.

ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);

Para un sistema en vivo, no es una buena idea, y especialmente si hay otras tablas con claves externas apuntando hacia él.

Answer 10

Tengo un método muy simple pero complicado.

Al eliminar una fila, puede conservar los ID en otra tabla temporal. Después de eso, cuando inserte datos nuevos en la tabla principal, puede buscar y elegir ID de la tabla temporal. Entonces usa una verificación aquí. Si la tabla temporal no tiene ID, calcule la ID máxima en la tabla principal y establezca la nueva ID como: new_ID = old_max_ID+1 .

NB: No puede usar la función de incremento automático aquí.

Answer 11

Tratar :

SET @num: = 0;

ACTUALIZA your_table SET id = @num: = (@ num + 1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

Eso restablecerá el valor autoincrementado y luego contará cada fila mientras se crea un nuevo valor para ella.

ejemplo: antes

• 1: primer valor aquí
• 2: segundo valor aquí
• X: valor eliminado
• 4: El resto de la mesa
• 5: El resto del resto ...

entonces la tabla mostrará la matriz: 1,2,4,5

Ejemplo: DESPUÉS (si usa este comando, obtendrá)

• 1: primer valor aquí
• 2: segundo valor aquí
• 3: El resto de la mesa
• 4: el resto del resto

No hay rastro del valor eliminado, y el resto del incremento continúa con este nuevo recuento.

PERO

1. Si en alguna parte de tu código algo usas el valor autoincrementado ... tal vez esta atribución cause un problema.
2. Si no usa este valor en su código, todo debería estar bien.

Answer 12

Vine aquí buscando una respuesta a la pregunta del título "MySQL - Auto Increment after delete" pero solo pude encontrar una respuesta para eso en las preguntas

• ¿Cómo eliminar cierta fila de la tabla mysql?
• Cómo reiniciar AUTO_INCREMENT en MySQL?

Al usar algo como:

DELETE FROM table;
ALTER TABLE table AUTO_INCREMENT = 1;

Tenga en cuenta que la respuesta de Darin Dimitrov explica muy bien AUTO_INCREMENT y su uso. Eche un vistazo allí antes de hacer algo que pueda lamentar.

PD: La pregunta en sí es más "Why you need to recycle key values?" y la respuesta de Dolph lo cubre.

Answer 13

aquí hay una función que arregla tu problema

 public static void fixID(Connection conn, String table) {
try {
Statement myStmt = conn.createStatement();
ResultSet myRs;
int i = 1, id = 1, n = 0;
boolean b;
String sql;
myRs = myStmt.executeQuery("select max(id) from " + table);
if (myRs.next()) {
n = myRs.getInt(1);
}
while (i <= n) {
b = false;
myRs = null;
while (!b) {
myRs = myStmt.executeQuery("select id from " + table + " where id=" + id);
if (!myRs.next()) {
id++;
} else {
b = true;
}
}
sql = "UPDATE " + table + " set id =" + i + " WHERE id=" + id;
myStmt.execute(sql);
i++;
id++;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

Answer 14

puede seleccionar los id como así:

set @rank = 0;
select id, @rank:=@rank+1 from tbl order by id

el resultado es una lista de identificadores y sus posiciones en la secuencia.

también puedes restablecer los id como así:

set @rank = 0;
update tbl a join (select id, @rank:=@rank+1 as rank from tbl order by id) b
on a.id = b.id set a.id = b.rank;

también puedes simplemente imprimir la primera identificación no utilizada así:

select min(id) as next_id from ((select a.id from (select 1 as id) a
left join tbl b on a.id = b.id where b.id is null) union
(select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id
where b.id is null)) c;

después de cada inserción, puede restablecer el auto_increment:

alter table tbl auto_increment = 16

o establezca explícitamente el valor de identificación al hacer la inserción:

insert into tbl values (16, ''something'');

por lo general, esto no es necesario, usted count(*) y la capacidad de crear un número de clasificación en sus conjuntos de resultados. una clasificación típica podría ser:

set @rank = 0;
select a.name, a.amount, b.rank from cust a,
(select amount, @rank:=@rank+1 as rank from cust order by amount desc) b
where a.amount = b.amount

clientes clasificados por cantidad gastada.

Answer 15

ALTER TABLE foo AUTO_INCREMENT=1

Si eliminó las entradas más recientes, debería configurarlo para usar el siguiente más bajo disponible. Como en, siempre y cuando no haya 19 ya, eliminar 16-18 restablecerá el autoincrement para usar 16.

EDITAR: Me perdí un poco sobre phpmyadmin. Puedes establecerlo allí, también. Vaya a la pantalla de la tabla y haga clic en la pestaña de operaciones. Hay un campo AUTOINCREMENT allí que puede configurar lo que necesite manualmente.

Answer 16

if($id == 1){ // deleting first row
mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>1");
}
else if($id>1 && $id<$num){ // deleting middle row
mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>$id");
}
else if($id == $num){ // deleting last row
mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");
}
else{
echo "ERROR";
}
mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");