[Mysql] redéfinition des id auto incrémentés après suppression

redéfinition des id auto incrémentés après suppression [Mysql] - SQL/NoSQL - Programmation

Marsh Posté le 08-12-2014 à 17:26:46    

Bonjour à tous,
 
base de donnée Mysql
 
Comme expliqué dans le titre, j'aurai besoin de redéfinir les valeurs de mes Id auto incrémentés, sachant qu'ils sont clés primaire.
 
l'idéal serait de pouvoir faire cela avec un trigger.  
 
Cette table dois pouvoir contenir plus d'1 million de d'enregistrement.
 
J'ai cru comprendre qu'en recréant la table les index serais denouveau nikel, mais si il y'a une autre solution je suis preneur.  
 
Merci


Message édité par kontas le 08-12-2014 à 22:38:23
Reply

Marsh Posté le 08-12-2014 à 17:26:46   

Reply

Marsh Posté le 08-12-2014 à 23:56:21    

après de longue recherche j'ai trouvé cela
 
SET  @num := 0;
 
UPDATE your_table SET id = @num := (@num+1);
 
ALTER TABLE your_table AUTO_INCREMENT =1;
 
qu'en pensez vous ?

Reply

Marsh Posté le 09-12-2014 à 10:47:15    

Oui, enfin attention : si ton id est clé étrangère dans une autre table, tu vas avoir de drôles de résultats quand tu vas faire des jointures si tu répercutes pas les modifs de ta table où id est clé primaire dans toutes les tables où il est clé étrangère :/
 
Edit : c'est quoi le besoin d'avoir tous les id contigus dans une table :??:


Message édité par rufo le 09-12-2014 à 10:48:13

---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 09-12-2014 à 11:04:33    

Salut,
 
Cet id que je veux contigu est pour gérer une sorte de classement.
 
Cette table est uniquement composé de deux champs :
 
idclassement, auto incrémenté
idutilisateur, qui ne peut y être qu'une seul fois. (clé étrangère)
 
 
Des utilisateurs peuvent s'ajouter au classement par le bas. Mais certain peuvent être supprimé.  
 
Comme ce classement a besoin d'être juste en temps réel et doit pouvoir contenir jusqu'à 1 million de ligne.
 
De plus je dois faire des calcules et extraire des Idutilisateur par tranche de classement

Message cité 1 fois
Message édité par kontas le 09-12-2014 à 11:04:45
Reply

Marsh Posté le 09-12-2014 à 11:19:26    

je ne vois pas trop comment ça marche ton truc :), car pas un pro en msql ...
mais comme ça, à la lecture de ton code :
ton update fait quoi?  
set id =@num := (@num+1) , cela correspond à quoi? à un si? comment il s'incremente? (c'est le altertable apres?)
tu initialises juste avant ton @num à 0 ...  
j'ai peur que ton update n'essaie donc que de mettre ton id à 1 (à vérifier avec un champ auto incrémental)
et vu que c'est une clé unique... boum... erreur :)
En plus attention c'est un ID , donc déjà, updater un ID c'est pas "bon" car il faut penser à l'intégrité de ta base et de modifier tout tes tables, qui font références à cet ID... Courage lol
Si c'est juste un problème de trou dans tes ID, ne te casse pas...
ce n'est vraiment pas important...  sauf si cela représente un numéro de facture...
Question grandeur d'un auto incrément... c'est bien supérieur au million donc pas de soucis...
 
