Discussion :

[xounet]

Bonjour,

Dans une requête SQL complexe, je dois réaliser les étapes suivantes :
1/extraire des données d'une table table1 pour les stocker dans une table "table_base"
2/extraire des résultats d'une requête sur la "table_base" et les stocker dans une table "table_calculs_1"
3/extraire des résultats d'une requête sur la "table_calculs_1" et les stocker dans une table "table_calculs_2"
4/Mettre à jour la table initiale table1 selon des critères qui sont regroupés dans la "table_calculs_2"

Ceci n'est visiblement pas possible dans une seule requête telle que celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
WITH 	
	table_base as (SELECT t1.n1,t1.n2,t1.n3, ....
																	FROM table1 t1, table1  t2, table1  t3 
																	WHERE ... IS NULL AND ...<.. AND ...
																	ORDER BY ... DESC ,.. ASC..),
										table_calculs_1 as (
																	SELECT max(n1) as valeur_precedente,n2 as valeur_nulle,min(n3) as valeur_suivante 
																	FROM table_interpolation_base 
																	GROUP BY n2),
										table_calculs_2 as (
																	SELECT .....
																	FROM table_calculs_1 , table_base 
																	WHERE n1=valeur_precedente AND n2=valeur_nulle AND n3=valeur_suivante)
										UPDATE table1 Q INNER JOIN table_calculs_2  T SET Q.test2=T.val ON Q.num_ligne=T.n2)

J'ai le message d'erreur :

Can't reopen table: 'table1'

Cependant je ne trouve pas vraiment de solution pour contourner ce problème.

[escartefigue]

Bonjour,

merci de communiquer le SQL complet et exact de la requête

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
table_base as 
         (SELECT t1.n1,t1.n2,t1.n3, ....
	  FROM table1 t1, table1  t2, table1  t3 
	  WHERE ... IS NULL AND ...<.. AND ...
          ORDER BY ... DESC ,.. ASC..
         )

Par ailleurs, dans l'ordre UPDATE, il ne faut pas mettre la clause SET entre les mots-clefs JOIN et ON, mais après la clause de jointure
Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
UPDATE table1 T1 
INNER JOIN table_calculs_2  TC2 
   ON TC2.num_ligne=T1.n2
SET TC2.test2=T.val

Enfin, écrire les jointures sous la forme FROM T1, T2, ..., Tn en mettant les critères de jointure dans la clause WHERE, c'est vraiment pas top

[xounet]

Bonjour, merci pour ces bons conseils.

En partant de mon problème de base j'ai simplifié la requête pour plus d'efficacité du coup voici le problème :
A partir de cette table table1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
41
42
43
44
45
46
47
48
49
50
51
--
-- Structure de la table `table1`
--
 
