mysql - 我可以使用变量在mysql中指定OUTFILE吗

Question

有没有办法做类似下面的事情？这不起作用，但显示了我想做的事情

SET @OutputPath = '/Users/jo/Documents'
SET @fullOutputPath = CONCAT(@OutputPath,'/','filename.csv')
SET @fullOutputPath2 = CONCAT(@OutputPath,'/','filename2.csv')

SELECT * INTO OUTFILE @fullOutputPath
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
FROM database.tableName;

SELECT * INTO OUTFILE @fullOutputPath2
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
FROM database.tableName2;

Answer 1

编辑:不使用变量(仅常量值)将数据(例如表格)保存到文件中

-- folder_path could could be like => c:/users/sami
-- choose the directory/folder already available in system
-- and make sure you have access to write the file there

SELECT * INTO OUTFILE 'folder_path/filename.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
FROM database.tableName;

现在使用变量

当你必须在sql中使用变量名时，你需要动态sql(仅适用于存储过程，既不适用于简单的sql查询，也不适用于触发器或函数)

SET @OutputPath := 'Users/jo/Documents'; //or any folder_path
SET @fullOutputPath := CONCAT(@OutputPath,'/','filename.csv');
SET @fullOutputPath2 := CONCAT(@OutputPath,'/','filename2.csv');

set @q1 := concat("SELECT * INTO OUTFILE ",@fullOutputPath,
" FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"'
FROM database.tableName");

set @q2 := concat("SELECT * INTO OUTFILE ",@fullOutputPath2,
" FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"'
FROM database.tableName2");

prepare s1 from @q1;
execute s1;deallocate prepare s1;

prepare s1 from @q2;
execute s1;deallocate prepare s1;

由于您的查询中已经有 ' 和 " ，所以我使用 " 连接您的查询并使用\来转义您的原始" 以确保其用作文字字符而不用于连接

刚才讲了变量在sql中的使用。首先，您应该确保您的查询是否像顶部的示例一样工作(不使用变量)

结论:如果您的上述查询工作正常，那么我告诉的动态 sql 也可以正常工作，因为您在某些存储过程中使用它。