Le truc "simple", sinon... pour avoir un ID continu:
tu réinialises ton auto_increment à 1 dans ta table
tu fais une copie de celle ci dans table bk
puis tu delete l'ensemble de tes enregistrements (attention aux contraintes et triggers, puis tjs à l'intégrité de tes données)
et tu fais un insert de ta table bk (en la triant) dans ta table vide (sans mettre le champ id dans ton insert (ou à null , ou à zero, à voir)...
 
c'est de la bidouille, mais ça marche bien :)... attention à l'intégrité des données... on n'update jamais un ID, en théorie...


---------------
mieux vaut être un con au chaud, qu'un con gelé lol
Reply

Marsh Posté le 09-12-2014 à 11:26:39    

Oublie l'idée d'utiliser / modifier tes ID auto-incrémenté (et tes ID tout court d'ailleurs). C'est une (très) mauvaise idée. Tu vas tout pèter, ça va jamais marcher comme tu le voudrais, ... vraiment, oublie.

 

Jettes un coup d'oeil ici éventuellement : Ranking en MySql. Ca donne déjà une première piste.

 

Une variante intéressante ici : http://stackoverflow.com/questions [...] n-in-mysql


Message édité par kao98 le 09-12-2014 à 11:29:18

---------------
Kao ..98 - Uplay (R6S) : kao98.7.62x39 - Origin (BF4, BF1) : kntkao98
Reply

Marsh Posté le 09-12-2014 à 11:54:04    

kontas a écrit :

Salut,
 
Cet id que je veux contigu est pour gérer une sorte de classement.
 
Cette table est uniquement composé de deux champs :
 
idclassement, auto incrémenté
idutilisateur, qui ne peut y être qu'une seul fois. (clé étrangère)
 
 
Des utilisateurs peuvent s'ajouter au classement par le bas. Mais certain peuvent être supprimé.  
 
Comme ce classement a besoin d'être juste en temps réel et doit pouvoir contenir jusqu'à 1 million de ligne.
 
De plus je dois faire des calcules et extraire des Idutilisateur par tranche de classement


Bon déjà, si c'est juste pour classer des utilisateurs, je vois vraiment pas l'intérêt d'avoir défini ton champ de classement comme clé primaire :pt1cable:  
 
Un lien qui devrait t'intéresser : https://haveacafe.wordpress.com/200 [...] nts-mysql/


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 09-12-2014 à 12:48:10    

une sorte de classement avec un ID incrémental, c'est étrange :)
en plus tu dis que ton utilisateur est unique... wahooo
si ton utilisateur "rejoue" ou se représente , cela veut dire que tu updates l'ID incrémental?  
Ou tu interdis cette possibilité?
Kao98, à mon avis à raison :)... oublies cette façon, et il me semble plus voir un soucis de conception de BD pour avoir à gérer de cette façon ton classement...

Message cité 1 fois
Message édité par gpl73 le 09-12-2014 à 12:49:15

---------------
mieux vaut être un con au chaud, qu'un con gelé lol
Reply

Marsh Posté le 09-12-2014 à 15:41:15    

C'est vrai que dans l'idée, modifier les id n'est pas forcement la meilleur qui soit  :D.
 
J'avais penser à cela par économie de bout de chandelle, pourquoi un champs supplémentaire alors que l'id de la ligne peut suffire...
 
L'utilisation de l'auto incrément été juste par faciliter, pas besoin de récupérer la valeur maxi du positionnement à chaque insert dans ma table.  
 
Vis à vis de la solution que j'ai trouvé en deuxième post  fonctionne, mais d'après vos commentaire je comprend qu'il vaut mieux que je trouve une autre solution.
 
L'idée de la fonction Rank() est intéressante, je vais faire des tests avec cela !
 
 
 

gpl73 a écrit :

une sorte de classement avec un ID incrémental, c'est étrange :)
en plus tu dis que ton utilisateur est unique... wahooo
si ton utilisateur "rejoue" ou se représente , cela veut dire que tu updates l'ID incrémental?  
Ou tu interdis cette possibilité?
Kao98, à mon avis à raison :)... oublies cette façon, et il me semble plus voir un soucis de conception de BD pour avoir à gérer de cette façon ton classement...


 
Si un utilisateur ne joue plus, il est supprimé de la table, si il veux rejouer, il est réinséré en fin de table, donc de classement.
 
Dans l'idée c'est assez simple,
 
j'ai besoin d'une table avec le classement de tout les utilisateurs, avec dans l'idéal un champs avec la position, si un utilisateur ne participe plus, il est supprimé de la table. Cette suppression doit entrainer un recalcule complet des positions.

Reply

Marsh Posté le 09-12-2014 à 15:41:48    

rufo a écrit :


Bon déjà, si c'est juste pour classer des utilisateurs, je vois vraiment pas l'intérêt d'avoir défini ton champ de classement comme clé primaire :pt1cable:  
 
Un lien qui devrait t'intéresser : https://haveacafe.wordpress.com/200 [...] nts-mysql/


 
Merci pour le lien, je vais étudier cela

Reply

Marsh Posté le 09-12-2014 à 15:41:48   

Reply

Marsh Posté le 09-12-2014 à 16:46:53    

Mon lien donne une solution similaire à celle de Kao98 : http://www.fromdual.com/ranking-mysql-results


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 09-12-2014 à 22:09:52    

En utilisant ce système j'arrive bien à générer un classement de mes utilisateurs. Par contre j'aurais bien aimer créer une vue avec cette requête, mais il est apparemment impossible de définir une variable dans la création d'une vue :/

Reply

Marsh Posté le 10-12-2014 à 08:26:00    

Pas forcément performant, mais ça peut donner une piste : http://stackoverflow.com/questions [...] mysql-view

