Discussion :

[andyajram]

Bonjour , j'ai une question a vous demander , dans ma base de données j'ai un champ ID qui s’incrémente a chaque fois que j'insère une ligne, mais le problème c'est que quand je supprime une ligne de la table , et je rajoute une autre , normalement il doit ajouter la nouvelle ligne et lui donner l'ID après la dernière ligne disponible , mais ce qui m'arrive c'est qu'il ajoute l'ID en prenant en compte la ligne supprimer , par exemple : j'ai les lignes

2
3
4
après que je supprime la ligne 4 et je rajoute une autre il faut que j'aie la ligne avec le numéro 4 , mais la ligne s'ajoute avec le numéro 5 , y'a t'il un truc que je dois faire ?

Merci d'avance

[escartefigue]

Bonjour

C'est le fonctionnement normal, ça ne doit pas vous gêner c'est un identifiant technique

Il ne faut d'ailleurs jamais utiliser une colonne identity pour vérifier l'ordre d'arrivée des données car on peut réutiliser des valeurs en modifiant la variable système auto_increment_increment

De plus, si l'identifiant 4 était réutilisé, ce serait très dangereux, car rien n'indique que d'autres tables (éventuellement dans d'autres bases de données) ne feraient pas référence à l'ancienne valeur 4
Du coup, si on réutilisait la même valeur, vous feriez pointer à tort les données de ces autres tables sur une ligne sans aucun rapport !

[andyajram]

Bonjour

C'est le fonctionnement normal, ça ne doit pas vous gêner c'est un identifiant technique

Il ne faut d'ailleurs jamais utiliser une colonne identity pour vérifier l'ordre d'arrivée des données car on peut réutiliser des valeurs en modifiant la variable système auto_increment_increment

non c'est parceque dans mon application je récupère les lignes avec leurs ID donc l'utilisateur quand il va voir les missions elle seront représenter chacune avec son ID , et ce n'est pas très joli de voir des décallage au niveau des ID , un utilisateur basique ne va rien comprendre

[escartefigue]

Encore une fois, l'identifiant auto incrémenté (identity column) est un identifiant technique
Il n'a pas vocation à être présenté aux utilisateurs dans une application
Il ne sert qu'au fonctionnement interne de la base de données

Si vous avez une contrainte fonctionnelle interdisant les trous de numérotation, alors il ne faut pas utiliser une identity column

[andyajram]

d'accord Merci pour l'explication , je vais voir avec le responsable si je peux le retirer de l'affichage

[Artemus24]

salut andyajram.

Comme le dit Escartefigue, l'auto incrément est une fonctionnalité technique du SGBD MySql.
Ce que vous décrivez, c'est le comportement normal de son fonctionnement.
Vous ne devez pas intervenir dans le paramétrage de l'auto increment de votre table.
Je répète à la suite de Escartefigue, cet identifiant est pour un usage technique et ne doit pas intervenir dans un traitement fonctionnel.

Mais rien ne vous oblige à utiliser l'auto increment dans votre table.
A vous de gérer cette colonne selon vos besoin. Par exemple, vous pouvez le simuler par un trigger :

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.num=ifnull((select max(num)+1 from test where val=NEW.val),1);
END$$
 
DELIMITER ;

Ici, la colonne se nomme 'num'.
J'incrémente cette colonne 'num' quand j'ai la même valeur dans la colonne 'val'.
Il existe plusieurs solutions possible en fonction de ce que vous cherchez à faire.

Votre problème est que vous n'aimez pas les trous dans la numérotation de votre identifiant.
Et vous pensez que l'on va atteindre rapidement le maximum de ce que l'on peut stocker dans cette colonne.
Si vous avez peur d'atteindre ce maximum, alors déclarez ceci :

Code :

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

`id`  bigint unsigned  NOT NULL  AUTO_INCREMENT  PRIMARY KEY,

Le maximum est de 18.446.744.073.709.551.615
Vous avez de la marge avant d'atteindre le max.

Et si vous n'aimez vraiment pas les trous, on peut faire une renumérotation de votre colonne auto incrémenté.

Code :

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

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

mais c'est à vos risques et périls !

Personnellement, je n'aime pas trop utiliser cet identifiant auto incrémenté dans une relation fonctionnelle.
Elle n'est vraiment pas parlante du tout.

@+

[escartefigue]

Le problème est surtout, comme je l'ai indiqué précédemment, que réutiliser pour une nouvelle ligne, un identifiant précédemment utilisé pour une ligne supprimée est très risqué.
Exemple
t0 : création de la ligne ayant l'identifiant 4 dans ma table
t1 : une application connexe lit des données de ma base de données récupère une partie des infos et notamment mes identifiants et les stocke dans ses propres tables
t2 : je supprime la ligne 4 dans ma table
t3 : je crée une nouvelle ligne avec l'identifiant 4, puisque je réutilise les trous
t4 : l'application connexe veut récupérer des infos supplémentaires qui concerne la ligne 4 ==> elle récupère des données qui n'ont aucun rapport !
alors qu'elle aurait du avoir un code retour lui indiquant que la ligne 4 n'existe plus
si mon application connexe souhaite par exemple récupérer le solde d'un compte, ou vérifier un mot de passe, imaginez un peu les dégâts potentiels

Donc gros danger à recycler des identifiants !

Je précise que les colonnes auto incrémentées ne sont pas une spécificité de MySQL, la plupart des SGBD proposent ce type d'identifiant, souvent avec des fonctionnalités plus riches que celles proposées par MySQL

[Artemus24]

Salut Escartefigue.

Donc gros danger à recycler des identifiants !

Mais je ne dis pas le contraire. J'ai même dit dans mon précédent message :

mais c'est à vos risques et périls !

L'exemple que tu donnes est un problème d'intégrité des données au travers de deux bases de données.
Normalement, on ne bidouille jamais dans une base de données, pour faire joli.
J'aime pas les trous alors je les bouche. La réponse est non, non et non !!!

On peut se le permettre (mais à vos risques et périls) si la structure de la base l'autorise et encore.
Exemple, une clef étrangère entre deux tables avec "on delete cascade on update cascade" sur la colonne en question.
Dans le cas contraire, c'est-à-dire pas de clef étrangère, ou "restrict" au lieu de "cascade".

La règle est de ne jamais réutiliser un identifiant qui a été supprimé au préalable.

D'où l'introduction de la suppression logique vis-à-vis d'une suppression physique que l'on ne fait jamais.
Exemple, le cas de l'archivage.

@+