137 votes

Réorganiser / réinitialiser la clé primaire à incrémentation automatique

J'ai une table MySQL avec une clé primaire à incrémentation automatique. J'ai supprimé quelques lignes au milieu de la table. Maintenant, j'ai, par exemple, quelque chose comme ceci dans la colonne ID : 12, 13, 14, 19, 20. J'ai supprimé les lignes 15, 16, 17 et 18.

Je veux réaffecter / réinitialiser / réorganiser la clé primaire afin d'avoir une continuité, c'est-à-dire faire du 19 un 15, du 20 un 16, et ainsi de suite.

Comment puis-je le faire ?

379voto

Anshul Points 1704

Même si cette question semble être assez ancienne, je vais poster une réponse pour quelqu'un qui arrive ici en cherchant.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

Si la colonne est utilisée comme clé étrangère dans d'autres tables, assurez-vous d'utiliser la fonction ON UPDATE CASCADE au lieu de l'option par défaut ON UPDATE NO ACTION pour la relation de clé étrangère dans ces tables.

En outre, afin de réinitialiser le AUTO_INCREMENT compte, vous pouvez immédiatement faire la déclaration suivante.

ALTER TABLE `users` AUTO_INCREMENT = 1;

Pour les MySQL, il réinitialisera la valeur à MAX(id) + 1 .

1 votes

Avec les clés étrangères, c'est une solution très intéressante pour ma table où beaucoup de choses sont insérées et supprimées et où je veux économiser l'espace de l'index.

2 votes

La documentation mySQL le déconseille : "En règle générale, à l'exception des instructions SET, vous ne devez jamais attribuer une valeur à une variable utilisateur et lire la valeur dans la même instruction. Par exemple, pour incrémenter une variable, il n'y a pas de problème : SET @a = @a + 1 ; Pour d'autres instructions, telles que SELECT, vous pouvez obtenir les résultats escomptés, mais ce n'est pas garanti. Dans l'instruction suivante, vous pourriez penser que MySQL évaluera d'abord @a et effectuera ensuite une affectation : SELECT @a, @a:=@a+1, ... ; Cependant, l'ordre d'évaluation des expressions impliquant des variables utilisateur n'est pas défini. "

3 votes

@ReverseEMF : Non. L'ordre d'affectation est fixé dans les expressions MySQL. D'après ce que vous avez cité, la documentation MySQL déconseille l'utilisation multiple et indépendante de la variable. Dans le cas ci-dessus, l'évaluation de l'expression est destinée à se produire dans un ordre prédéfini en raison d'une seule expression d'affectation "utilisateurs". . id` = @count:= @count + 1`. Extrait de la documentation : "La valeur du côté droit peut être une valeur littérale, une autre variable stockant une valeur, ou toute expression légale qui donne une valeur scalaire".

100voto

Tom Haigh Points 32314

Vous pouvez supprimer la colonne de clé primaire et la recréer. Tous les identifiants devraient alors être réaffectés dans l'ordre.

Cependant, c'est probablement une mauvaise idée dans la plupart des situations. Si vous avez d'autres tables qui ont des clés étrangères vers cette table, cela ne fonctionnera certainement pas.

0 votes

J'ai d'autres tables qui détiennent une clé étrangère à cette table, mais je ne fais que commencer le projet, donc ça me va Merci !

67 votes

Il serait peut-être mieux à long terme que vous essayiez et commenciez à accepter que vos identifiants ne soient pas toujours séquentiels, sinon lorsque vous commencerez à travailler sur des projets plus importants, cela vous rendra vraiment fou !

9 votes

ALTER TABLE votre_table AUTO_INCREMENT =1

66voto

rxgx Points 2468

Pour réinitialiser les ID de ma table User, j'utilise la requête SQL suivante. Il a été dit plus haut que cela ruinera toute relation que vous pourriez avoir avec d'autres tables.

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

0 votes

Sur une table MyISAM de 584 000 lignes, cela a pris environ 7,3 secondes.

3 votes

Si j'avais eu 100 votes, j'aurais voté en faveur de cet article tout le temps, car il décompose les instructions SQL pour un novice comme moi, ce qui facilite la compréhension.

1 votes

La deuxième ligne n'est pas nécessaire ou est-ce que je me trompe ? Il commence par 1,,,, pour InnoDB, mais c'est le cas pour moi.

33voto

Vous pouvez simplement utiliser cette requête

alter table abc auto_increment = 1;

2 votes

Cela ne fonctionnera pas dans ce cas. Pour les tables ISAM, il fixera la valeur de autoinc à max(id) + 1. Pour InnoDB, cela ne fera rien. Voir les docs alter table pour modifier AUTOINCREMENT. dev.mysql.com/doc/refman/5.0/fr/alter-table.html

3 votes

@Ireeder à partir de 5.6 le comportement pour innodb est similaire à celui de myisam

0 votes

Si vous avez d'autres tables qui ont des clés étrangères à cette table, est-ce que cela les brisera ?

17voto

bagyei37 Points 5
SET  @num := 0;

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

ALTER TABLE your_table AUTO_INCREMENT =1;

Je pense que ça va le faire

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X