CREATE TABLE `table1` (
  `num_ligne` int NOT NULL,
  `date` datetime DEFAULT NULL,
  `test` decimal(10,1) DEFAULT NULL COMMENT 'ceci est un commentaire',
  `test2` int DEFAULT NULL COMMENT 'ceci est un commentaire'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
 
--
-- Déchargement des données de la table `table1`
--
 
INSERT INTO `table1` (`num_ligne`, `date`, `test`, `test2`) VALUES
(1, '2022-01-01 00:00:00', '4.0', 0),
(2, '2022-01-05 00:00:00', '4.0', 0),
(3, '2022-01-08 00:00:00', '489.0', 0),
(4, '2022-01-10 10:54:03', '-456.0', NULL),
(5, '2022-01-15 00:00:00', '7445.0', 226),
(6, '2022-01-17 00:00:00', '45.0', 325),
(7, '2022-01-19 00:00:00', '-4.0', NULL),
(8, '2022-01-20 00:00:00', '-5000.0', 164),
(9, '2022-01-22 00:00:00', '-4.0', 5),
(10, '2022-01-23 00:00:00', '78.0', NULL),
(11, '2022-01-24 00:00:00', NULL, 117),
(12, '2022-01-25 00:00:00', '7.0', 289),
(13, '2022-01-26 00:00:00', NULL, 461);
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `table1`
--
ALTER TABLE `table1`
  ADD PRIMARY KEY (`num_ligne`),
  ADD UNIQUE KEY `test num ligne` (`test`,`num_ligne`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `table1`
--
ALTER TABLE `table1`
  MODIFY `num_ligne` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;
COMMIT;

je souhaite d'abord la cloner en une table temporaire pour ensuite pouvoir travailler non pas sur la table d'origine mais sur le clone qui est en mémoire vive, donc voici le type de requête que j'essaie d'utiliser :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE TEMPORARY TABLE tt1 SELECT * FROM test.table1;
	WITH 	
	table_calculs1 as (SELECT t2.num_ligne n2,t1.test as val FROM tt1 t1 WHERE t2.test2 IS NULL AND t1.num_ligne=t2.num_ligne)                                 
UPDATE tt1 INNER JOIN table_calculs1 T  ON num_ligne=T.n2 SET test2=T.val WHERE val IS NOT NULL

Et voici le message que j'ai en retour :

MySQL a répondu : Documentation
#1137 - Impossible de réouvrir la table: 'tt1

Le problème sur lequel j'ai déjà réfléchi pas mal de fois provient me semble t il de l'utilisation de tables temporaires qui ne permettent pas d'être utilisées à plusieurs reprises dans une requêtes autrement qu'en lecture. La solution est de créer d'autres tables temporaires copies, mais table1 pouvant être très "grand" cela me semble pas adapté de le copier deux ou trois fois en mémoire vive. C'est pour cela que je cherche d'autres solutions.

[escartefigue]

Bonjour,

Les commentaires du script sont erronés, on parle de "déchargement" et de tables "déchargées" alors qu'au contraire il s'agit de chargements par insert
Ca semble un détail, mais tant qu'à commenter, autant que ça corresponde au code

Ensuite, cette déclarative de CTE est erronée, l'alias T2 ne correspondant à aucune table

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
(SELECT t2.num_ligne n2
      , t1.test         as val 
 FROM tt1 t1 
 WHERE t2.test2 IS NULL 
   AND t1.num_ligne=t2.num_ligne)

[xounet]

Merci pour ton retour.
Concernant les commentaires, ceux-ci proviennent de phpmyadmin pour la partie exportation de tables donc j'ai pas la main dessus.

Concernant la requête, je suis désolé effectivement j'ai fait une erreur de copie en simplifiant ma requête en fait j'utilise bien deux fois la table tt1, l'exemple est vraiment basique et semble un peu inutile, mais l'idée derrière cet exemple c'est de compléter les valeurs nulles d'une table par des valeurs non nulles de cette même table en procédant de cette façon (table temporaire+cte).
Voici la requête corrigée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE TEMPORARY TABLE tt1 SELECT * FROM test.table1;
	WITH 	
	table_calculs1 as (SELECT t2.num_ligne n2,t1.test as val FROM tt1 t1,tt1 t2 WHERE t2.test2 IS NULL AND t1.num_ligne=t2.num_ligne)                                 
UPDATE tt1 INNER JOIN table_calculs1 T  ON num_ligne=T.n2 SET test2=T.val WHERE val IS NOT NULL

En espérant que cela répond aux interrogations concernant le problème.

[xounet]

Visiblement rien ne permet d'utiliser les tables temporaires de cette façon c'est expliqué en partie :
https://dev.mysql.com/doc/refman/5.7...-problems.html
et passer par des CTE pour manipuler des tables temporaires n'est pas forcément la solution même si ça peut fonctionner parfois. Dommage.
En fait les CTE permettent surtout d'améliorer l'écriture de requêtes complexes mais n'apporte pas réellement de fonctionnalités nouvelles.

[SQLpro]

...
En fait les CTE permettent surtout d'améliorer l'écriture de requêtes complexes mais n'apporte pas réellement de fonctionnalités nouvelles.

Si la possibilité d'écrire des requêtes récursives...
https://sqlpro.developpez.com/cours/...te-recursives/

A +

[Artemus24]

Salut à tous.

WITH+UPDATE utilisant une même table temporaire

Rien ne vous oblige à passer par une table temporaire, ni à utilser un CTE.

Pour créer une nouvelle table déjà existente, avec MySql, il vous suffit décrire :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

create table `nouvelle_table` like `ancienne_table;

Dans une requête SQL complexe, je dois réaliser les étapes suivantes :

Est-il nécessaire de découper vos étapes en passant par des tables temporaires ?
Que vous fassiez un découpage, je n'ai rien à redire.

Mais pourquoi ne pas utiliser des view pour le faire ?
Et utiliser ces view pour remplir vos tables nouvellement créées.

Voici un exemple de découpage :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "latin1_general_ci"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`   integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `val`  tinyint unsigned NOT NULL,
  `lib`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`val`,`lib`) VALUES
  (1,'un'),  (2,'deux'),(1,'trois'),(2,'quatre'),(1,'cinq'),(2,'six')
--------------
 
--------------
select * from `test`
--------------
 
+----+-----+--------+
| id | val | lib    |
+----+-----+--------+
|  1 |   1 | un     |
|  2 |   2 | deux   |
|  3 |   1 | trois  |
|  4 |   2 | quatre |
|  5 |   1 | cinq   |
|  6 |   2 | six    |
+----+-----+--------+
--------------
drop view if exists `vue_1`
--------------
 
--------------
create view `vue_1` as
  select id,
         case when lib = 'un'     then 'one'
              when lib = 'deux'   then 'two'
              when lib = 'quatre' then 'four'
              when lib = 'cinq'   then 'five'
                                 else 'nothing' end as lib
    from `test`
   where `val` = 1
order by lib
--------------
 
--------------
select * from `vue_1`
--------------
 
+----+---------+
| id | lib     |
+----+---------+
|  5 | five    |
|  3 | nothing |
|  1 | one     |
+----+---------+
--------------
drop view if exists `vue_2`
--------------
 
--------------
create view `vue_2` as
  select id,
         case when lib = 'un'     then 'eins'
              when lib = 'deux'   then 'zwei'
              when lib = 'quatre' then 'vier'
              when lib = 'cinq'   then 'funf'
                                  else 'nichts' end as lib
    from `test`
   where `val` = 2
order by lib
--------------
 
--------------
select * from `vue_2`
--------------
 
+----+--------+
| id | lib    |
+----+--------+
|  6 | nichts |
|  4 | vier   |
|  2 | zwei   |
+----+--------+
--------------
drop view if exists `vue_3`
--------------
 
--------------
create view `vue_3`   as
         select ta.id,case when ta.val=1  then tb.lib
                           when ta.val=2  then tc.lib
                           else 'rien'    end  as lib
           from test  as ta
left outer join vue_1 as tb on tb.id = ta.id
left outer join vue_2 as tc on tc.id = ta.id
--------------
 
--------------
select * from `vue_3`
--------------
 
+----+---------+
| id | lib     |
+----+---------+
|  1 | one     |
|  2 | zwei    |
|  3 | nothing |
|  4 | vier    |
|  5 | five    |
|  6 | nichts  |
+----+---------+
--------------
update     test  as t1
inner join vue_3 as t2
        on t2.id  = t1.id
       set t1.lib = t2.lib
--------------
 
--------------
select * from `test`
--------------
 
+----+-----+---------+
| id | val | lib     |
+----+-----+---------+
|  1 |   1 | one     |
|  2 |   2 | zwei    |
|  3 |   1 | nothing |
|  4 |   2 | vier    |
|  5 |   1 | five    |
|  6 |   2 | nichts  |
+----+-----+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

La seule requête que vous devez utiliser dans vos scripts est celle du UPDATE.
Les autres se déclarent dans votre base de données au même endroit que vos tables et autres procédures stockées.
Mieux vaut externaliser vos requêtes, surtout si ce sont des view, car l'impact d'une modification sera minime dans votre application.

Cordialement.
Artemus24.
@+

[xounet]

Artemus24,

Merci pour cette réponse très complète comme à chaque fois. En tous les cas tu as vraiment bien illustré le principe que je cherche à réaliser, en utilisant les views.
Effectivement les views permettent cela sans avoir besoin de passer par des tables temporaires.
Mais dans mon cas les requêtes SQL sont générées dynamiquement donc, le nom des tables, les nom de colonnes ne sont pas "constants", à moins que cela soit possible je serai contraint de créer les views à la demande et faire une base de données de views pour éviter de créer puis d'effacer les views. C'est une solution intéressante effectivement, mais moins polyvalente que WITH.

Cependant il me semble que les tables temporaires apportent plus d'efficacité globalement :

1. le disque dur est moins sollicité lorsque l'on travaille de façon intense sur de très grosses tables
2. elles persistent tant qu'elles ne sont pas effacées
3. elles se gèrent comme des tables normales (mais ne sont pas accessibles plusieurs fois dans une même requête voir au dessus)
4. elles sont dédiées à un seul client et donc améliorent la répartition du trafic, la vitesse d'exécution pour plusieurs utilisateurs.

Je pense à une autre variante combinant tout : WITH + TEMPORARY TABLE + VIEWS ? à creuser

[Artemus24]

Salut xounet.

Merci pour cette réponse très complète comme à chaque fois. En tous les cas tu as vraiment bien illustré le principe que je cherche à réaliser, en utilisant les views.

Pas de quoi.

Effectivement les views permettent cela sans avoir besoin de passer par des tables temporaires.

Les CTE aussi. Les CTE sont utilisés quand tu veux le faire en cascade.

Mais dans mon cas les requêtes SQL sont générées dynamiquement ...

Il se peut que tu as un squelette avec des variables dynamique.
Dans ce cas, il faut voir comment fonctionne ton générateur.

C'est une solution intéressante effectivement, mais moins polyvalente que WITH.

Si la structure de tes requêtes est toujours la même, sauf sur quelques points, comme le nom d'une table ou le nom de quelques colonnes, tu peux créer une procédure stockée qui va mettre cela en forme.
Dans ton générateur de requête dynamique, tu appelles juste la procédure stockées en passant les paramètres dont tu as besoin.
Cela permet d'alléger ton générateur de requêtes dynamique. Comme tu externalises tes requêtes sous la forme d'une procédure stockées, la maintenance est plus facile à faire.

Cependant il me semble que les tables temporaires apportent plus d'efficacité globalement

Tes tables temporaires, tu les utilises comme des points de sauvegardes.
C'est pratique car tu ne refais pas à chaque fois le traitement pour les reconstituer.

Rien ne t'empêche de les rendre permanente. Il suffit de faire comme dans mon exemple.
Tu te crées un modèle de table. quand tu en a besoin d'une, tu la crées en faisant :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

create table `nouvelle_table` like `ancienne_table;

"Nouvelle_Table" devient permanente. Tu n'en as plus besoin :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

drop table if exists Nouvelle_Table;

Pour le nom, tu peux préfixer par le user si tu en possède un au moment du lancement de ton script.

Tu réparties tes traitements soit coté serveur MySql (Procédure stockée) ou soit coté application selon tes besoins.

Pour ce qui est des CTE, c'est utile mais il ne faut pas trop en abuser car c'est couteux en traitement.
Disons que tu lances la nuit un traitement qui va préparer la journée du lendemain. Ca, tu peux le faire !

Après, cela dépend de tes besoins mais aussi de ce que tu veux faire.
Ainsi que tes capacités à écrire tes requêtes et à les répartir dans tes applications.

Cordialement.
Artemus24.
@+

[xounet]

Bonjour,

Après réflexions, voici mes conclusions : l'utilisation des view me semble trop peu flexible et pas adaptée pour des requêtes en série, ni les procédures ou autre... et finalement comme with ne contourne pas le problème lié aux tables temporaires , j'ai finalement remplacé WITH en créant de nouvelles tables temporaires. L'exemple ci dessous le montre (sur la base de l'exemple inventé cité plus haut) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TEMPORARY TABLE tt1 SELECT * FROM test.table1;
CREATE TEMPORARY TABLE tt1_1 SELECT * FROM tt1 WHERE test2 IS NULL;
CREATE TEMPORARY TABLE tt1_f (SELECT t2.num_ligne n2,t1.test as val  FROM tt1 t1 INNER JOIN tt1_1 t2 ON t1.num_ligne=t2.num_ligne);
UPDATE tt1 INNER JOIN tt1_f T  ON num_ligne=T.n2 SET test2=T.val WHERE val IS NOT NULL;
SELECT * FROM tt1;

On voit sur cet exemple qui est une requête inventée pour la démo que l'on peut multiplier rapidement le nombre de tables temporaires si la requête est complexe.
La création d'une table temporaire de très petite taille ne prend que quelques millisecondes au final et il faut simplement faire attention à bien supprimer les tables temporaires qui ne servent plus au fur et à mesure.