Uso de secuencias MySQL (AUTO_INCREMENT)

En MySQL, una secuencia es un objeto que genera una secuencia numérica autoincremental, es un conjunto de enteros1、2、3、...Dado que una tabla de datos solo puede tener un campo de clave primaria autoincremental.

Aunque MySQL no tiene un tipo de secuencia integrado, se puede usar el atributo AUTO_INCREMENT para simular el comportamiento de una secuencia, normalmenteAUTO_INCREMENTel atributo se utiliza para especificar el autoincremento de una columna en la tabla.

Un ejemplo de creación de una tabla usando AUTO_INCREMENT:

Ejemplo

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

En el ejemplo anterior, la columna id se define como INT AUTO_INCREMENT, lo que significa que cada vez que se inserta una fila de datos, el valor de la columna id aumenta automáticamente. La restricción de clave primaria garantiza la unicidad de la columna id.

Al insertar una fila de datos, puedes omitir el valor de la columna id; la base de datos asignará automáticamente un valor único y autoincremental.

INSERT INTO example_table (name) VALUES ('John');

También puedes usar la función LAST_INSERT_ID() para obtener el valor autoincremental de la fila recién insertada:

SELECT LAST_INSERT_ID();

Si necesitas obtener el valor autoincremental actual de la tabla, puedes usar la siguiente declaración:

SHOW TABLE STATUS LIKE 'example_table';

En el conjunto de resultados, el valor de la columna Auto_increment es el valor autoincremental actual de la tabla.

Ten en cuenta que la columna con el atributo AUTO_INCREMENT solo puede ser de tipo entero (generalmente INT o BIGINT). Además, si eliminas una fila de la tabla, su valor autoincremental no se reutilizará, sino que continuará incrementándose. Si deseas establecer manualmente el valor autoincremental, puedes usar la declaración SET, pero no es una práctica habitual, ya que podría causar conflictos de unicidad.


Usar AUTO_INCREMENT

La forma más sencilla de usar secuencias en MySQL es usar AUTO_INCREMENT para definir la secuencia.

Ejemplo

En el siguiente ejemplo se crea la tabla de datos insect; en la tabla insect, la columna id puede crecer automáticamente sin necesidad de especificar un valor.

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Obtener el valor de AUTO_INCREMENT

En el cliente de MySQL puedes usar la función LAST_INSERT_ID() de SQL para obtener el valor de la columna autoincremental de la última inserción en la tabla.

En los scripts de PHP o PERL también se proporcionan funciones correspondientes para obtener el valor de la columna autoincremental de la última inserción en la tabla.

Ejemplo en PERL

Usa el atributo mysql_insertid para obtener el valor de AUTO_INCREMENT. Ejemplo:

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

Ejemplo en PHP

PHP utiliza la función mysql_insert_id() para obtener el valor de la columna AUTO_INCREMENT en la declaración SQL de inserción ejecutada.

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Restablecer la secuencia

Si has eliminado varios registros de la tabla de datos y deseas reordenar la columna AUTO_INCREMENT de los datos restantes, puedes lograrlo eliminando la columna autoincremental y volviéndola a agregar. Sin embargo, esta operación debe realizarse con mucho cuidado, ya que si se agregan nuevos registros mientras se elimina, podrían producirse confusiones de datos. La operación es la siguiente:

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

Establecer el valor inicial de la secuencia

Generalmente, el valor inicial de la secuencia es 1, pero si necesitas especificar un valor inicial de 100, podemos lograrlo con la siguiente declaración:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

O también puedes hacerlo después de crear la tabla, mediante la siguiente declaración:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;
Otras extensiones