• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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.
0
thomaszhwang
Asked:
thomaszhwang
  • 3
  • 3
3 Solutions
 
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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
 
thomaszhwangAuthor Commented:
Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now