concaténation de valeur dans une variable MySQL

Version imprimable

29/04/2009, 15h15
.TKrD.

concaténation de valeur dans une variable MySQL
bonjour, je suis entrain de faire une procédure et je voudrais savoir comment faire pour concaténé dans une variable, plusieurs valeurs récupérés via une boucle.

exemple en php
Code:

1 2 3 4 5 while($i<10){ $variable=$variable.$i.","; $i++; }
29/04/2009, 15h25
.TKrD.

J'ai trouvé la fonction CONCAT_WS() mais je ne sais pas comment on fait pour ajouter la valeur d'une variable en paramètre :lol:
29/04/2009, 15h55
.TKrD.
Je me demande si avec un truc du genre :
Code:

1 2 3 SELECT INTO VARIABLE CONCAT_WS(",","Premier nom",NULL,"Dernier nom");
ça ne marcherait pas...

Code:

1
2
3
4
while($i<10){
     $variable=$variable.$i.",";
     $i++;
}

Serait quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
DECLARE i INTEGER;
DECLARE strResultat VARCHAR(500);
 
SET i = 0;
SET strResultat = '';
 
WHILE (i<10) DO
    SET strResultat = CONCAT(strResultat, i, ',');
    SET i = i+1;
END WHILE;

Par contre je ne vois pas trop comment faire avec un curseur (au niveau de la condition dans la boucle), et après mes deux posts se rejoignent car je veux me servire de ce résultat dans une autre requete, du style:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
DECLARE i INTEGER;
DECLARE requete VARCHAR(50);
DECLARE nouvelle_requete VARCHAR(50050);
DECLARE autre_req VARCHAR(1000);
DECLARE strResultat VARCHAR(50000);
DECLARE curs CURSOR FOR SELECT champ FROM table WHERE condition;


SET strResultat = '';
SET requete = 'SELECT * FROM table WHERE champ NOT IN (';

OPEN curs;

WHILE ( FETCH curs INTO i ) DO //condition dont je ne suis pas sure
    SET strResultat = CONCAT(strResultat, i, ',');
END WHILE;

CLOSE curs;

SET @nouvelle_requete = CONCAT(requete,strResultat,');'); 

PREPARE nouv_req FROM @nouvelle_requete; //autre requete
EXECUTE nouv_req; //la il faudrait également que je récupère les résultat 
de cette requete dans une variable que je vais appeler @peut-etre, avec un into?

