Comment résoudre le problème lorsque l'ID auto-croissant MySQL est épuisé

mysql n'a plus d'identifiants auto-croissants, que dois-je faire ?

En tant que programmeur, lors d'entretiens d'embauche, je me demande si vous avez déjà rencontré des problèmes comme celui-ci.

Zhang Gong est un programmeur Java. Il s'est récemment rendu dans une société Internet pour un entretien et l'intervieweur lui a posé une telle question.

Intervieweur : "Avez-vous déjà utilisé MySQL ? Utilisez-vous l'ID de clé primaire à incrémentation automatique ou l'UUID comme ID de clé primaire dans votre table de données ?" # Zhang Gong : « Utiliser la clé primaire à incrémentation automatique »

Intervieweur : « Pourquoi la clé primaire à incrémentation automatique ? »

Zhang Gong : « En raison de l'utilisation de la clé primaire à incrémentation automatique En augmentant la clé primaire, les données sont stockées séquentiellement dans la structure physique. Les performances sont bonnes"

Intervieweur : "Alors la clé primaire auto-incrémentée a atteint la valeur maximale. Que dois-je faire si c'est le cas. épuisé ? "

Zhang Gong : « Quand il est épuisé, il est épuisé. Postulez à nouveau.呗 »

Intervieweur : « Vous pouvez revenir en arrière et attendre. la notification"

Aujourd'hui, nous allons parler de ce qu'il faut faire si cette clé primaire auto-incrémentée est épuisée. ?

Dans MySQL, la plage du type entier int est la suivante. La plage de valeurs de int est : -2^31——2^31-1, soit -2147483648—2147483647#🎜. 🎜#

Comme le montre l'image :

Par exemple, l'entier non signé peut stocker une plage de 0 à 4294967295, soit environ 4,3 milliards. Lorsque l'identifiant auto-incrémenté atteint la valeur maximale, quelles exceptions se produiront si l'insertion continue

Pratiquons-le ?

Tout d'abord, créez une table tb_user, qui contient uniquement un identifiant d'incrémentation automatique

create table  tb_user(id int unsigned auto_increment primary key) ;

Ensuite, insérez une donnée dans cette table :

insert into tb_user values(null);

#🎜 🎜 #Utilisez la commande show show create table tb_user; pour vérifier la situation de la table :

CREATE TABLE `tb_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Si vous faites attention, vous constaterez que AUTO_INCREMENT est devenu 2, mais c'est loin de la valeur maximale de 4294967295. Si vous voulez qu'il devienne 4294967295 Nous devons insérer beaucoup d'enregistrements, mais cela ne doit pas être si compliqué Nous pouvons déclarer directement la valeur initiale de AUTO_INCREMENT lors de la création de la table.

Ajustez l'instruction de création de table que nous venons de créer. Supprimez d'abord la table tout à l'heure, puis ajoutez auto_increment = 4294967295

create table tb_user(id int unsigned auto_increment primary key) auto_increment = 4294967295;

lors de la création de la table, puis insérez-la dans la table. ainsi. La requête indiquant que l'identifiant est 4294967295 est déjà la valeur maximale. À ce stade, si

est tenté d'insérer une donnée dans la table, une exception de conflit de clé primaire sera signalée comme indiqué ci-dessous.

insert into tb_user values(null);

Cela peut montrer que lors d'une nouvelle insertion, l'ID d'incrémentation automatique utilisé est toujours 4294967295 et qu'une exception de conflit de clé primaire sera signalée.

4294967295, ce numéro peut déjà faire face à la plupart des scénarios. Si votre service insère et supprime fréquemment des données, il existe toujours un risque de manquer.

Il est recommandé d'utiliser bigint unsigned, ce nombre sera grand.

La réponse est oui, et la solution est très simple. Changez le type Int en type BigInt La plage de BigInt est la suivante

-2^63 -1. à 2^63-1

-9223372036854775808 9223372036854775807

Même si chaque Insérer 10 000 éléments de données dans la table de données dans secondes. Après avoir fonctionné pendant 100 ans, voyons combien de données il y a. Vous pouvez résoudre le problème en définissant l'ID d'incrémentation automatique sur le type BigInt.

Si vous répondez ainsi à l'intervieweur pendant l'entretien.

Vous : "Ce n'est pas simple. Il suffit de changer le type de clé primaire auto-incrémentée en type BigInt pour le résoudre !"

Intervieweur : "Vous êtes en ligne Comment changer le type de données de la colonne ? "

Vous : "...ne l'avez jamais réellement utilisé"

Il est à noter que cette méthode n'est disponible que dans myl5.6+. Commence à prendre en charge , MySQL prend en charge la modification en ligne des tables de base de données. Pendant le processus de modification de la table, pour la plupart des opérations, la table d'origine peut être lue et écrite.

Les opérations DML simultanées ne sont pas prises en charge pour les opérations telles que la modification des types de données ! Autrement dit, si vous utilisez directement des instructions comme alter pour modifier la structure des données de la table en ligne, cette table ne pourra pas effectuer d'opérations de mise à jour (suppression, mise à jour, insertion). Il n’est donc pas possible de modifier la structure des tables sur la chaîne de production.

Y a-t-il une meilleure façon ? Nous discuterons de cette question plus tard.

Je me demande si vous avez remarqué une telle situation. Bien que l'ID d'incrémentation automatique de la clé primaire commence à 0, c'est-à-dire que la plage qui peut être utilisée maintenant est 0~2147483647, mais il y en a. certains identifiants dans les données réelles Les valeurs ne sont pas continues.

Si la table de production réelle a une seule table avec plus de centaines de millions de données et que vous souhaitez écrire des données dans la table de données à ce moment-là, les performances seront certainement affectées et vous devez considérez rapidement les sous-bases de données et les tables.

Si la base de données est divisée en tables, l'ID auto-incrémenté de chaque table ne peut pas être utilisé pour identifier les données de manière globale et unique. Pour prendre en charge l'environnement des bases de données et des tables de partitionnement, nous devons fournir une stratégie permettant de générer des numéros d'identification uniques au monde.

Donc en pratique, il n'y a aucun moyen d'attendre que la clé primaire auto-incrémentée soit épuisée.

Pour une réponse plus conviviale, autant vous référer à ceci

Intervieweur : "Alors la clé primaire auto-incrémentée a atteint la valeur maximale. Quoi dois-je faire si elle est épuisée ?" #🎜🎜 #

Vous : Je n'ai jamais rencontré ce problème auparavant, car nous utilisons le type int pour la clé primaire auto-incrémentée, qui ne peut généralement pas atteindre la valeur maximale, nous devons donc envisager de diviser les tables et les bases de données.

Si l'intervieweur ne cesse de vous courir après et continue de vous interroger sur les points clés des sous-bases de données et sous-tableaux, vous pouvez également répondre de manière ciblée, en indiquant que vous avez plein expérience de développement dans ce domaine, je pense que cela ajoutera des points à cette interview.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!