CODEWITHSUNDEEP
mysqlsqlfileexport
рüффп
рüффп

Reputation: 5448

Export mysql rows into different files

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

Is 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).

Upvotes: 5

Views: 5795

Answers (1)

Jeremy
Jeremy

Reputation: 615

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';

...

Upvotes: 13

Related Questions