Message cité 1 fois
Message édité par kao98 le 10-12-2014 à 08:27:15

---------------
Kao ..98 - Uplay (R6S) : kao98.7.62x39 - Origin (BF4, BF1) : kntkao98
Reply

Marsh Posté le 10-12-2014 à 12:02:52    


kontas a écrit :


 
Si un utilisateur ne joue plus, il est supprimé de la table, si il veux rejouer, il est réinséré en fin de table, donc de classement.
 
Dans l'idée c'est assez simple,
 
j'ai besoin d'une table avec le classement de tout les utilisateurs, avec dans l'idéal un champs avec la position, si un utilisateur ne participe plus, il est supprimé de la table. Cette suppression doit entrainer un recalcule complet des positions.


 
C'est un classement sans point ? ... mais juste par "entrée" dans ton système...
Ne serait ce pas plus simple alors de flaguer ceci?
tu fais une table, avec ton id_id utilisateur (unique), et le(s)champ(s) date+heure d'entrée (ou un timestamp direcetement)...
après quoi, tu tries en fonction de ton champ date... et hop, plus rien à faire :)  
Le recalcul de la position est à mon avis une fonction à définir, est à extraire au moment T, de ton traitement...non?


---------------
mieux vaut être un con au chaud, qu'un con gelé lol
Reply

Marsh Posté le 10-12-2014 à 17:26:43    

kao98 a écrit :

Pas forcément performant, mais ça peut donner une piste : http://stackoverflow.com/questions [...] mysql-view


 
tu parle de cette requête la :  
 

Code :
  1. SELECT t.id,
  2.        t.variety,
  3.        (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM
  4.   FROM TABLE t


 
J'ai pas trop saisie sa méthode, il créer une seconde table 'TABLE' qui sert juste de reference au classement ?
 

gpl73 a écrit :


 
 
C'est un classement sans point ? ... mais juste par "entrée" dans ton système...
Ne serait ce pas plus simple alors de flaguer ceci?
tu fais une table, avec ton id_id utilisateur (unique), et le(s)champ(s) date+heure d'entrée (ou un timestamp direcetement)...
après quoi, tu tries en fonction de ton champ date... et hop, plus rien à faire :)  
Le recalcul de la position est à mon avis une fonction à définir, est à extraire au moment T, de ton traitement...non?


 
C'était bien ma première première idée. Classer par TimeStamp est assez préçi, par contre je me retrouve toujours avec la même problématique, a savoir une colonne avec la position.
 
Mais je pourrais combiné plusieurs méthode, créer une colonne timestamp, puis en faisant la requête, je me base sur le timeStamp pour générer une colonne avec la position.

Reply

Marsh Posté le 10-12-2014 à 17:47:22    

kontas a écrit :

 

tu parle de cette requête la :

 
Code :
  1. SELECT t.id,
  2.        t.variety,
  3.        (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM
  4.   FROM TABLE t
 

J'ai pas trop saisie sa méthode, il créer une seconde table 'TABLE' qui sert juste de reference au classement ?

 


 

Non, il ne crée pas une table TABLE ! TABLE ça représente la table que tu requête !
C'est juste une sous requête en fait. On peut imaginer ça mixer avec l'histoire du timestamp.

 
Code :
  1. CREATE TABLE user (
  2.         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.         name VARCHAR(100),
  4.         created TIMESTAMP DEFAULT NOW()
  5.       );
  6.  
  7. INSERT INTO user (name) VALUES ('user 1');
  8. INSERT INTO user (name) VALUES ('user 2');
  9. INSERT INTO user (name) VALUES ('user 3');
  10. INSERT INTO user (name) VALUES ('user x');
  11. INSERT INTO user (name) VALUES ('user 4');
  12.  
  13. DELETE FROM user WHERE name LIKE 'user x';
  14.  
  15. SELECT
  16.  id,
  17.  name,
  18.  (SELECT COUNT(*) FROM user WHERE created < u.created ORDER BY created) +1 AS rank
  19.  
  20. FROM user u
  21. ORDER BY created;
 

NB: j'ai pas de serveur SQL sous la main, ai pas pu tester.

 

En bref, tu calcul toi-même le rang de chaque enregistrement en fonction du timestamp.
C'est pas ce qu'il y a de plus performant, mais ça doit pas non plus être trop lent AMA.
Et il doit être possible d'en faire une vue.

Message cité 1 fois
Message édité par kao98 le 10-12-2014 à 17:49:33

---------------
Kao ..98 - Uplay (R6S) : kao98.7.62x39 - Origin (BF4, BF1) : kntkao98
Reply

Marsh Posté le 10-12-2014 à 18:59:28    

kontas a écrit :


 
C'était bien ma première première idée. Classer par TimeStamp est assez préçi, par contre je me retrouve toujours avec la même problématique, a savoir une colonne avec la position.
 
Mais je pourrais combiné plusieurs méthode, créer une colonne timestamp, puis en faisant la requête, je me base sur le timeStamp pour générer une colonne avec la position.


 
je ne comprends plus :)
tu as dans ta table , 2-3 champs  (id_table, id_user, timestp)
1, user1 , tm1
2, user2 , tm2
3, user3 , tm3
5, user5 , tm5
10, user10, tm10
ok?
 
