使用INTO OUTFILE的mysql存储过程 [英] mysql stored procedure with INTO OUTFILE

问题描述

我有一个mysql存储过程，我想在其中做两件事 1.查询表并返回结果作为普通结果集. 2.遍历结果集并从过程本身创建格式化的文本文件.

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.

我查看了INTO OUTFILE，但似乎INTO OUTFILE将结果原始写入指定的文件，而且如果我们使用INTO OUTFILE，结果集将为空.似乎我们不能两者兼有.

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.

这是我的样本SP

DELIMITER $$

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

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

END

有什么想法吗? 谢谢 Prem

Any thoughts ? Thanks Prem

推荐答案

假设(出于示例的原因， )表

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 ;

执行后:

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

文件内容为:

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

注意::在使用OUTFILE MySQL时，需要刷新来创建文件.如果将文件保留在输出目录中，则在后续过程调用中，将出现以下错误

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

文件'/path/to/deals.txt'已经存在

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.

这篇关于使用INTO OUTFILE的mysql存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！