MySQL AUTO_INCREMENT avec exemples

Qu’est-ce que l’incrémentation automatique ?

Auto Increment est une fonction qui fonctionne sur les types de données numériques. Il génère automatiquement des valeurs numériques séquentielles chaque fois qu'un enregistrement est inséré dans une table pour un champ défini comme incrémentation automatique.

Cet attribut fonctionne avec tous les types entiers, de TINYINT à BIGINT. La colonne doit également être indexée, ce qui est automatique lorsqu'elle est déclarée comme clé primaire.

Quand utiliser l’incrémentation automatique ?

Dans la leçon sur normalisation des bases de donnéesNous avons examiné comment les données peuvent être stockées avec une redondance minimale, en les stockant dans de nombreuses petites tables, liées entre elles par des clés primaires et étrangères.

Une clé primaire doit être unique, car elle identifie de manière unique une ligne dans une base de données. Mais comment garantir que la clé primaire est toujours unique ?

Une solution possible serait d'utiliser une formule pour générer la clé primaire, qui vérifie son existence dans la table avant d'y ajouter des données. Cette solution peut fonctionner, mais elle est complexe et non infaillible. Deux sessions insérant des données simultanément peuvent toujours lire la même valeur maximale et provoquer un conflit.

Afin d'éviter une telle complexité et de garantir que la clé primaire soit toujours unique, nous pouvons utiliser la MySQL La fonctionnalité d'auto-incrémentation permet de générer des clés primaires. Elle est utilisée avec le type de données INT, qui prend en charge les valeurs signées et non signées. Les données non signées ne peuvent contenir que des nombres positifs. Il est recommandé de définir une contrainte de non-signature sur la clé primaire à auto-incrémentation.

Syntaxe d'incrémentation automatique

Une fois le raisonnement établi, examinez le script utilisé pour créer le tableau des catégories de films.

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`)
);

Notez la mention « AUTO_INCREMENT » dans le champ category_id. Cela permet de générer automatiquement l'identifiant de la catégorie à chaque insertion d'une nouvelle ligne dans la table. Cet identifiant n'est pas fourni lors de l'insertion des données. MySQL le génère.

À noter: Le mot-clé UNSIGNED double la plage positive de la colonne, et la largeur d'affichage, autrefois écrite sous la forme int(11), est obsolète. MySQL À partir du 8.0.17. Simple int est la forme actuelle.

Par défaut, la valeur initiale de AUTO_INCREMENT est 1, et elle sera incrémentée de 1 pour chaque nouvel enregistrement.

Examinons le contenu actuel du tableau des catégories.

SELECT * FROM `categories`;

Exécuter le script ci-dessus dans MySQL Workbench appliqué à myflixdb nous donne les résultats suivants.

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

Il existe huit lignes, donc le prochain identifiant généré devrait être 9. Insérons maintenant une nouvelle catégorie dans la table des catégories, en ne fournissant que le nom.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Exécution du script ci-dessus sur myflixdb dans MySQL établi nous donne les résultats suivants présentés ci-dessous.

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

Veuillez noter que nous n'avons pas fourni l'identifiant de catégorie. MySQL Il a été généré automatiquement, car l'identifiant de catégorie est défini comme auto-incrémenté.

Si vous souhaitez obtenir le dernier identifiant d'insertion généré par MySQL, vous pouvez utiliser la fonction LAST_INSERT_ID pour ce faire. Le script ci-dessous obtient le dernier identifiant généré.

SELECT LAST_INSERT_ID();

L'exécution du script ci-dessus renvoie le dernier numéro d'auto-incrémentation généré par la requête INSERT. Les résultats sont affichés ci-dessous.

Astuce: La fonction LAST_INSERT_ID() est limitée à votre propre connexion ; par conséquent, une valeur générée par l’insertion d’un autre utilisateur ne peut jamais vous être renvoyée par erreur.

Comment définir ou réinitialiser la valeur de départ de AUTO_INCREMENT

La numérotation par défaut commence à 1, mais elle ne correspond pas toujours aux besoins d'un projet. Il peut être nécessaire de reprendre la numérotation des factures d'un système existant, et il est souvent indispensable de réinitialiser une table de test. MySQL Le compteur est directement accessible, ce qui permet de traiter les deux cas avec une seule clause. Suivez ces étapes pour contrôler le nombre initial.

1. Définissez la valeur lors de la création. Ajoutez la clause AUTO_INCREMENT à l'instruction CREATE TABLE. La première ligne insérée recevra alors ce numéro au lieu de 1.
2. Modifier la valeur d'un tableau existant. Utilisez le ALTER TABLE avec la même clause. MySQL n'accepte le nouveau numéro que s'il est supérieur à l'identifiant le plus long actuellement stocké.
3. Réinitialisez une table que vous avez vidée. La commande TRUNCATE TABLE supprime toutes les lignes et remet le compteur à 1 en une seule opération, ce que la commande DELETE seule ne fait pas.
4. Confirmez la modification. Insérez une ligne et relisez l'identifiant avec LAST_INSERT_ID() avant de vous fier à la nouvelle séquence.

-- 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`;

La taille du pas peut également être modifiée via la variable système auto_increment_increment, mais cette modification s'applique à l'ensemble du serveur et non à une seule table. Elle est principalement utilisée en réplication, lorsque deux serveurs ne doivent pas générer le même identifiant.

Pourquoi des lacunes apparaissent-elles dans une séquence AUTO_INCREMENT ?

Tôt ou tard, un tableau affiche des identifiants tels que 1, 2, 5, 6. Rien n'est cassé. Le compteur est conçu pour garantir l'unicité, et non la continuité d'une suite de nombres ; il ne renvoie jamais deux fois la même valeur.

Des lacunes apparaissent pour les raisons suivantes.

• Lignes supprimées : Lorsqu'une ligne est supprimée d'une table, son identifiant auto-incrémenté n'est pas réutilisé. MySQL continue de générer de nouveaux numéros de manière séquentielle.
• Transactions annulées : Le numéro est réservé dès l'exécution de l'insertion. Si la transaction est annulée, la ligne disparaît, mais le numéro a déjà été dépensé.
• Insertions échouées : Une instruction rejetée par une contrainte UNIQUE peut tout de même consommer un identifiant avant d'échouer.
• Inserts en vrac : InnoDB peut réserver un bloc de nombres pour une insertion multi-lignes et supprimer ceux qu'il n'utilise pas.

Tenter de combler ces lacunes est une erreur. Renuméroter les lignes invalide toutes les clés étrangères qui y font référence, et la valeur elle-même n'a aucune signification métier. Si un rapport nécessite une liste continue, générez le numéro de ligne dans la requête au lieu de réécrire les données stockées.

FAQ