tu tries cette table par timestamp (ou id_table)
le rang de ton user, est le select count de kao98 :
SELECT COUNT(*) FROM TABLE WHERE id_table < id_user +1 (ou avec le timestp de ton user) ou même entre deux timestamps tu peux avoir ainsi même un classement relatif sur une période
 
Ce select count... n'est stocké, mais calculé à chaque fois... tu peux soit laisser cette sous requête comme ça ou t'en faire une fonction...
 


---------------
mieux vaut être un con au chaud, qu'un con gelé lol
Reply

Marsh Posté le 16-12-2014 à 11:32:46    

kao98 a écrit :


 
Non, il ne crée pas une table TABLE ! TABLE ça représente la table que tu requête !
C'est juste une sous requête en fait. On peut imaginer ça mixer avec l'histoire du timestamp.
 

Code :
  1. CREATE TABLE user (
  2.         id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.         name VARCHAR(100),
  4.         created TIMESTAMP DEFAULT NOW()
  5.       );
  6.  
  7. INSERT INTO user (name) VALUES ('user 1');
  8. INSERT INTO user (name) VALUES ('user 2');
  9. INSERT INTO user (name) VALUES ('user 3');
  10. INSERT INTO user (name) VALUES ('user x');
  11. INSERT INTO user (name) VALUES ('user 4');
  12.  
  13. DELETE FROM user WHERE name LIKE 'user x';
  14.  
  15. SELECT
  16.  id,
  17.  name,
  18.  (SELECT COUNT(*) FROM user WHERE created < u.created ORDER BY created) +1 AS rank
  19.  
  20. FROM user u
  21. ORDER BY created;


 
NB: j'ai pas de serveur SQL sous la main, ai pas pu tester.
 
En bref, tu calcul toi-même le rang de chaque enregistrement en fonction du timestamp.
C'est pas ce qu'il y a de plus performant, mais ça doit pas non plus être trop lent AMA.
Et il doit être possible d'en faire une vue.


 
Merci pour cette explication, par contre quand j'exécute la requette, le Rank reste à 1 pour chacun des enregistrement et ne s'incrémente pas.
 
Question vitesse, ca donne un résultat calculé en 0.15sec avec une table contenant 15000 enregistrement

Reply

Marsh Posté le 16-12-2014 à 11:41:18    

Je viens d'essayer sur un serveur Oracle. J'ai dû modifier légèrement la sous-requête pour que ça fonctionne, mais le rank était calculé correctement.
 
J'ai dû supprimer le order by de la sous-requête (il ne sert à rien d'ailleurs, mea culpa) :
 

Code :
  1. SELECT
  2.  id,
  3.  name,
  4.  (SELECT COUNT(*) FROM user WHERE created < u.created) +1 AS rank
  5.  
  6. FROM user u
  7. ORDER BY created;


---------------
Kao ..98 - Uplay (R6S) : kao98.7.62x39 - Origin (BF4, BF1) : kntkao98
Reply

Marsh Posté le 16-12-2014 à 14:32:09    

Cette requête me génère toujours un rank de 1 pour toutes les lignes :/

 

Mais ca fonctionne avec ca :

 
Code :
  1. SELECT id, name, created, @curRank := @curRank +1 AS rank
  2. FROM user, (
  3. SELECT @curRank :=0 ) r ORDER BY created DESC
 

Désolé d'être si bidon, mais que pensez vous de ca, c'est une sorte de Super mix de vos idées ^^ :

 
Code :
  1. SELECT idutilisateur FROM (
  2.      SELECT    idclassement,
  3.                    idutilisateur,
  4.                    classementDate,
  5.                    @curRank := @curRank + 1 AS rank
  6.      FROM      classement p, (SELECT @curRank := 0) r
  7.      ORDER BY  classementDate DESC ) AS classementActuel
  8. WHERE classementActuel.rank BETWEEN 7 AND 49
 

Avec cela tout semble être Ok.  :D


Message édité par kontas le 16-12-2014 à 14:32:47
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed