mysql 存储过程输出文件

Question

我试图将 SP 查询结果写入文件，但得到一致的错误代码:1086 文件已存在。尽管文件名必须是唯一的，因为它包含随机生成的数字。这是我的代码的一部分:

SET fullOutputPath = CONCAT(user,'_',FLOOR(1000+RAND()*9999),'.txt');

SELECT fullOutputPath;
-- write the resultset to the file
SELECT node_concat 
    INTO OUTFILE ",fullOutputPath,"
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
    LINES TERMINATED BY '\n'
  FROM sn_neighbour_tmp;

有什么想法吗？

Answer 1

您需要使用13.5 SQL Syntax for Prepared Statements .

示例:

...
SET `fullOutputPath` := CONCAT(USER, '_', FLOOR(1000 + RAND() * 9999), '.txt');
SET @`qry` := CONCAT('SELECT `node_concat` 
                          INTO OUTFILE ', `fullOutputPath`, ' 
                          FIELDS TERMINATED BY \',\' 
                          OPTIONALLY ENCLOSED BY \'\' 
                          LINES TERMINATED BY \'\n\' 
                      FROM `sn_neighbour_tmp`');
PREPARE `stmt` FROM @`qry`;
SET @`qry` := NULL;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
...