Export mysql rows into different files

Question

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 20
21_de.html will contains the html columns for id 21
etc...

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

Jeremy · Accepted Answer · 2014-04-30T16:07:21.420

13

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

...

edited Apr 30 '14 at 16:07

answered Apr 30 '14 at 14:02

Jeremy

575
4
7

1

The OP asked how to export every line in a different file, this will export everything in the same one – Serpiton Apr 30 '14 at 15:09
The WHERE clause will allow this requirement to be met. In the example above only the 'fr' row will be selected and inserted into the output file. – Jeremy Apr 30 '14 at 15:46
I've amended my response to include a SQL statement to generate multiple OUTFILE commands. – Jeremy Apr 30 '14 at 16:07
But where the files are going to be saved? I tried this but cannot find it on the disk. – рüффп May 05 '14 at 13:31
@ruffp: To make the files easy to find I recommend using the full path in the OUTFILE clause. See: http://stackoverflow.com/questions/11484471/cant-find-the-file-created-by-outfile-in-mysql – Jeremy May 05 '14 at 14:11
Yes but the mysqld process can only write into /tmp. Anyway by following your link I found the files under /var/lib... after I can always make a system command (I am under linux) to move the sql and html file in the right folder. – рüффп May 05 '14 at 14:26
Another funny thing is: writing e.g to `/home/myuser/dump.sql` is not allowed but from a sql script containing the system command I can execute: `system mkdir /home/myuser/result` and `system mv /var/lib/mysql/schema/dump.sql /home/myuser/result` – рüффп May 05 '14 at 14:28
@ruffp: You might consider asking a question on the DBA stackexchange site to address the question of where the MySQL server can write files and the permissions/configuration needed to support writing files to a different location. If my answer is helpful please mark it as accepted. – Jeremy May 05 '14 at 16:52
For information, I asked [that question](http://dba.stackexchange.com/q/64809/16243) in the [DBA StackExchange Site](http://dba.stackexchange.com) but no real answer yet for the real reasons. – рüффп Mar 29 '17 at 21:17

Export mysql rows into different files

1 Answers1