MySQL AUTO_INCREMENT con ejemplos
Marcus Allen
⚡ Resumen inteligente
MySQL AUTO_INCREMENT genera automáticamente números secuenciales para una columna numérica cada vez que se inserta una fila. Este atributo elimina la necesidad de calcular identificadores únicos manualmente, lo que lo convierte en la forma estándar de completar una clave primaria.
¿Qué es el incremento automático?
El incremento automático es una función que opera con tipos de datos numéricos. Genera automáticamente valores numéricos secuenciales cada vez que se inserta un registro en una tabla para un campo definido como incremento automático.
Este atributo funciona con cualquier tipo de entero, desde TINYINT hasta BIGINT. La columna también debe estar indexada, lo cual ocurre automáticamente al declararla como clave primaria.
¿Cuándo se utiliza el incremento automático?
En la lección sobre normalización de base de datosAnalizamos cómo se pueden almacenar los datos con una redundancia mínima, almacenándolos en muchas tablas pequeñas, relacionadas entre sí mediante claves primarias y foráneas.
Una clave primaria debe ser única, ya que identifica de forma unívoca una fila en una base de datos. Pero, ¿cómo podemos garantizar que la clave primaria sea siempre única?
Una posible solución sería usar una fórmula para generar la clave primaria, que verifique la existencia de la clave en la tabla antes de agregar datos. Esto podría funcionar, pero el método es complejo y no infalible. Dos sesiones que insertan datos simultáneamente podrían leer el mismo valor máximo y generar un conflicto.
Para evitar tal complejidad y garantizar que la clave primaria sea siempre única, podemos utilizar la MySQL Función de autoincremento para generar claves primarias. El autoincremento se utiliza con el tipo de dato INT. Este tipo de dato admite valores con y sin signo. Los datos sin signo solo pueden contener números positivos. Como buena práctica, se recomienda definir la restricción de "sin signo" en la clave primaria de autoincremento.
Sintaxis de incremento automático
Una vez aclarado el razonamiento, observe el guion utilizado para crear la tabla de categorías de películas.
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
Observe el “AUTO_INCREMENT” en el campo category_id. Esto hace que el ID de categoría se genere automáticamente cada vez que se inserta una nueva fila en la tabla. No se proporciona al insertar datos en la tabla. MySQL lo genera.
Nota: La palabra clave UNSIGNED duplica el rango positivo de la columna y el ancho de visualización que antes se escribía como int(11) está obsoleto. MySQL A partir del 8 de octubre de 2017. Simple int es la forma actual.
Por defecto, el valor inicial de AUTO_INCREMENT es 1, y se incrementará en 1 por cada nuevo registro.
Examinemos el contenido actual de la tabla de categorías.
SELECT * FROM `categories`;
Ejecutando el script anterior en MySQL Workbench contra myflixdb nos da los siguientes resultados.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
Existen ocho filas, por lo que el siguiente ID generado debería ser 9. Ahora insertemos una nueva categoría en la tabla de categorías, proporcionando solo el nombre.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Ejecutando el script anterior contra myflixdb en MySQL banco de trabajo nos da los siguientes resultados que se muestran a continuación.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
Tenga en cuenta que no proporcionamos el ID de la categoría. MySQL Se generó automáticamente porque el ID de categoría está definido como autoincrementable.
Si desea obtener la última identificación de inserción generada por MySQL, puedes usar la función LAST_INSERT_ID para hacerlo. El script que se muestra a continuación obtiene la última identificación que se generó.
SELECT LAST_INSERT_ID();
Al ejecutar el script anterior, se obtiene el último número de autoincremento generado por la consulta INSERT. Los resultados se muestran a continuación.
Tip: LAST_INSERT_ID() está limitado a tu propia conexión, por lo que un valor generado por la inserción de otro usuario nunca se te devolverá por error.
Cómo establecer o restablecer el valor inicial de AUTO_INCREMENT.
La secuencia predeterminada comienza en 1, pero no siempre es lo que necesita un proyecto. Es posible que los números de factura deban continuar desde un sistema heredado, y a menudo es necesario reiniciar una tabla de prueba. MySQL Esto expone el contador directamente, por lo que ambos casos se manejan con una sola cláusula. Siga estos pasos para controlar el número inicial.
- Establezca el valor en el momento de la creación. Agregue la cláusula AUTO_INCREMENT a la instrucción CREATE TABLE. La primera fila insertada recibirá ese número en lugar de 1.
- Modificar el valor de una tabla existente. Usar ALTERAR MESA con la misma cláusula. MySQL Acepta el nuevo número solo si es mayor que el identificador más grande almacenado actualmente.
- Restablece una tabla que hayas vaciado. La función TRUNCATE TABLE elimina todas las filas y restablece el contador a 1 en una sola operación, algo que la función DELETE por sí sola no hace.
- Confirma el cambio. Inserta una fila y lee el identificador con LAST_INSERT_ID() antes de confiar en la nueva secuencia.
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
El tamaño del paso también se puede modificar con la variable de sistema auto_increment_increment, pero se aplica a todo el servidor en lugar de a una sola tabla. Se utiliza principalmente en la replicación, donde dos servidores no deben generar el mismo identificador.
¿Por qué aparecen huecos en una secuencia AUTO_INCREMENT?
Tarde o temprano, una tabla mostrará identificadores como 1, 2, 5, 6. No hay ningún problema. El contador está diseñado para garantizar la unicidad, no para garantizar una secuencia ininterrumpida de números, y nunca emite el mismo valor dos veces.
Las lagunas aparecen por las siguientes razones.
- Filas eliminadas: Cuando se elimina una fila de una tabla, su ID autoincrementado no se reutiliza. MySQL continúa generando nuevos números secuencialmente.
- Transacciones revertidas: El número se reclama en el momento en que se ejecuta la inserción. Si la transacción se revierte, la fila desaparece, pero el número ya se ha utilizado.
- Inserciones fallidas: Una instrucción rechazada por una restricción UNIQUE aún puede consumir un identificador antes de que falle.
- Insertos a granel: InnoDB puede reservar un bloque de números para una inserción de varias filas y descartar los que no utiliza.
Intentar cerrar estas brechas es un error. Renumerar las filas rompe todas las claves foráneas que las referencian, y el valor en sí no tiene relevancia para el negocio. Si un informe necesita una lista continua, genere el número de fila en la consulta en lugar de sobrescribir los datos almacenados.
