Supprimer les doublons reatif d'une BDD MySQL

[Spark]

Bonjour à tous, cela faisais longtemps que je ne m'étais pas tourné vers vous

Dans un premier temps la structure :

CREATE TABLE `droplist` (
  `Id` int(11) NOT NULL auto_increment,
  `mobId` int(11) NOT NULL default '0',
  `itemId` int(11) NOT NULL default '0',
  `min` int(11) NOT NULL default '0',
  `max` int(11) NOT NULL default '0',
  `chance` float NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=182181 ;

Il m'est trop difficile de vous fournir des données réelle car je ne parviendrais pas à obtenir des doublons avec un volume de lignes raisonnable, je vais donc en fabriquer.

INSERT INTO `droplist` VALUES ('26', '210369', '167000231', '1', '1', '0.7');
INSERT INTO `droplist` VALUES ('28', '210369', '167000231', '1', '1', '0.7');
INSERT INTO `droplist` VALUES ('30', '210369', '167000231', '1', '1000', '0.12');
INSERT INTO `droplist` VALUES ('31', '210369', '167000231', '1', '1', '0.12');
INSERT INTO `droplist` VALUES ('32', '210369', '114100580', '1', '1', '0.12');
INSERT INTO `droplist` VALUES ('33', '210369', '114100590', '1', '1', '0.12');
INSERT INTO `droplist` VALUES ('34', '210369', '100600329', '1', '1', '1.18');
INSERT INTO `droplist` VALUES ('41', '210369', '182004813', '1', '1', '41.18');
INSERT INTO `droplist` VALUES ('42', '210732', '100100293', '1', '1', '1.45');
INSERT INTO `droplist` VALUES ('43', '210732', '100100293', '10', '100', '1.45');
INSERT INTO `droplist` VALUES ('44', '210732', '100100293', '1', '100', '1.45');
INSERT INTO `droplist` VALUES ('45', '210732', '167000229', '1', '1', '4.35');
INSERT INTO `droplist` VALUES ('46', '210732', '167000226', '1', '1', '1.45');
INSERT INTO `droplist` VALUES ('47', '210732', '167000231', '1', '1', '1');
INSERT INTO `droplist` VALUES ('48', '210732', '167000226', '1', '1', '1.45');
INSERT INTO `droplist` VALUES ('49', '210732', '169000003', '1', '1', '12.5');

Qu'est-ce que je considère comme étant un doublon ?

Lorsque mobId et itemId on la même valeur d'une ligne à l'autre.

Les autres colonnes peuvent être un facteur de préférence dans le filtrage :

Conserver la plus grosse valeur de 'chance', et la plus grosse valeur de 'max' en cas d'égalité de 'chance', la plus grosse valeur de 'min' en cas d'égalité de 'max', la plus petite valeur de id en cas d'égalité de 'min'. Mais ceci n'est pas un besoin absolut, si c'est trop dur l'id sera le seul critère.

(voir les requêtes plus bas que j'aurais aimé tenter, à supposé qu'elles soient juste).

Mon petit parcourt avant de me tourner vers vous :

J'ai trouvé et lu attentivement ce tuto

Les doublons : détection et éradication....

Cependant le chapitre 3. Doublons relatifs pause problème. Une erreur de syntaxe est détecté dès le premier WHERE.

L'alias semble poser problème (?).

J'ai consulté la documentation , et j'ai trouver ceci :

Note : en MySQL 4.0, vous devez utiliser le véritable nom de table. En MySQL 4.1, vous devez utiliser l'alias éventuel, lorsque vous nommez la table :

En MySQL 4.0 :

DELETE test FROM test AS t1, test2 WHERE ...

En MySQL 4.1 :

DELETE t1 FROM test AS t1, test2 WHERE ...

J'utilise MySQL (5). Est-ce la raison du blocage ?

J'ai tenté les deux syntaxes sans succès :

#1109 - Unknown table 't_doublon' in MULTI DELETE

FROM T_DOUBLON T
WHERE T.NUM < ANY (SELECT NUM
FROM T_DOUBLON T2
WHERE T.NUM <> T2.NUM
AND T.NOM = T2.NOM
AND T.PRENOM = T2.PRENOM)

DELETE T_DOUBLON

#1093 - You can't specify target table 'T' for update in FROM clause

FROM T_DOUBLON T
WHERE T.NUM < ANY (SELECT NUM
FROM T_DOUBLON T2
WHERE T.NUM <> T2.NUM
AND T.NOM = T2.NOM
AND T.PRENOM = T2.PRENOM)

DELETE T

Je trouve la documentation très bizarre à ce sujet, le nom de la table n'est pas sencé être spécifié (?). Je ne sais plus quoi faire, cette méthode est-elle vraiment possible ?

Donc vous voyez je n'ai pas chômé mais je n'y arrive pas.

Pour en venir au cœur du sujet, voici ce que j'espérais faire si j'avais pu aller plus loin, 4 requêtes successives pour supprimer ce que je considère comme des doublons. Des égalités subsistes avec les 3 premières requêtes, ces égalités je les ai ajouté à mesure dans les condition pour que vous les voyez bien, c'est la 4ème qui fini le filtrage avec l'id.

Je ne sais pas encore si ma façon de procéder est bonne vu que je ne parviens même pas à utiliser l'exemple du tuto. J'ai voulu me tourner vers une table temporaire mais là je suis encore plus perdu.

Note : j'ai mis IN mais j'aurais aussi testé =

DELETE 
FROM droplist AS D
WHERE D.id IN (SELECT id
                FROM droplist AS D2
                WHERE D.mobId  = D2.mobId
                AND   D.itemId = D2.itemId
                AND   D.chance < D2.chance);

DELETE 
FROM droplist AS D
WHERE D.id IN (SELECT id
                FROM droplist AS D2
                WHERE D.mobId  = D2.mobId
                AND   D.itemId = D2.itemId
                AND   D.chance = D2.chance
                AND   D.max < D2.max);  

DELETE 
FROM droplist AS D
WHERE D.id IN (SELECT id
                FROM droplist AS D2
                WHERE D.mobId  = D2.mobId
                AND   D.itemId = D2.itemId
                AND   D.chance = D2.chance
                AND   D.max = D2.max
                AND   D.min < D2.min);  

DELETE 
FROM droplist AS D
WHERE D.id IN (SELECT id
                FROM droplist AS D2
                WHERE D.mobId  = D2.mobId
                AND   D.itemId = D2.itemId
                AND   D.chance = D2.chance
                AND   D.max = D2.max
                AND   D.min = D2.min
                AND   D.Id > D2.Id);

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE D.id IN (SELECT id

FROM droplist AS D2

W' at line 3

Modifié 11 Août 2010 par Spark

[Sarc]

Bonjour,

Tu veux supprimer les doublons une fois de temps en temps via un cron ou un script manuel, une unique fois, ou à chaque fois que tu écris une nouvelle ligne ?

Parce que la réponse ne sera pas la même.

Je pense que ta sous-requête doit être totalement indépendante de ta requête principale, et pouvoir fonctionner seule. Ce n'est pas le cas dans ton exemple. Alors comment faire ?

Généralement un truc du genre :

DELETE FROM matable WHERE matable.id IN (SELECT id FROM matable m1, matable m2 WHERE (m1.champ = m2.champ) AND (m1.id < m2.id))

Le m1.id<m2.id permet de n'en supprimer qu'une seule des deux, mais ça peut être une autre condition qui en sauvera une des deux.

Essaye de fouiller dans ce coin, et dis-nous si ça marche

[Spark]

Bonsoir sarc et merci pour ton aide.

C'est une opération à ne faire qu'une fois pour la nettoyer, donc les solutions un peu cracrad ne sont exclus.

J'ai essayé ce que tu m'a proposé mais, après avoir levé l'ambiguïté sur id, j'ai la même erreur :

#1093 - You can't specify target table 'droplist' for update in FROM clause

edit : là je reprend le principe de ta requête mais en deux temps, avec la sous requête je génère un fichier sql pour supprimer les doublons, puis j'importerais le fichier. J'ai testé sur un échantillon et ca fonctionne, mais sur la table complète ca charge depuis 2 heures, je pense que c'est mort ' Il y a 116 312 enregistrements dans la table et il n'y a rien d'écrit dans le fichier qui devait être généré. Même avec un LIMIT 0, 25 le chargement dur indéfiniment.

edit2 : en indexant la table complète ca fonctionne mieux >_>

Modifié 12 Août 2010 par Spark

[Sarc]

Donc, ça a marché comme tu souhaitais ?

Oui, c'était un peu à ça que je pensais comme solution cracra. Générer la table de tous les résultats de doublons, et mouliner dessus pour les supprimer.

Content que ça ait fonctionné !

[Spark]

Oui merci , ca c'était la partie facile