Comment boucle correctement dans une fonction stockée sur MySQL?
Je vais avoir quelques difficultés à obtenir un assez simple procédure stockée droit.
Considérer l'article suivant tableau extrait de:
id replaced_by baseID
1 2 0
2 3 0
3 0 0
Un simple tableau hiérarchique, à l'aide de copy-on-write. Lorsqu'un article est publié, le replaced_by champ de l'actuel article est mis à l'id de la nouvelle copie.
J'ai ajouté un baseID champ, à l'avenir, doit stocker la baseID d'un article.
Dans mon exemple ci-dessus, il y a un article (par exemple l'id 3). C'est baseID serait de 1.
Pour obtenir le baseID, j'ai créé la procédure stockée suivante:
DELIMITER $$
CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
BEGIN
DECLARE x INT;
DECLARE y INT;
SET x = articleID;
sloop:LOOP
SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
IF y IS NOT NULL THEN
SET x = y;
ITERATE sloop;
ELSE
LEAVE sloop;
END IF;
END LOOP;
RETURN x;
END $$
DELIMITER ;
Il semble assez simple, jusqu'à ce que j'ai fait appel de la fonction à l'aide:
SELECT getBaseID(3);
Je l'espère, la fonction retourne 1. Je suis même prêt à comprendre, il peut prendre une tranche de seconde.
Au lieu de cela, le CPU de la machine, qui va jusqu'à 100% (mysqld).
J'ai même réécrit la même fonction à l'aide de REPEAT .. UNTIL
et avec WHILE .. DO
, avec le même résultat final.
Quelqu'un peut-il expliquer pourquoi mon CPU monte à 100% lorsqu'il entre dans la boucle?
Note de côté: j'essaie simplement de gagner du temps. J'ai créé exactement la même fonction en PHP qui effectue bien, mais notre hypothèse est que MySQL peut le faire un peu plus rapidement. Nous avons besoin de passer au crible environ 18 millions de disques. Un petit bout de temps que je peux économiser va être la peine.
Merci d'avance pour toute aide et/ou des pointeurs.
Résolu SQL:
DELIMITER $$
CREATE FUNCTION getBaseID(articleID INT) RETURNS INT
BEGIN
DECLARE x INT;
DECLARE y INT;
SET x = articleID;
sloop:LOOP
SET y = NULL;
SELECT id INTO y FROM article WHERE replaced_by_articleID = x;
IF y IS NULL THEN
LEAVE sloop;
END IF;
SET x = y;
ITERATE sloop;
END LOOP;
RETURN x;
END $$
DELIMITER ;
OriginalL'auteur djBo | 2011-08-11
Vous devez vous connecter pour publier un commentaire.
De mysql :
Si vous avez une boucle infinie lorsque aucun enregistrement trouvé avec un
x
(y
reste inchangé)Essayez
SET y = (SELECT id ....)
à la place ou ajouterSET y = null
avant votre instruction select (ça doit être la première instruction dans la boucle)OriginalL'auteur a1ex07
Il se sent comme vous peut-être manquant un index sur la replaced_by colonne. Si il y a pas d'index sur replaced_by, vous êtes à la recherche à un full table scan avec chaque itération.
Vous devez également vous assurer que la ligne existe avant l'extraction de la
Deux fois plus nombreux appels SQL, mais mieux vaut prévenir que guérir.
Lui donner un essai !!!
OriginalL'auteur RolandoMySQLDBA