Utilisation des séquences MySQL (AUTO_INCREMENT)

Dans MySQL, une séquence est un objet qui génère automatiquement une séquence numérique, un ensemble d'entiers.1、2、3、...Car une table de données ne peut avoir qu'un seul champ de clé primaire auto-incrémenté.

Bien que MySQL lui-même n'ait pas de type séquence intégré, vous pouvez utiliser l'attribut AUTO_INCREMENT pour simuler le comportement d'une séquence. GénéralementAUTO_INCREMENTL'attribut est utilisé pour spécifier la propriété d'auto-incrémentation d'une colonne dans une table.

Un exemple de création d'une table à l'aide de AUTO_INCREMENT :

Exemple

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

Dans l'exemple ci-dessus, la colonne id est définie comme INT AUTO_INCREMENT, ce qui signifie que chaque fois qu'une ligne de données est insérée, la valeur de la colonne id augmente automatiquement. La contrainte de clé primaire garantit l'unicité de la colonne id.

Lorsque vous insérez une ligne de données, vous pouvez omettre la valeur de la colonne id, et la base de données lui attribuera automatiquement une valeur unique auto-incrémentée :

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

Vous pouvez également utiliser la fonction LAST_INSERT_ID() pour obtenir la valeur d'auto-incrémentation de la ligne qui vient d'être insérée :

SELECT LAST_INSERT_ID();

Si vous avez besoin d'obtenir la valeur d'auto-incrément actuelle d'une table, vous pouvez utiliser l'instruction suivante :

SHOW TABLE STATUS LIKE 'example_table';

Dans le jeu de résultats, la valeur de la colonne Auto_increment est la valeur d'auto-incrément actuelle de la table.

Veuillez noter qu'une colonne utilisant l'attribut AUTO_INCREMENT doit être d'un type entier (généralement INT ou BIGINT). De plus, si vous supprimez une ligne de la table, sa valeur d'auto-incrément ne sera pas réutilisée, mais continuera d'augmenter. Si vous souhaitez définir manuellement la valeur d'auto-incrément, vous pouvez utiliser l'instruction SET, mais ce n'est pas une pratique courante car cela peut entraîner des conflits d'unicité.

Utilisation d'AUTO_INCREMENT

La façon la plus simple d'utiliser les séquences dans MySQL est d'utiliser MySQL AUTO_INCREMENT pour définir une séquence.

Exemple

Dans l'exemple suivant, une table de données insect est créée. La colonne id de la table insect peut croître automatiquement sans spécifier de valeur.

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)

Obtention de la valeur AUTO_INCREMENT

Dans le client MySQL, vous pouvez utiliser la fonction LAST_INSERT_ID() en SQL pour obtenir la valeur de la colonne d'auto-incrément de la dernière ligne insérée.

Des fonctions correspondantes sont également fournies dans des scripts PHP ou PERL pour obtenir la valeur de la colonne d'auto-incrément de la dernière ligne insérée.

Exemple PERL

Utilisez l'attribut mysql_insertid pour obtenir la valeur AUTO_INCREMENT. L'exemple est le suivant :

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

Exemple PHP

PHP utilise la fonction mysql_insert_id() pour obtenir la valeur de la colonne AUTO_INCREMENT dans l'instruction SQL INSERT exécutée. 

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

Réinitialisation de la séquence

Si vous avez supprimé plusieurs enregistrements d'une table de données et que vous souhaitez renuméroter la colonne AUTO_INCREMENT des données restantes, vous pouvez le faire en supprimant la colonne auto-incrémentée puis en la rajoutant. Toutefois, cette opération doit être effectuée avec une grande prudence. Si de nouveaux enregistrements sont ajoutés pendant la suppression, une confusion des données peut se produire. L'opération est la suivante :

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

Définition de la valeur de départ de la séquence

Normalement, la valeur de départ de la séquence est 1, mais si vous avez besoin de spécifier une valeur de départ de 100, vous pouvez y parvenir avec l'instruction suivante :

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;

Alternativement, après la création de la table, vous pouvez également y parvenir avec l'instruction suivante :

mysql> ALTER TABLE t AUTO_INCREMENT = 100;
Autres extensions