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

Is there a way to do something like the following ? which doesn't work but shows what I want to do

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;

解决方案

Edit: Saving data(e.g. a table) into file without using variable (only constant values)

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

Now using variable

Whenever you have to use a variable name in sql, you need dynamic sql (which is applicable in stored procedures only, neither in simple sql query nor in triggers or functions)

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;

As you had both ' and " in your query already, so I concatenated your query using " and used \ to escape your original " to ensure its use as a literal character and not used for concatenation

I just told the use of variable in sql. First You should make sure if your query works like example at the top (without using variable)

Conclusion: If your above query works fine then my told dynamic sql will work as well given that you are using it in some stored procedure.