Is it possible to use a MySQL Routine to export a query result into a file?

I want to use MySQL Routine to export the result of a query into a file on Linux.  I'm just wondering if this is possible?  Thanks.

The reason why I want to use MySQL Routine is because I have already had a routine running periodically to performance a data transformation.  I just want this export as part of this process.
thomaszhwangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johanntagleCommented:
Why not just do a "SELECT ..... INTO OUTFILE '/path/to/filename' FROM ... " from within the procedure?  Just make sure the file doesn't exist yet (generate a new filename every time) and the directory is writable by the mysql.
0
thomaszhwangAuthor Commented:
Actually I hope to overwrite this file, possible?
0
johanntagleCommented:
No it's not possible.  It was made that way to prevent accidental overwriting of important files such as /etc/passwd.  Maybe you can call your routine from a shell script that deletes the existing file first. (guess this means you won't be using the event scheduler but a cronjob)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

thomaszhwangAuthor Commented:
I suppose I can use a variable for the file name, right?

I tried but it looks like it doesn't like variables...  Any idea?
0
johanntagleCommented:
Unfortunately I've tried different things you're right it doesn't seem to accept variables.  But I found a workaround.  Here I generate the filename based on the current time:

mysql> select * from test;
+-----+--------+---------------------+
| id  | data   | last_updated        |
+-----+--------+---------------------+
|  20 | second | 0000-00-00 00:00:00 |
|  30 | third  | 0000-00-00 00:00:00 |
|  40 | first  | 0000-00-00 00:00:00 |
|  50 | second | 0000-00-00 00:00:00 |
|  60 | third  | 0000-00-00 00:00:00 |
| 100 | bleh   | 2012-03-19 10:59:24 |
+-----+--------+---------------------+
6 rows in set (0.00 sec)

mysql> delimiter |
mysql> create procedure test_outfile()
    -> begin
    -> SET @sql_text = 
    ->    CONCAT ('SELECT * into outfile \'/tmp/', DATE_FORMAT( NOW(), '%Y%m%d%H%i%s'),  '.txt\' from test');
    -> 
    -> PREPARE s1 FROM @sql_text;
    -> EXECUTE s1;
    -> DROP PREPARE s1;
    -> end;|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select now(); call test_outfile();
+---------------------+
| now()               |
+---------------------+
| 2012-04-08 10:36:31 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select now(); call test_outfile();
+---------------------+
| now()               |
+---------------------+
| 2012-04-08 10:36:46 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Open in new window


I included a "select now()" above to check against the files that got created:

$ cd /tmp
$ ls -al *.txt
-rw-rw-rw- 1 mysql mysql 176 2012-04-08 10:36 20120408103631.txt
-rw-rw-rw- 1 mysql mysql 176 2012-04-08 10:36 20120408103646.txt
$ cat 20120408103631.txt
20	second	0000-00-00 00:00:00
30	third	0000-00-00 00:00:00
40	first	0000-00-00 00:00:00
50	second	0000-00-00 00:00:00
60	third	0000-00-00 00:00:00
100	bleh	2012-03-19 10:59:24

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomaszhwangAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.