Estaba trabajando en una nueva funcionalidad para un cliente el otro día, probando cómo se comportaban ciertos registros con un módulo nuevo. Obviamente, cada cierto tiempo, yo necesitaba vaciar la tabla para empezar de cero. Usaba un simple TRUNCATE TABLE, pero me di cuenta de un detalle: el AUTO_INCREMENT seguía contando desde el último número usado. ¿El ID 1 ya lo había usado? El siguiente era el 10, o el 20, no el 1. Eso no era lo que quería.
Si te ha pasado lo mismo, o simplemente necesitas que tu contador de IDs empiece de nuevo, aquí te explico cómo lo resuelvo yo, sin andar con rodeos.
Resetear el contador: La solución directa
La forma más limpia y directa de hacer que un AUTO_INCREMENT en MySQL vuelva a empezar desde 1 (o desde el número que quieras) es con un ALTER TABLE. Pero ojo que acá hay un detalle importante que muchos pasan por alto, y que a mí, de hecho, me costó entender bien al principio con InnoDB.
Si tu tabla está vacía, o simplemente quieres que el próximo ID sea un número específico (siempre y cuando este sea mayor que el ID máximo actual), la sentencia es esta:
ALTER TABLE nombre_de_tu_tabla AUTO_INCREMENT = 1;Con eso, la próxima vez que insertes un registro, si la tabla está vacía, el ID será 1. Si pusiste AUTO_INCREMENT = 100, el próximo ID será 100.
Un detalle importante con InnoDB
Aquí es donde viene el truco, y la razón por la que a veces esta solución parece no funcionar. Si usas InnoDB (que es lo más común hoy en día) y la tabla no está vacía, no puedes setear el AUTO_INCREMENT a un valor menor o igual al ID máximo que ya existe en la tabla. Si intentas poner AUTO_INCREMENT = 1 y ya tienes un registro con ID 5, MySQL simplemente ignorará tu comando y el contador seguirá desde 6.
Para que funcione en InnoDB, el valor que le des a AUTO_INCREMENT debe ser mayor que el ID máximo actual. Si lo que quieres es realmente resetearlo a 1, lo más seguro es que necesites vaciar la tabla primero con TRUNCATE TABLE nombre_de_tu_tabla; y luego aplicar el ALTER TABLE.
¿Y si mis IDs ya están mal y quiero reordenarlos?
Hay un escenario distinto, donde quizá no solo quieres resetear el contador en una tabla vacía, sino que tienes IDs desordenados o con saltos, y quieres re-enumerarlos desde 1 en registros que ya existen. Esto es más raro, y yo personalmente lo evito, porque romper los IDs de una tabla con datos ya existentes es peligroso si hay relaciones con otras tablas. Pero si sabes lo que haces y es un caso puntual, puedes forzar una re-secuenciación así:
SET @num := 0;
UPDATE tu_tabla SET id = @num := (@num+1) ORDER BY id;
ALTER TABLE tu_tabla AUTO_INCREMENT = 1;Esto actualizará el campo id de cada fila existente para que sean 1, 2, 3, etc., y luego reseteará el contador para que el siguiente ID insertado continúe la secuencia. Es una solución más invasiva y no la usaría si solo quiero que el próximo ID sea 1 en una tabla nueva o vacía.
Mi recomendación final
En mi experiencia, la mayoría de las veces que necesitas resetear el AUTO_INCREMENT es porque vaciaste la tabla o estás haciendo pruebas. Para esos casos, el ALTER TABLE tablename AUTO_INCREMENT = 1; es tu mejor amigo, simple y al grano. Asegúrate de que tu tabla esté vacía si usas InnoDB para que el contador realmente vuelva a 1.
El truco del UPDATE con variables lo reservo para cuando realmente tengo un desorden de IDs y necesito re-secuenciarlos en registros que ya existen. Pero, sinceramente, es una situación que rara vez enfrento y que, de ser posible, trato de evitar.
Mi recomendación es clara: si tu tabla está vacía y quieres que el próximo ID sea 1, usa ALTER TABLE nombre_de_tu_tabla AUTO_INCREMENT = 1;. No le des más vueltas.