Solved

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

Posted on 2012-04-06
6
233 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:thomaszhwang
  • 3
  • 3
6 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
Comment Utility
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
 

Author Comment

by:thomaszhwang
Comment Utility
Actually I hope to overwrite this file, possible?
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:thomaszhwang
Comment Utility
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:thomaszhwang
Comment Utility
Thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Creating and Managing Databases with phpMyAdmin in cPanel.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now