Créer un fichier texte depuis une procédure

Bonjour,

Je sais créer un fichier à partir d'un select accompagné d'un INTO OUTFILE.
Ce que j'aimerais savoir, c'est s'il est possible de créer un fichier mais sans passer par un select.
Je m'explique.
Dans une procédure, je crée un script SQL que je sauvegarde pour le moment dans une table temporaire.
Chaque ligne de script est donc ajoutée à la table.
Je termine en exportant le contenu de la table dans un fichier texte avec Datagrip.
Mais je me dis qu'il serait plus simple de concaténer les lignes dans une variable puis d'enregistrer ce fichier.

Je précise que c'est plus une question liée à mon besoin d'en apprendre plus qu'une nécessité urgente.
Le système existant fonctionne mais bon, il y a peut-être mieux ? :)

Salut Papy214.

Créer un fichier externe n'est pas la fonctionnalité d'un SGBDR.
Si c'est pour migrer, il y a l'export qui est en réalité un "mysqldump".
Sinon, vous pouvez le faire au travers de php.

La question fondamentale est la finalité de ce fichier externe ?

@+

Bonjour,

En fait, pour donner un exemple, depuis une procédure, je lis une table ligne à ligne et je prends une valeur de champ qui représente un tableau (une chaîne de caractère avec des valeurs séparées par une virgule).
En fonction des valeurs trouvées, je fais soit une requête insert, soit un update. Et j'y ajoute selon certain cas une suppression dans une autre table.
Donc, plutôt que de jouer directement chaque requête, j'enregistre le texte de la requête dans une table temporaire.
Pour finir je fais un "select * from matabletemp" et je fais un export dans un fichier tsv pour vérifier exactement ce que le script devrait faire au final et je le passe au responsable qui le vérifie avant de l'exécuter.
Cette méthode fonctionne bien mais je me disais qu'il y avait peut-être un moyen de concaténer chaque requête dans une variable longtext et de l'enregistrer à la fin dans un fichier.
D'ailleurs, plutôt que de générer autant de lignes dans une table temporaire, je me rends compte en écrivant tout ça que je pourrais simplement exporter ma variable simple avec un select mavariable into file.
Comme quoi, en écrivant les choses on trouve souvent d'autres solutions.
Quand à PHP, je ne l'utilise pas dans ce contexte. Je ne suis pas côté programmation web :-)

Salut Papy214.

On peut utiliser php pour faire autre chose que du web.
Php est fréquemment associé à Mysql, à Phpmyadmin et à Apache, comme par exemple dans WAMP.

Je ne connais pas d'autres façons de produire un fichier ".txt" à partir d'une mise en forme des lignes issues d'une table.
Je n'utilise pas cette façon d faire, car cela nécessite de faire des manipulations, qui est source d'erreurs.

Ici, votre finalité est de venir mettre à jour vos tables. Mais pourquoi procéder ainsi ?

Vous pouvez mettre à jour une base de données à partir d'une autre base de données d'un autre serveur mysql.
Votr application va se connecter à vos deux bases de données, analyser les lignes issues de votre première base puis mettre à jour la deuxième base.
Vu que le traitement est automatique, il n'y a aucune vérification manuelle à faire.
Peut-être produire un état qui va résumer ce qui ne vous convient pas.

Ce genre de traitement est ce que l'on nomme une différence.
Si la ligne existe dans la première base de données, mais pas dans la seconde, on fait une création.
Si la ligne existe dans la première base de données, mais aussi dans la seconde, on fait une mise à jour, s'il existe des différence.
Si la ligne n'existe pas dans la première base de données, mais elle existe dans la seconde, on fait une suppression.

Je suppose que vos deux bases de données ne sont pas identiques.
Si c'était le cas, vous pourriez faire une réplication.
Dans le cas contraire, il est nécessaire de faire une interface afin de traduire ce que vous désirez faire entre vos deux bases de données.

@+

