mysql stored procedure with INTO OUTFILE

Question

I have a mysql stored procedure where I want to do two things 1. query a table and return the result as normal result set. 2. iterate through the result set and create a formatted text file from the procedure itself.

I looked at INTO OUTFILE, but it seems INTO OUTFILE writes the result raw to the specified file and also if we use INTO OUTFILE resultset will be empty. Seems we can't have both.

Here is my sample SP

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getdeals`()
BEGIN

select * INTO OUTFILE '/Users/tuser/sql/out.txt' from deals;

END

Any thoughts ? Thanks Prem

Answer 1

Assuming (for the sake of the example) that your deals table looks like

---------------------------
| id | deal_date  | deal  |
---------------------------
| 1  | 2014-03-10 | Deal1 |
| 2  | 2014-03-11 | Deal2 |
| 3  | 2014-03-12 | Deal3 |
---------------------------

Now your procedure code might look

DELIMITER //
CREATE PROCEDURE get_deals()
BEGIN
    -- create a temporary table and fill it with the desired subset of data
    -- Apply WHERE and ORDER BY as needed
    DROP TEMPORARY TABLE IF EXISTS tmp_deals;
    CREATE TEMPORARY TABLE tmp_deals 
    SELECT id, deal_date, deal -- explicitly specify real column names here. Don't use SELECT *. It's a bad practice.
      FROM deals
     ORDER BY id DESC;

    -- write the resultset to the file
    SELECT * 
      INTO OUTFILE '/path/to/deals.txt'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
      FROM tmp_deals;

    -- return the resultset to the client
    SELECT * FROM tmp_deals; 
END//
DELIMITER ;

After executing it:

CALL get_deals();

On the client you'll get:

---------------------------
| id | deal_date  | deal  |
---------------------------
| 3  | 2014-03-12 | Deal3 |
| 2  | 2014-03-11 | Deal2 |
| 1  | 2014-03-10 | Deal1 |
---------------------------

And the file contents will be:

3,"2014-03-12","Deal3"
2,"2014-03-11","Deal2"
1,"2014-03-10","Deal1"

Note: when using OUTFILE MySQL requires that the file should be created afresh. If you leave the file in the output directory then on the subsequent procedure call you'll get the following error

File '/path/to/deals.txt' already exists

One way to workaround it is by appending a timestamp to the filename either within the procedure itself or by passing a value through a parameter.