Configuration de l'Auto-incrémentation

Définition lors de la création de la table

Il est possible d'attribuer une propriété d'auto-incrémentation à une clé primaire dès la création de la structure, tout en définissant une valeur de départ personnalisée.

CREATE TABLE `customers` (
  `customer_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `full_name` varchar(50) DEFAULT NULL COMMENT 'Nom complet du client',
  `years_old` int DEFAULT NULL COMMENT 'Âge',
  UNIQUE KEY `idx_unique_name` (`full_name`)
) ENGINE=InnoDB AUTO_INCREMENT = 500;

Dans cette configuration :

• AUTO_INCREMENT active la génération automatique de l'identifiant.
• AUTO_INCREMENT = 500 force la première valeur générée à être 500 (la valeur par défaut étant 1, avec un pas de 1).

Modification d'une table existante

Si la structure est déjà en place, l'instruction ALTER TABLE permet d'ajouter cette contrainte. Voici comment transformer un champ standard en clé primaire auto-incrémentée :

ALTER TABLE customers MODIFY customer_id INT AUTO_INCREMENT PRIMARY KEY;

Suppression de la propriété

Pour retirer la génération automatique, il suffit de redéfinir le type de la colonne en omettant le mot-clé dédié :

ALTER TABLE customers MODIFY customer_id INT PRIMARY KEY;

Fonctionnement interne de AUTO_INCREMENT

Paramètres de pas et de décalage

Le mécanisme garantit l'unicité des clés primaires via deux variables système principales : le décalage initial (auto_increment_offset) et l'incrément (auto_increment_increment).

mysql> SHOW VARIABLES LIKE '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

Stratégies de persistance selon le moteur

Le traitement de la valeur courante de l'auto-incrémentation varie radicalement selon l'architecture du moteur de stockage :

• MyISAM : La valeur est écrite directement dans le fichier de données.
• InnoDB (MySQL 5.7 et antérieurs) : La valeur résidait uniquement en mémoire vive. Lors d'un redémarrage du service, MySQL recalculait la valeur en exécutant SELECT MAX(id) + 1. Par conséquent, si la dernière ligne était supprimée avant un crash, l'identifiant supprimé pouvait être réattribué après le redémarrage.
• InnoDB (MySQL 8.0+) : Introduction de la persistance. Les modifications du compteur sont désormais journalisées dans le redo log. Un redémarrage restaurera exactement la valeur du compteur telle qu'elle était avant l'arrêt, éliminant les anomalies de réattribution.

Règles d'attribution

• Si l'insertion fournit NULL, 0 ou omet la colonne, le moteur injecte la valeur courante du compteur.
• Si une valeur explicite est fournie et qu'elle est supérieure ou égale au compteur actuel, l'insertion réussit et le compteur est recalibré sur valeur_insérée + pas.
• Si la valeur explicite est inférieure au compteur, le compteur global n'est pas modifié (sous réserve qu'il n'y ait pas de conflit de clé).

Contraintes architecturales

• Un seul champ par table peut posséder cette propriété.
• Le champ doit obligatoirement être indexé (généralement en tant que clé primaire).
• La colonne ne peut pas accepter les valeurs NULL.
• Le type de données doit être un entier (TINYINT, INT, BIGINT, etc.). Si la limite maximale du type est atteinte, le mécanisme se bloque.

Exemples de configurations invalides :

-- Erreur : Absence de définition en tant que clé
CREATE TABLE personnel (
    staff_id INT AUTO_INCREMENT,
    role VARCHAR(30)
);
-- ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

-- Erreur : Type de données incompatible
CREATE TABLE personnel (
    staff_id INT PRIMARY KEY,
    department_code VARCHAR(10) UNIQUE KEY AUTO_INCREMENT
);
-- ERROR 1063 (42000): Incorrect column specifier for column 'department_code'

Analyse des Discontinuités de Séquence

L'identifiant généré n'est pas toujours strictement séquentiel. Plusieurs scénarios techniques provoquent des "trous" dans la numérotation.

Conflits sur index unique

Le compteur d'auto-incrémentation est consommé de manière définitive au moment de la tentative d'insertion, indépendamment du succès de la requête.

CREATE TABLE `subscribers` (
  `sub_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `email` varchar(100) DEFAULT NULL,
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT = 1;

INSERT INTO subscribers VALUES (1, 'alice@example.com', 28);

Si une seconde requête tente d'insérer la même adresse email :

mysql> INSERT INTO subscribers VALUES (NULL, 'alice@example.com', 30);
ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'subscribers.idx_email'

Bien que la transaction ait échoué, l'identifiant 2 a été alloué en mémoire. La prochaine insertion valide recevra donc l'identifiant 3.

Allocation exponentielle lors des insertions en masse

Pour les instructions dont le volume de données est imprévisible à l'avance (comme INSERT INTO ... SELECT), MySQL utilise un algorithme d'allocation dynamique pour minimiser les verrous :

1. Première demande : allocation de 1 ID.
2. Deuxième demande : allocation de 2 ID.
3. Troisième demande : allocation de 4 ID.
4. Le volume alloué double à chaque itération.

CREATE TABLE `source_data` (
  `record_id` int(11) NOT NULL AUTO_INCREMENT,
  `payload` int(11) DEFAULT NULL,
  PRIMARY KEY (`record_id`)
) ENGINE=InnoDB;

INSERT INTO source_data VALUES (NULL, 10), (NULL, 20), (NULL, 30), (NULL, 40);

CREATE TABLE `destination_data` LIKE source_data;
INSERT INTO destination_data(payload) SELECT payload FROM source_data;
INSERT INTO destination_data VALUES (NULL, 50);

Dans cet exemple, la copie des 4 lignes a déclenché des allocations de 1, 2, puis 4 ID (total 7 ID réservés). Les ID 5, 6 et 7 sont perdus. La ligne finale insérée manuellement recevra l'ID 8.

Comportement de ON DUPLICATE KEY UPDATE

L'utilisation de clauses conditionnelles d'insertion/mise à jour consomme également des valeurs du compteur, même si l'opération finale est une simple mise à jour.

CREATE TABLE user_accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    login_count INT DEFAULT 0
);

