Menu
I have a mySQL table which contains html code and some other information.
One example like this:
SELECT id, lang, html
FROM mytable t
WHERE type = 10
give the result:
id lang html
-------------------------------------------------------
20 fr '<html> ... html code here </html>'
21 de '<html> ... html code here </html>'
22 it '<html> ... html code here </html>'
23 en '<html> ... html code here </html>'
and my idea is to export the html code into one file per lines like
20_fr.html will contains the html columns for id 2021_de.html will contains the html columns for id 21Is it possible to do so in SQL language only like this example for exporting all rows into one file.
I know I can do it with Java or any other language but I am interested only by a SQL (a stored procedure could be ok).
734
You can use the SELECT statement with the INTO and OUTFILE clauses.
See: http://dev.mysql.com/doc/refman/5.7/en/select-into.html
SELECT html
FROM mytable
WHERE lang = 'fr'
INTO OUTFILE 'frdata.txt'
The following SQL query might be used to generate one file output statement per row in the table. Note the use of the CONCAT function to build a new SQL SELECT INTO OUTFILE command per row.
SELECT CONCAT( 'SELECT html from mytable where lang=''', lang, '''', ' INTO OUTFILE ''', CAST(id AS CHAR), '_', lang, '.html'';')
FROM mytable
ORDER BY id;
This will yield the the statements:
SELECT html from mytable where lang='fr' INTO OUTFILE '20_fr.html';
SELECT html from mytable where lang='de' INTO OUTFILE '21_de.html';
SELECT html from mytable where lang='it' INTO OUTFILE '22_it.html';
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