Ce n'est pas vraiment une copie ou une récupération.
Pour être plus précis, il y a 2 bases de données, une pour la prod et une de test et préparation qui est une copie de la base de prod.
A partir de fichier Excel ou autre, je dois mettre à jour des données dans diverses tables (en fait un import de données).
Sur la base de test, j'ai certains droits et certaines possibilités que je n'ai pas sur la prod.
Par exemple, créer une procédure stockée en prod, on me le refusera.
Donc, au départ, la première version de ma procédure fait réellement le travail attendu et en cas de problème, je peux "facilement" revenir en arrière .
Quand pour moi tout est correct, je fais une seconde procédure qui, au lieu de faire le travail, crée les lignes de script que je donnerai ensuite au responsable final.
Lui, vérifie en plus de moi que tout est ok et si oui, passe le script sur la base de prod à laquelle je n'ai pas accès, le tout sans créer ni procédure ni fonction.
Ca parait un peu compliqué comme ça mais je je ne suis pas le "grand manitou" qui gère tout ça :-)
Mais je ne désespère pas d'y arriver avec le temps parce que j'ai déjà beaucoup appris sur le sujet.

Merci en tous cas de tous ces conseils

Salut Papy214.

Pour ce qui est de l'importation d'un fichier excel, il existe le "load data infile".
Je préconise de charger le fichier Excel dans une table de travail et ensuite de faire les mises à jour de vos tables à partir de cette tabl de travail.
Autrement dit, vous avez toute la puissance des requêtes MySql pour réaliser vos contrôles et vos mises à jour.
Si en plus, vous faites toujours la même chose, autant créer un script mysql.