INSERT INTO user_accounts (username, login_count) VALUES ('bob', 1);

Exécution d'une mise à jour conditionnelle :

INSERT INTO user_accounts (username, login_count) 
VALUES ('bob', 2)
ON DUPLICATE KEY UPDATE login_count = VALUES(login_count);

-- Insertion d'un nouvel utilisateur
INSERT INTO user_accounts (username, login_count) VALUES ('charlie', 1);

L'identifiant de 'charlie' sera 3. La mise à jour de 'bob' a implicitement incrémenté le compteur interne de la table.

Autres facteurs de fragmentation

• Suppressions de lignes : La suppression d'un enregistrement ne recycle pas son identifiant.
• Annulations de transaction (Rollbacks) : Si une transaction insère des lignes puis est annulée, les identifiants générés pendant son exécution ne sont pas récupérés.

Techniques de réinitialisation

Vidage complet de la table

La commande TRUNCATE supprime toutes les données et réinitialise le compteur à sa valeur de base. Cette opération est irréversible.

TRUNCATE TABLE user_accounts;

Ajustement manuel du compteur

Il est possible de forcer la prochaine valeur via une altération de la structure :

ALTER TABLE user_accounts AUTO_INCREMENT = 1000;

Récupération de l'identifiant généré

Pour lier des tables enfants, il est recommandé de laisser le moteur générer la clé et de la récupérer immédiatement après :

INSERT INTO user_accounts (username) VALUES ('dave');
SELECT LAST_INSERT_ID();

Justification architecturale de la non-réutilisation

L'absence de recyclgae des identifiants annulés ou échoués est un choix délibéré axé sur la performance concurrentielle.

Imaginons deux transactions simultanées. Pour garantir l'unicité, MySQL place un verrou léger sur le compteur. La transaction A obtient l'ID 100 et la transaction B obtient l'ID 101. Si la transaction A est annulée (rollback) et que MySQL décide de remettre l'ID 100 dans le pool des valeurs disponibles, la prochaine requête C pourrait recevoir cet ID 100. Cependant, si la transaction B a entre-temps été validée, l'intégrité séquentielle est déjà rompue. Pire encore, tenter de vérifier systématiquement si un ID libéré est "sûr" à réutiliser exigerait des verrous globaux lourds sur la table, détruisant le débit d'insertion.

Par conséquent, le moteur InnoDB sacrifie la continuité stricte des séquences pour garantir un mécanisme de génération d'identifiants hautement performant et sans goulot d'étranglement.