Discussion :

[Nicryc]

Bonjour,
J'ai un problème, enfin ce n'est pas vraiment un problème mais bon. En gros j'ai une base de données SQL que j'administre avec phpMyAdmin. J'ai une table Actualité(id, titre, date, description), l'id étant clé primaire et unique. Je l'ai également mis en AUTO_INCREMENT avec phpMyAdmin. Ça l'incrémente bien tout seul à chaque fois que j'ajoute un n-uplet mais lorsque je supprime le dernier n-uplet (id=6 par exemple), le dernier devient ainsi (id=5) et c'est là qu'est mon problème. Lorsque j'ajoute une ligne l'id est automatique incrémenté à 7, ça fait un trou du coup.
Si vous avez une solution au problème.
Merci.

[al1_24]

Problème déjà maintes fois soulevé dans ces forums.
Pour résumer :

• Un identifiant auto-incrémenté est un identifiant technique dont l'unicité est assurée.
• Qu'il y ait des "trous" dans la séquence de l'identifiant technique ne pose aucun problème fonctionnel.
• Si vous souhaitez un identifiant fonctionnel auto-incrémenté, il faut gérer cet incrément "à la main".
• Qu'advient-il si vous supprimez une ligne dans votre table ? Allez-vous décaler tous les identifiants qui suivent la ligne supprimée pour "remplir le trou" ?

[Nicryc]

Oui je sais ça ne pose pas de problèmes mais au bout d'un moment ça va faire des très grands nombres quoi. Du coup, oui j'aimerais décalé tous les id après une ligne supprimer pour remplir le trou. Donc il faut le faire à la main ? En PHP (sachant que je fais un site web) ?

[escartefigue]

Comme l'a expliqué al1_24, vu qu'il s'agit d'un identifiant purement technique, la présence de ces trous n'a absolument aucune importance, et le risque est très faible de saturer (sauf si vous choisissez une valeur d'incrément très élevée et un identifiant en small mais bon faudrait être tordu )
C'est justement l'un de intérêts de ce type d'identifiant que ne pas avoir à se préoccuper de la chronologie des valeurs.

Si votre table est appelée à contenir un très grand nombre de lignes (des centaines de milliard), choisissez un identifiant de type bigint

N'essayez surtout pas de "boucher" les trous, surtout si les volumes sont conséquents, vous allez mettre à plat votre base de données quand il s'agira de propager les mises à jour d'identifiant dans les tables enfant via les foreign key.

[Nicryc]

Mais même quand la table est vide il y a pas moyen de refaire reprendre l'incrémentation à 0 ?

[escartefigue]

Si: en jouant sur les système_variable

http://dev.mysql.com/doc/refman/5.7/...crement_offset

[Artemus24]

Salut Nicryc.

Problème déjà maintes fois soulevé dans ces forums.

Remarque tout à fait exacte ! Mais qui a une porté très limitée pour le résultat final.

Lorsque j'ajoute une ligne l'id est automatique incrémenté à 7, ça fait un trou du coup.

Si ce sont les trous qui vous dérange, alors n'utilisez pas l'auto incrément, gérez le par vous-même.
Mais se substituer à l'auto incrément qui fonctionne parfaitement, je ne voie pas trop l'intérêt.

Mais même quand la table est vide il y a pas moyen de refaire reprendre l'incrémentation à 0 ?

L'incrémentation ne commence pas à zéro, mais à 1.

Une solution consiste à rechercher la plus grande valeur de votre identifiant, avant de l'insérer dans la table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ===================
-- Trigger `increment`
-- ===================
 
DROP TRIGGER IF EXISTS `incrementer`;
 
DELIMITER $$
CREATE TRIGGER `incrementer`
BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
  SET NEW.id=ifnull((select max(id)+1 from test),1);
END$$
 
DELIMITER ;

Ceci prend bien plus de temps que de passer par l'auto incrément, qui je le rappelle fonctionne parfaitement sous MySql.

Sinon, de temps en temps, vous pouvez vous permettre de renuméroter vos lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

update `test`, (select @x:=0) as x set id = (@x:=@x+1);

@+

[al1_24]

(...)Sinon, de temps en temps, vous pouvez vous permettre de renuméroter vos lignes(...)

Sans oublier de mettre à jour toutes les autres tables qui feraient référence à ces valeurs...

[Artemus24]

Salut Al1_24.

Sans oublier de mettre à jour toutes les autres tables qui feraient référence à ces valeurs...

Pas nécessairement, si ce sont des foreign key, dont on a déclaré ceci (voir ce qui est rouge) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT `FK_01` FOREIGN KEY (`...`) REFERENCES `...` (`...`) ON DELETE CASCADE ON UPDATE CASCADE

@+

[escartefigue]

Sauf que quand je mentionnais ceci :

N'essayez surtout pas de "boucher" les trous, surtout si les volumes sont conséquents, vous allez mettre à plat votre base de données quand il s'agira de propager les mises à jour d'identifiant dans les tables enfant via les foreign key.

C'était justement pour alerter de ce qui se passe à cause des

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONSTRAINT `FK_01` FOREIGN KEY (`...`) REFERENCES `...` (`...`) ON DELETE CASCADE ON UPDATE CASCADE

Plus il y a des FK et plus il y a de volume dans les tables enfant, plus le risque est grand de modifier en masse les valeurs de clef, y compris quand ces modifs en masse sont gérées par le SGBD au travers des contraintes

En outre, attention à ne pas confondre incrément et valeur initiale :

L'incrémentation ne commence pas à zéro, mais à 1.

L'incrément ne commence pas par 1 ou toute autre valeur, il vaut par défaut 1, mais peut prendre une autre valeur paramétrée avec la variable auto_increment_increment
C'est la valeur initiale qui commence par défaut à 1, mais qui peut être elle aussi paramétrée autrement via la variable auto_increment_offset
Ces 2 variables peuvent prendre les valeurs 1 à 65535

CF. le lien vers la doc MySQL précédemment communiqué