我可以使用变量在mysql中指定OUTFILE吗 [英] can I use a variable to specify OUTFILE in mysql
问题描述
是否可以执行以下操作?这不起作用,但是显示了我想做什么
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;
推荐答案
编辑:不使用变量(仅常量值)将数据(例如表)保存到文件中
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;
现在使用变量
每当必须在sql中使用变量名时,就需要动态sql(仅适用于存储过程,不适用于简单的sql查询或触发器或函数)
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
我刚刚告诉了variable
在sql中的用法.首先,您应该确保查询是否像顶部的示例一样工作(不使用变量)
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)
结论:如果您的上述查询工作正常,那么您在某些存储过程中使用它时,我告诉的动态sql也将正常工作.
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.
这篇关于我可以使用变量在mysql中指定OUTFILE吗的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!