Incrément automatique après suppression dans MySQL

J’ai une table MySQL avec un champ clé primaire sur lequel AUTO_INCREMENT est activé. Après avoir lu d’autres articles ici, j’ai remarqué des personnes avec le même problème et avec des réponses variées. Certains recommandent de ne pas utiliser cette fonctionnalité, d’autres affirment qu’il ne peut pas être «corrigé».

J’ai:

table: course fields: courseID, courseName 

Exemple: nombre d’enregistrements dans le tableau: 18. Si je supprime les enregistrements 16, 17 et 18 – je m’attendrais à ce que le prochain enregistrement entré ait le code d’identification de 16, mais ce sera 19 car le dernier coursID entré était 18.

Mes connaissances en SQL ne sont pas étonnantes, mais existe-t-il un moyen d’actualiser ou de mettre à jour ce nombre avec une requête (ou un paramètre dans l’interface phpMyAdmin)?

Ce tableau se rapportera à d’autres dans une firebase database.


Compte tenu de tous les conseils, j’ai décidé d’ignorer ce «problème». Je vais simplement supprimer et append des enregistrements tout en laissant l’incrément automatique faire son travail. Je suppose que le nombre n’a pas vraiment d’importance, car il n’est utilisé que comme identifiant unique et n’a pas de signification commerciale (comme mentionné ci-dessus).

Pour ceux que j’ai pu confondre avec mon message original: Je ne souhaite pas utiliser ce champ pour savoir combien de disques j’ai. Je voulais juste que la firebase database soit nette et plus cohérente.

Ce que vous essayez de faire semble dangereux, car ce n’est pas l’utilisation prévue de AUTO_INCREMENT .

Si vous voulez vraiment trouver la plus petite valeur de clé inutilisée, n’utilisez pas du tout AUTO_INCREMENT et gérez vos clés manuellement. Cependant, ceci n’est PAS une pratique recommandée.

Prenez un peu de recul et demandez « pourquoi vous devez recycler les valeurs clés? » Les BIGINT non signées INT (ou BIGINT ) ne fournissent-elles pas suffisamment d’espace clé?

Avez-vous vraiment plus de 18,446,744,073,709,551,615 enregistrements uniques au cours de la vie de votre application?

 ALTER TABLE foo AUTO_INCREMENT=1 

Si vous avez supprimé les entrées les plus récentes, vous devez définir celles qui sont les plus basses disponibles. Comme dans, tant qu’il n’y a pas déjà 19, supprimer 16-18 réinitialisera l’incrémentation à utiliser 16.


EDIT: J’ai raté le bit à propos de phpmyadmin. Vous pouvez le définir aussi. Accédez à l’écran du tableau et cliquez sur l’onglet Opérations. Il existe un champ AUTOINCREMENT que vous pouvez définir manuellement.

Les clés d’incrémentation primaire dans la firebase database sont utilisées pour identifier de manière unique une ligne donnée et ne doivent recevoir aucune signification métier . courseOrder donc la clé primaire telle courseOrder et ajoutez une autre colonne appelée par exemple courseOrder . Ensuite, lorsque vous supprimez un enregistrement de la firebase database, vous souhaiterez peut-être envoyer une instruction UPDATE supplémentaire afin de décrémenter la colonne courseOrder de toutes les lignes dont le courseOrder supérieur à celui que vous supprimez.

En parallèle, vous ne devez jamais modifier la valeur d’une clé primaire dans une firebase database relationnelle, car d’autres tables peuvent la référencer en tant que clé étrangère et la modifier risque de violer les contraintes référentielles.

Essayez:

SET @num: = 0;

UPDATE your_table SET id = @num: = (@ num + 1);

ALTER TABLE tableName AUTO_INCREMENT = 1;

Cela réinitialisera la valeur auto-incrémentée, puis comptera chaque ligne pendant qu’une nouvelle valeur est créée pour elle.

exemple: avant

  • 1: première valeur ici
  • 2: deuxième valeur ici
  • X: valeur supprimée
  • 4: Le rest de la table
  • 5: Le rest du rest ..

la table affichera donc le tableau: 1,2,4,5

Exemple: APRÈS (si vous utilisez cette commande, vous obtiendrez)

  • 1: première valeur ici
  • 2: deuxième valeur ici
  • 3: Le rest de la table
  • 4: le rest du rest

Aucune trace de la valeur supprimée et le rest de l’incrémentation continue avec ce nouveau compte.

MAIS

  1. Si quelque part sur votre code quelque chose utilise la valeur auto-incrémentée … peut-être que cette atsortingbution posera problème.
  2. Si vous n’utilisez pas cette valeur dans votre code, tout devrait être correct.

Vous ne devriez pas vous fier à l’identifiant AUTO_INCREMENT pour vous dire combien d’enregistrements vous avez dans la table. Vous devriez utiliser SELECT COUNT(*) FROM course . Les identifiants sont là pour identifier de manière unique le cours et peuvent être utilisés comme références dans d’autres tableaux, vous ne devez donc pas répéter les identifiants et ne pas chercher à réinitialiser le champ d’incrémentation automatique.

vous pouvez sélectionner les identifiants comme suit:

 set @rank = 0; select id, @rank:=@rank+1 from tbl order by id 

le résultat est une liste d’identifiants et leurs positions dans la séquence.

vous pouvez également réinitialiser les identifiants comme suit:

 set @rank = 0; update tbl a join (select id, @rank:=@rank+1 as rank from tbl order by id) b on a.id = b.id set a.id = b.rank; 