Si votre travail consiste à corriger des bugs (ben oui, ça arrive de temps en temps en prod), il st aussi possible de créer un script de vérification.
Cela peut se faire en plusieurs phases :
--> vérification avec extraction des lignes qui posent des problèmes dans une table de recyclage.
--> intervention manuelle (si c'est nécessaire) pour corriger les problèmes.
--> mise en forme dans une tables des corrections à effectuer à partir de la table de recyclage.
--> et enfin, envoie vers la prod des corrections.

Cela peut s'automatiser si ce sont toujours les mêmes problèmes que vous rencontrez.

Il n'y a rien de compliquer dans ce que vous me dites.
Il y a surtout à prévoir ce genre de traitement de recyclage des données qui ne sont pas considérés comme correcte.
Je suppose que vous faites ce genre de travail périodiquement, non ?

@+

Effectivement, c'est un travail que je fais maintenant assez souvent parce que j'ai justement "inventé" (mot un peu fort j'en conviens) une méthode telle que vous la décrivez.
Pour aller jusqu'au bout de la description du travail à effectuer, il se fait en 2 temps.
Le premier consiste à créer un fichier CSV basé sur un modèle très précis, fichier qui sera ensuite importé par une interface web.
Le second consiste à ajouter des éléments ou à faire des opérations en SQL pour ajuster certaines données, créer des relations entre certains éléments (possible par l'interface mais plus rapide en SQL).
Avant, les collègues qui faisaient ce travail faisaient ajustements et modifications de données dans Excel jusqu'à créer un fichier CSV à importer.
Mais quand j'ai voulu faire la même chose, je me suis vite rendu compte que ce mode de travail était pénible et limité. Surtout que quand le client renvoie une nouvelle version d'un fichier de données, il faut tout reprendre à 0 (ou presque).
Donc, je suis parti sur un import des fichiers excel dans une base MySQL de travail.
Puis je crée une vue dans laquelle je récupère toutes les colonnes de le table de données importées.
Sur chaque valeur, je fais des ajustements. Par exemple, si l'import final attend une valeur numérique pour indiquer une civilité et que le fichier client contient des monsieur, madame, je fais la transformation avec un if ou un case.
Après avoir fait tous ces premiers ajustements, je fais une seconde vue basée sur la première qui elle a le format attendu par l'import final dans l'interface web. Chaque champ attendu est pris dans la version transformée des données (la vue précédente).
De cette façon, j'ajuste très facilement les changements de structure éventuels d'un nouveau fichier de données.
Et je peux aussi faire de nombreuses vérifications sur les données pour remonter des problèmes éventuels (doublons non autorisés, références manquantes,
etc.)
Et pour finir, j'utilise soit une procédure stockée pour d'autres traitements quand c'est autorisé, soit je crée un script (manuel ou par code d'une proécdure) pour les ajustements finaux s'ils sont nécessaires.
Donc, oui, je profite pleinement de MySQL pour me faciliter le travail.
C'est d'ailleurs ce qui m'a permis d'en apprendre autant sur ce SGBD parce qu'avant, j'étais plutôt axé SQL Server.

Salut Papy214.

Importer un fichier excel (".csv") dans une table est très facile. Pourquoi ?
Parce que la table, qui est une table de travail est l'exacte réplique de votre fichier Excel.

A partir de là, que faire ?
Il vous faut un lien entre vos données issue d'Excel et vos tables mysql. En principe, ce lien est un identifiant.
Si ce n'est pas le cas (de l'identifiant), il vous faut autre chose, comme une adresse email, un nom+prénom qui est unique, enfin, je ne sais pas trop quoi pouvant vous servir à identifier d'une manière unique votre ligne.

Quand ce problème est résolu, il ne vous reste qu'à mettre à jour vos tables.
Il y a bien sûr des conversions à faire, comme par exemple "M." pour "monsieur", ou "Mme" pour "madame".
C'est pouquoi le "case when ... then ... else ... end" est très utile.

Pour ce qui est du recyclage, qui est en fait une table contenant le rejet des lignes de votre fichier excel, vous devez y mettre les lignes dont vous ne pouvez pas traiter.
Par exemple, conversion impossible d'un champs du fichier excel, impossible d'identifier la ligne, ...

Voici un exemple de chargement d'un fichier excel et de son introduction dans une base de données :

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 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 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465

-------------- START TRANSACTION --------------   -------------- DROP DATABASE IF EXISTS `base` --------------   -------------- CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` --------------   -------------- DROP TABLE IF EXISTS trav --------------   -------------- CREATE TABLE `trav` ( `id` integer unsigned not null auto_increment primary key, `col1` varchar(255) null, `col2` varchar(255) null, `col3` varchar(255) null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_04/fichier.txt' INTO TABLE `trav` CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 0 LINES (`col1`,`col2`,`col3`) --------------   -------------- select * from trav --------------   +----+--------+----------+--------------+ | id | col1 | col2 | col3 | +----+--------+----------+--------------+ | 1 | quatre | Anglais | four | | 2 | quatre | Allemand | vier | | 3 | quatre | Italien | quattro | | 4 | trois | anglais | three | | 5 | cinq | Allemand | fünf | | 6 | cinq | Italien | cinque | | 7 | cinq | Anglais | five | | 8 | NULL | NULL | vingt-quatre | | 9 | six | Anglais | six | | 10 | six | Allemand | sechs | | 11 | six | Italien | sei | | 12 | un | Espagnol | uno | | 13 | deux | Espagnol | dos | | 14 | trois | Espagnol | tres | | 15 | quatre | Espagnol | cuatro | | 16 | cinq | Espagnol | cinquo | | 17 | six | Espagnol | seis | +----+--------+----------+--------------+ -------------- COMMIT --------------   -------------- DROP TABLE IF EXISTS `nombre` --------------   -------------- CREATE TABLE `nombre` ( `id` integer unsigned not null auto_increment primary key, `litteral` varchar(255) not null, index `idx` (`litteral`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `nombre` (`id`,`litteral`) value (10, 'un'),(20, 'deux'),(30, 'trois') --------------   -------------- select * from `nombre` --------------   +----+----------+ | id | litteral | +----+----------+ | 20 | deux | | 30 | trois | | 10 | un | +----+----------+ -------------- insert into `nombre` (`litteral`) select distinct t.col1 from `trav` as t where not exists (select 1 from `nombre` as p where p.`litteral` = t.col1) and t.col1 is not null --------------   -------------- select * from `nombre` --------------   +----+----------+ | id | litteral | +----+----------+ | 32 | cinq | | 20 | deux | | 31 | quatre | | 33 | six | | 30 | trois | | 10 | un | +----+----------+ -------------- COMMIT --------------   -------------- DROP TABLE IF EXISTS `langue` --------------   -------------- CREATE TABLE `langue` ( `id` integer unsigned not null auto_increment primary key, `designation` varchar(255) not null, index `idx` (`designation`) ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `langue` (`id`, `designation`) value (100, 'Allemand'), (200, 'Italien'), (300, 'Anglais') --------------   -------------- select * from `langue` --------------   +-----+-------------+ | id | designation | +-----+-------------+ | 100 | Allemand | | 300 | Anglais | | 200 | Italien | +-----+-------------+ -------------- insert into `langue` (`designation`) select distinct t.col2 from `trav` as t where not exists (select 1 from `langue` as m where m.`designation` = t.col2) and t.col2 is not null --------------   -------------- select * from `langue` --------------   +-----+-------------+ | id | designation | +-----+-------------+ | 100 | Allemand | | 300 | Anglais | | 301 | Espagnol | | 200 | Italien | +-----+-------------+ -------------- COMMIT --------------   -------------- DROP TABLE IF EXISTS `traduction` --------------   -------------- CREATE TABLE `traduction` ( `id` integer unsigned not null auto_increment primary key, `nombre_id` integer unsigned not null, `langue_id` integer unsigned not null, `traduction` varchar(255) not null, unique index `idx` (`nombre_id`,`langue_id`), CONSTRAINT `FK_NOMBRE` FOREIGN KEY (`nombre_id`) REFERENCES `nombre` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_LANGUE` FOREIGN KEY (`langue_id`) REFERENCES `langue` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `traduction` (`nombre_id`,`langue_id`,`traduction`) value (10, 100, 'eins'), (20, 100, 'zwei'), (30, 100, 'drei'), (10, 200, 'uno'), (20, 200, 'due'), (30, 200, 'tre'), (10, 300, 'one'), (20, 300, 'two'), (30, 300, 'tree') --------------   -------------- select * from `traduction` --------------   +----+-----------+-----------+------------+ | id | nombre_id | langue_id | traduction | +----+-----------+-----------+------------+ | 1 | 10 | 100 | eins | | 2 | 20 | 100 | zwei | | 3 | 30 | 100 | drei | | 4 | 10 | 200 | uno | | 5 | 20 | 200 | due | | 6 | 30 | 200 | tre | | 7 | 10 | 300 | one | | 8 | 20 | 300 | two | | 9 | 30 | 300 | tree | +----+-----------+-----------+------------+ -------------- insert into `traduction` (`nombre_id`,`langue_id`,`traduction`) select p.id, m.id, t.col3 from `trav` as t   inner join `nombre` as p on p.litteral = t.col1   inner join `langue` as m on m.designation = t.col2   where t.col3 is not null   on duplicate key update traduction = values(traduction) --------------   -------------- select * from traduction --------------   +----+-----------+-----------+------------+ | id | nombre_id | langue_id | traduction | +----+-----------+-----------+------------+ | 1 | 10 | 100 | eins | | 2 | 20 | 100 | zwei | | 3 | 30 | 100 | drei | | 4 | 10 | 200 | uno | | 5 | 20 | 200 | due | | 6 | 30 | 200 | tre | | 7 | 10 | 300 | one | | 8 | 20 | 300 | two | | 9 | 30 | 300 | three | | 10 | 31 | 300 | four | | 11 | 31 | 100 | vier | | 12 | 31 | 200 | quattro | | 13 | 32 | 100 | fünf | | 14 | 32 | 200 | cinque | | 15 | 32 | 300 | five | | 16 | 33 | 300 | six | | 17 | 33 | 100 | sechs | | 18 | 33 | 200 | sei | | 19 | 10 | 301 | uno | | 20 | 20 | 301 | dos | | 21 | 30 | 301 | tres | | 22 | 31 | 301 | cuatro | | 23 | 32 | 301 | cinquo | | 24 | 33 | 301 | seis | +----+-----------+-----------+------------+ -------------- set @id:=0 --------------   -------------- update `nombre` set id=(@id:=@id+1) order by `id` --------------   -------------- describe `nombre` --------------   +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | litteral | varchar(255) | NO | MUL | NULL | | +----------+--------------+------+-----+---------+----------------+ -------------- set @id:=0 --------------   -------------- update `langue` set id=(@id:=@id+1) order by `id` --------------   -------------- describe `langue` --------------   +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | designation | varchar(255) | NO | MUL | NULL | | +-------------+--------------+------+-----+---------+----------------+ -------------- alter table `traduction` drop column `id` --------------   -------------- alter table `traduction` add column `id` integer first --------------   -------------- set @id:=0 --------------   -------------- update `traduction` set id=(@id:=@id+1) order by `langue_id`, `nombre_id` --------------   -------------- alter table `traduction` change column `id` `id` integer unsigned auto_increment primary key first --------------   -------------- describe traduction --------------   +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | nombre_id | int unsigned | NO | MUL | NULL | | | langue_id | int unsigned | NO | MUL | NULL | | | traduction | varchar(255) | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ -------------- select * from `traduction` --------------   +----+-----------+-----------+------------+ | id | nombre_id | langue_id | traduction | +----+-----------+-----------+------------+ | 1 | 1 | 1 | eins | | 2 | 2 | 1 | zwei | | 3 | 3 | 1 | drei | | 4 | 4 | 1 | vier | | 5 | 5 | 1 | fünf | | 6 | 6 | 1 | sechs | | 7 | 1 | 2 | uno | | 8 | 2 | 2 | due | | 9 | 3 | 2 | tre | | 10 | 4 | 2 | quattro | | 11 | 5 | 2 | cinque | | 12 | 6 | 2 | sei | | 13 | 1 | 3 | one | | 14 | 2 | 3 | two | | 15 | 3 | 3 | three | | 16 | 4 | 3 | four | | 17 | 5 | 3 | five | | 18 | 6 | 3 | six | | 19 | 1 | 4 | uno | | 20 | 2 | 4 | dos | | 21 | 3 | 4 | tres | | 22 | 4 | 4 | cuatro | | 23 | 5 | 4 | cinquo | | 24 | 6 | 4 | seis | +----+-----------+-----------+------------+ -------------- COMMIT --------------   -------------- DROP TABLE IF EXISTS `poubelle` --------------   -------------- CREATE TABLE `poubelle` ( `id` integer unsigned not null auto_increment primary key, `col1` varchar(255) null, `col2` varchar(255) null, `col3` varchar(255) null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED --------------   -------------- insert into `poubelle` (`col1`,`col2`,`col3`) select col1, col2, col3 from `trav`   where col1 is null or col2 is null --------------   -------------- select * from `poubelle` --------------   +----+------+------+--------------+ | id | col1 | col2 | col3 | +----+------+------+--------------+ | 1 | NULL | NULL | vingt-quatre | +----+------+------+--------------+ -------------- COMMIT --------------   -------------- drop table `trav` --------------   -------------- select p.`litteral`, m.`designation`, f.`traduction` from `traduction` as f   inner join `nombre` as p on p.`id` = f.`nombre_id`   inner join `langue` as m on m.`id` = f.`langue_id`   order by p.`id`, m.`id` --------------   +----------+-------------+------------+ | litteral | designation | traduction | +----------+-------------+------------+ | un | Allemand | eins | | un | Italien | uno | | un | Anglais | one | | un | Espagnol | uno | | deux | Allemand | zwei | | deux | Italien | due | | deux | Anglais | two | | deux | Espagnol | dos | | trois | Allemand | drei | | trois | Italien | tre | | trois | Anglais | three | | trois | Espagnol | tres | | quatre | Allemand | vier | | quatre | Italien | quattro | | quatre | Anglais | four | | quatre | Espagnol | cuatro | | cinq | Allemand | fünf | | cinq | Italien | cinque | | cinq | Anglais | five | | cinq | Espagnol | cinquo | | six | Allemand | sechs | | six | Italien | sei | | six | Anglais | six | | six | Espagnol | seis | +----------+-------------+------------+ -------------- COMMIT --------------   Appuyez sur une touche pour continuer...

---

@+