WHILE (possible de passer à l'enregistrement suivant de nouv_req) DO 

    SET @autre_requete = CONCAT('SELECT blabli FROM table2 WHERE champ = ',@peut-etre,'\'');

    PREPARE autre_req FROM @autre_requete; 
    EXECUTE autre_req; // encore peut être avec un into??

    INSERT INTO nouvelle table VALUES (toutes les valeurs récupérés);

    DEALLOCATE PREPARE nouv_req;

END WHILE

DEALLOCATE PREPARE nouv_req;

petite question au passage, j'ai remarqué que certaines variables ont un @ et d'autres non, est-ce que c'est pour utiliser la valeur de celle-ci?

30/04/2009, 11h33
.TKrD.

En faite, si je mets les "INTO" dans la concaténation de ma requete avant de faire le "PREPARE", ça devrait marcher?

mais après je ne vois pas encore comment parcourir mes résultats avec le "EXECUTE" (au niveau syntaxique) à moins que l'on puisse appeler la requete "préparé" avec FETCH?

Je ne pense pas que ça va marcher car :
- on ne peut pas définir un curseur sur une requête dynamique
- je ne crois pas que l'on puisse récupérer le résultat du EXECUTE pour parcourir le curseur en résultant

Vous allez vite voir que le langage procédural de MySQL est NETTEMENT moins riche que le PL/SQL :(. PostgreSQL est de ce côté-là, avec le PL/PgSQL nettement meilleur, parfois même plus riche que le PL/SQL (notamment pour les tableaux).

Par contre j'ai l'impression que vous vous compliquez sacrément la vie et qu'un simple INSERT suffit :?

Quelque chose du genre :
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO nouvelle_table(...) SELECT ... FROM table2 t2 WHERE NOT EXISTS( SELECT NULL FROM table1 t1 WHERE t1.id_t2 = t2.id_t2 -- condition de jointure AND ... -- condition sur les enregistrements que l'on ne veut pas );

30/04/2009, 13h28
.TKrD.

[QUOTE=rbaraer;4260159][QUOTE]

En faite, c'est que j'ai environ 12000 enregistrements et qu'avec un NOT IN (avec une requete), ca prends vraiment beaucoup de temps (plus d'1/2 heure en tout cas) alors qu'avec un NOT IN (avec des chiffres) ça prend moins de 3 minutes (ce qui explique mon choix).

Maintenant je me demande si avec 1 prodécure stocké et 3 fonctions, ça ne pourait pas réglé mon problème de lecture de résultats?

(J'insiste car pour l'instant, ma page php met entre 3 et 4 heures pour s'executer)

Citation:

Envoyé par rbaraer

Je ne pense pas que ça va marcher car :
- on ne peut pas définir un curseur sur une requête dynamique
- je ne crois pas que l'on puisse récupérer le résultat du EXECUTE pour parcourir le curseur en résultant

On ne peut donc avoir qu'un seul résultat avec un EXECUTE?
Citation:
Envoyé par rbaraer
Par contre j'ai l'impression que vous vous compliquez sacrément la vie et qu'un simple INSERT suffit :?

Quelque chose du genre :
Code:

1 2 3 4 5 6 7 8 9 INSERT INTO nouvelle_table(...) SELECT ... FROM table2 t2 WHERE NOT EXISTS( SELECT NULL FROM table1 t1 WHERE t1.id_t2 = t2.id_t2 -- condition de jointure AND ... -- condition sur les enregistrements que l'on ne veut pas );
En faite, c'est que j'ai environ 12000 enregistrements et qu'avec un NOT IN (avec une requete), ca prends vraiment beaucoup de temps (plus d'1/2 heure en tout cas) alors qu'avec un NOT IN (avec des chiffres) ça prend moins de 3 minute, ce qui explique mon choix.

Maintenant je me demande si avec 1 prodécure stocké et 3 fonctions, ça ne pourait pas réglé mon problème de lecture de résultats? (si la réponse à la première question est "oui")

ou sinon inserer mes résultas dans une table puis aller les consulter par la suite...

J'insiste car pour l'instant, ma page php met entre 3 et 4 heures pour s'executer

30/04/2009, 14h40
rbaraer

Citation:

Envoyé par .TKrD.

En faite, c'est que j'ai environ 12000 enregistrements et qu'avec un NOT IN (avec une requete), ca prends vraiment beaucoup de temps (plus d'1/2 heure en tout cas) alors qu'avec un NOT IN (avec des chiffres) ça prend moins de 3 minute, ce qui explique mon choix.

Pouvez-vous ajouter des indexes ? Il doit certainement en manquer. Le plus simple et le plus performant sera la bonne requête SQL optimisée + des indexes.

12000 enregistrements est relativement peu : 30 minutes est énorme pour si peu d'enregistrements. Pouvez-vous poster votre requête SQL qui met 30 minutes, ainsi que les tables en jeu ?

Citation:

Envoyé par rbaraer

Pouvez-vous ajouter des indexes ? Il doit certainement en manquer. Le plus simple et le plus performant sera la bonne requête SQL optimisée + des indexes.

12000 enregistrements est relativement peu : 30 minutes est énorme pour si peu d'enregistrements. Pouvez-vous poster votre requête SQL qui met 30 minutes, ainsi que les tables en jeu ?

Alors, ça va être un peu compliqué car c'est une application que je ne domine pas mais je vais quand même essayer.

Je préviens d'avance que je ne peut pas modifier la base de données (ce qui serait le mieux dans ces cas la car elle est vraiment mal faite ...).

ma requete qui est normalement dans du code PHP, je la met en mode NOT IN (requete) pour mieux la comprendre:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
 
SELECT societe.CODE_SOCIETE, societe.NOM_COMMERCIAL_SOCIETE, societe.TELEPHONE_SOCIETE, code_postal.VILLE, code_postal.LIBELLE_DEPARTEMENT, societe_type.CODE_TYPE_SOCIETE, personnel.NOM_PERSONNEL, personnel.PRENOM_PERSONNEL, chiffre_affaire.EFFECTIF_SOCIETE 
FROM societe 
LEFT JOIN code_postal ON societe.CODE_CP = code_postal.CODE_CP 
LEFT JOIN chiffre_affaire ON societe.CODE_CA = chiffre_affaire.CODE_CA 
LEFT JOIN societe_type ON societe.CODE_SOCIETE = societe_type.CODE_SOCIETE 
LEFT JOIN responsable_compte_soc ON societe.CODE_SOCIETE = responsable_compte_soc.CODE_SOCIETE 
LEFT JOIN personnel ON responsable_compte_soc.CODE_PERSONNEL = personnel.CODE_PERSONNEL  
WHERE DATE_FIN_SOC_RC IS NULL 
AND societe.CODE_SOCIETE NOT IN (
 
SELECT DISTINCT CODE_SOCIETE
FROM horaire 
INNER JOIN horaire_tache ON horaire.CODE_HORAIRE_TACHE = horaire_tache.CODE_HORAIRE_TACHE 
INNER JOIN tache ON horaire_tache.CODE_TACHE = tache.CODE_TACHE 
INNER JOIN tache_proj_article ON tache.CODE_TACHE_PROJET_ARTICLE = tache_proj_article.CODE_TACHE_PROJET_ARTICLE 
WHERE tache_proj_article.CODE_PROJET = '5878' 
AND DATE_DEBUT_TACHE >= '".$_POST['annee']."-".$_POST['mois']."-".$_POST['jour']."' 
AND (UPPER( libelle_tache ) RLIKE '(.*)R(D)?V(.*)' OR UPPER( sujet_tache ) RLIKE '(.*)R(D)?V(.*)')
ORDER BY code_societe
 
)

au niveau de la jonction des tables, elles sont dans la requete

et ensuite, avec chaque résultat (environ 12000 au total), il faut que je relance ces deux requetes:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT tache.DATE_DEBUT_TACHE, tache.DATE_FIN_TACHE, personnel.NOM_PERSONNEL, personnel.PRENOM_PERSONNEL, personnel.TELEPHONE_INTERNE_PERSONNEL, interlocuteur.NOM_INTERLOCUTEUR, interlocuteur.PRENOM_INTERLOCUTEUR, interlocuteur.TELEPHONE_PERSO_INTERLOCUTEUR 
FROM horaire 
LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache 
LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
LEFT JOIN personnel ON horaire_tache.CODE_PERSONNEL = personnel.CODE_PERSONNEL 
LEFT JOIN interlocuteur ON horaire.CODE_INTERLOCUTEUR = interlocuteur.CODE_INTERLOCUTEUR 
WHERE (UPPER(libelle_tache) like '%RDV%' OR UPPER(sujet_tache) like '%RDV%' ) 
AND horaire.code_societe = '".$tableauSocieteSansRDV[$j]['CODE_SOCIETE']."' 
AND date_debut_tache = ( 
 
SELECT MAX(DATE_DEBUT_TACHE) 
FROM horaire 
LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache 
LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
WHERE (UPPER(libelle_tache) like '%RDV%' OR UPPER(sujet_tache) like '%RDV%')  
AND horaire.code_societe = '".$tableauSocieteSansRDV[$j]['CODE_SOCIETE']."';
 
)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT tache.DATE_DEBUT_TACHE, tache.DATE_FIN_TACHE, personnel.NOM_PERSONNEL, personnel.PRENOM_PERSONNEL, personnel.TELEPHONE_INTERNE_PERSONNEL, interlocuteur.NOM_INTERLOCUTEUR, interlocuteur.PRENOM_INTERLOCUTEUR, interlocuteur.TELEPHONE_PERSO_INTERLOCUTEUR 
FROM horaire 
LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache 
LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
LEFT JOIN personnel ON horaire_tache.CODE_PERSONNEL = personnel.CODE_PERSONNEL 
LEFT JOIN interlocuteur ON horaire.CODE_INTERLOCUTEUR = interlocuteur.CODE_INTERLOCUTEUR 
LEFT JOIN tache_proj_article ON tache.CODE_TACHE_PROJET_ARTICLE = tache_proj_article.CODE_TACHE_PROJET_ARTICLE 
WHERE tache_proj_article.CODE_PROJET = '5878' 
AND (UPPER(libelle_tache) like '%PHON%' OR UPPER(libelle_tache) like '%APPEL%') 
AND horaire.code_societe = '".$tableauSocieteSansRDV[$j]['CODE_SOCIETE']."' 
AND date_debut_tache = ( 
 
SELECT MAX(DATE_DEBUT_TACHE) 
FROM horaire 
LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache
LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
LEFT JOIN tache_proj_article ON tache.CODE_TACHE_PROJET_ARTICLE = tache_proj_article.CODE_TACHE_PROJET_ARTICLE 
WHERE tache_proj_article.CODE_PROJET = '5878' 
AND (UPPER(libelle_tache) like '%PHON%' OR UPPER(libelle_tache) like '%APPEL%') 
AND horaire.code_societe = '".$tableauSocieteSansRDV[$j]['CODE_SOCIETE'].";
 
)

et c'est ces requetes qui mettent 3/4 heures.

OK, donc vous lancez la 1ère requête une fois, puis bouclez sur les 12000 résultats pour lancer à chaque fois les 2 autres requêtes, soit environ 24000 requêtes, c'est bien ça ?

Là je comprends mieux les temps d'exécution ;) ... mais on peut certainement améliorer ça.

D'abord, les LEFT JOIN : sont-ils tous nécessaires ? Ne peut-on pas en remplacer certains par un JOIN ? Le LEFT JOIN est nécessaire si le code utilisé dans la jointure peut être NULL, mais il est beaucoup moins performant qu'un JOIN (ou INNER JOIN, c'est la même chose).

Ensuite, essayez ça pour la 1ère requête (le but étant de faire une requête pour toutes les sociétés, plutôt qu'une par société) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
SELECT societe.code_societe, tache.DATE_DEBUT_TACHE, tache.DATE_FIN_TACHE, personnel.NOM_PERSONNEL, personnel.PRENOM_PERSONNEL, personnel.TELEPHONE_INTERNE_PERSONNEL, interlocuteur.NOM_INTERLOCUTEUR, interlocuteur.PRENOM_INTERLOCUTEUR, interlocuteur.TELEPHONE_PERSO_INTERLOCUTEUR 
FROM horaire H1
INNER JOIN societe ON societe.code_societe = horaire.code_societe
LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache 
LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
LEFT JOIN personnel ON horaire_tache.CODE_PERSONNEL = personnel.CODE_PERSONNEL 
LEFT JOIN interlocuteur ON horaire.CODE_INTERLOCUTEUR = interlocuteur.CODE_INTERLOCUTEUR 
WHERE (UPPER(libelle_tache) LIKE '%RDV%' OR UPPER(sujet_tache) LIKE '%RDV%' ) 
AND societe.DATE_FIN_SOC_RC IS NULL -- J'espère que c'est la bonne table 
AND horaire.CODE_SOCIETE NOT IN (
    SELECT CODE_SOCIETE
    FROM horaire 
    INNER JOIN horaire_tache ON horaire.CODE_HORAIRE_TACHE = horaire_tache.CODE_HORAIRE_TACHE 
    INNER JOIN tache ON horaire_tache.CODE_TACHE = tache.CODE_TACHE 
    INNER JOIN tache_proj_article ON tache.CODE_TACHE_PROJET_ARTICLE = tache_proj_article.CODE_TACHE_PROJET_ARTICLE 
    WHERE tache_proj_article.CODE_PROJET = '5878' 
    AND DATE_DEBUT_TACHE >= '".$_POST['annee']."-".$_POST['mois']."-".$_POST['jour']."' 
    AND (UPPER( libelle_tache ) RLIKE '(.*)R(D)?V(.*)' OR UPPER( sujet_tache ) RLIKE '(.*)R(D)?V(.*)')
)
AND date_debut_tache = ( 
    SELECT MAX(DATE_DEBUT_TACHE) 
    FROM horaire 
    LEFT JOIN horaire_tache ON horaire.code_horaire_tache = horaire_tache.code_horaire_tache 
    LEFT JOIN tache ON horaire_tache.code_tache = tache.code_tache 
    WHERE (UPPER(libelle_tache) LIKE '%RDV%' OR UPPER(sujet_tache) LIKE '%RDV%')  
    AND horaire.code_societe = H1.code_societe;
)

Ca donne quoi ?

04/05/2009, 00h37
Sivrît
Citation:

Envoyé par .TKrD.

En faite, c'est que j'ai environ 12000 enregistrements et qu'avec un NOT IN (avec une requete), ca prends vraiment beaucoup de temps (plus d'1/2 heure en tout cas) alors qu'avec un NOT IN (avec des chiffres) ça prend moins de 3 minutes (ce qui explique mon choix).

Maintenant je me demande si avec 1 prodécure stocké et 3 fonctions, ça ne pourait pas réglé mon problème de lecture de résultats?

(J'insiste car pour l'instant, ma page php met entre 3 et 4 heures pour s'executer)

Curieux, les deux versions devraient pourtant se tenir (du moment que la sous requête ne prend pas trop de temps à elle toute seule). Il faudrait peut-être essayer en donnant bien des alias à toutes les tables pour être certain que la sous requête n'est pas corrélée, ou bien transformer le "NOT IN" en "LEFT JOIN".

Sinon, 3 minutes pour le résultat initial (qui provient d'une requête relativement simple) c'est relativement long. Pas colossal pour un résultat aussi gros, mais quand même... il ne manquerait pas des index ?

Après, itérer sur le résultat d'une requête pour générer encore plus de requêtes est un "code smell" : Ce n'est pas forcément mauvais, mais ça ne sent pas bon et indique généralement qu'il faudrait utiliser une jointure. Et là, je rejoins rbaraer, 24001 requêtes pour une page ce n'est pas décent.
Citation:
Envoyé par .TKrD.

Je me demande si avec un truc du genre :

Code:

1 2 3 SELECT INTO VARIABLE CONCAT_WS(",","Premier nom",NULL,"Dernier nom");

ça ne marcherait pas...
La syntaxe à utiliser aurait je pense été dans le genre :
Code:

1 2 3 4 5 SELECT CONCAT_WS(",",champ) INTO ma_variable FROM matable WHERE condition