Vous pouvez aussi simplement imprimer le premier identifiant inutilisé comme suit:

 select min(id) as next_id from ((select a.id from (select 1 as id) a left join tbl b on a.id = b.id where b.id is null) union (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id where b.id is null)) c; 

Après chaque insertion, vous pouvez réinitialiser l’auto-incrémentation:

 alter table tbl auto_increment = 16 

ou définir explicitement la valeur id lors de l’insertion:

 insert into tbl values (16, 'something'); 

En général, cela n’est pas nécessaire, vous avez un count(*) et la possibilité de créer un numéro de classement dans vos jeux de résultats. un classement typique pourrait être:

 set @rank = 0; select a.name, a.amount, b.rank from cust a, (select amount, @rank:=@rank+1 as rank from cust order by amount desc) b where a.amount = b.amount 

clients classés par montant dépensé.

Je suis venu ici à la recherche d’une réponse à la question du titre "MySQL - Auto Increment after delete" mais je n’ai pu trouver qu’une réponse à cela dans les questions

  • Comment supprimer certaines lignes de la table mysql?
  • Comment réinitialiser AUTO_INCREMENT dans MySQL?

En utilisant quelque chose comme:

 DELETE FROM table; ALTER TABLE table AUTO_INCREMENT = 1; 

Notez que la réponse de Darin Dimitrov explique très bien AUTO_INCREMENT et son utilisation. Jetez un oeil là avant de faire quelque chose que vous pourriez regretter.

PS: La question elle-même est plus "Why you need to recycle key values?" et la réponse de Dolph couvre cela.

J’ai une méthode très simple mais délicate.

Lors de la suppression d’une ligne, vous pouvez conserver les ID dans une autre table temporaire. Après cela, lorsque vous insérez de nouvelles données dans la table principale, vous pouvez rechercher et sélectionner des identifiants dans la table temporaire. Alors, utilisez une coche ici. Si la table temporaire n’a pas d’ID, calculez l’ID maximum dans la table principale et définissez le nouvel ID comme new_ID = old_max_ID+1 : new_ID = old_max_ID+1 .

NB: Vous ne pouvez pas utiliser la fonction d’incrémentation automatique ici.

Je peux penser à beaucoup de scénarios où vous pourriez avoir besoin de le faire, en particulier pendant un processus de migration ou de développement. Par exemple, je viens juste de créer une nouvelle table en croisant deux tables existantes (dans le cadre d’un processus de configuration complexe), puis j’ai besoin d’append une clé primaire après l’événement. Vous pouvez supprimer la colonne de clé primaire existante, puis procédez comme suit.

 ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`); 

Pour un système live, ce n’est pas une bonne idée, et surtout s’il y a d’autres tables avec des clés étrangères qui le pointent.

Il existe en fait un moyen de résoudre ce problème. D’abord, vous supprimez la colonne de clé primaire auto_incremented, puis vous l’ajoutez à nouveau, comme ceci:

 ALTER TABLE table_name DROP column_name; ALTER TABLE table_name ADD column_name int not null auto_increment primary key first; 

Vous pouvez utiliser votre logiciel / script client mysql pour spécifier l’origine de la clé primaire après la suppression des enregistrements requirejs.

Ce que vous essayez de faire est très dangereux. Pensez-y attentivement. Il y a une très bonne raison pour le comportement par défaut de l’incrémentation automatique.

Considère ceci:

Un enregistrement est supprimé dans une table qui a une relation avec une autre table. L’enregistrement correspondant dans le deuxième tableau ne peut pas être supprimé pour des raisons d’audit. Cet enregistrement devient orphelin à partir de la première table. Si un nouvel enregistrement est inséré dans la première table et qu’une clé primaire séquentielle est utilisée, cet enregistrement est maintenant lié à l’orphelin. De toute évidence, c’est mauvais. En utilisant une PK auto-incrémentée, un identifiant qui n’a jamais été utilisé est toujours garanti. Cela signifie que les orphelins restnt orphelins, ce qui est correct.

 if($id == 1){ // deleting first row mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>1"); } else if($id>1 && $id<$num){ // deleting middle row mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id>$id"); } else if($id == $num){ // deleting last row mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num"); } else{ echo "ERROR"; } mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num"); 

Vous pouvez penser à faire un déclencheur après la suppression pour pouvoir mettre à jour la valeur de l’autoincrement et la valeur de l’ID de toutes les lignes qui ne ressemble pas à ce que vous vouliez voir.

Vous pouvez donc travailler avec la même table et l’incrémentation automatique sera corrigée automatiquement chaque fois que vous supprimerez une ligne que le déclencheur corrigera.

C’est certainement pas recommandable. Si vous avez une grande firebase database avec plusieurs tables, vous aurez probablement enregistré un ID utilisateur tel qu’identifié dans la table 2. Si vous réorganisez la table 1, l’ID utilisateur prévu ne sera probablement pas l’ID de table 2 voulu.

voici une fonction qui corrige votre problème

  public static void fixID(Connection conn, Ssortingng table) { try { Statement myStmt = conn.createStatement(); ResultSet myRs; int i = 1, id = 1, n = 0; boolean b; Ssortingng sql; myRs = myStmt.executeQuery("select max(id) from " + table); if (myRs.next()) { n = myRs.getInt(1); } while (i <= n) { b = false; myRs = null; while (!b) { myRs = myStmt.executeQuery("select id from " + table + " where id=" + id); if (!myRs.next()) { id++; } else { b = true; } } sql = "UPDATE " + table + " set id =" + i + " WHERE id=" + id; myStmt.execute(sql); i++; id++; } } catch (SQLException e) { e.printStackTrace(); } }