我可以使用变量在 mysql 中指定 OUTFILE 吗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13548118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
can I use a variable to specify OUTFILE in mysql
提问by johowie
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;
回答by Sami
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)
每当必须在 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;
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 variablein sql. First You should make sure if your query works like example at the top(without using variable)
我只是告诉了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.
结论：如果你上面的查询工作正常，那么我告诉的动态 sql 也会工作，因为你在一些存储过程中使用它。
回答by Mixaz
I have a low carma so I'm posting an answer that should go as a comment to Sami's post - you need to enclose the file name by quotes (note added ' before and after @fullOutputPath):
我的 Carma 很低，所以我发布的答案应该作为对 Sami 帖子的评论 - 您需要用引号将文件名括起来（注意在@fullOutputPath 之前和之后添加了 '）：
set @q1 := concat("SELECT * INTO OUTFILE '",@fullOutputPath,
"' FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
FROM database.tableName");
回答by Mustafa
If you want to do this from bash, i.e. export some data from mysql in csv to a file with dynamic name, it maybe easier and more readable like the following.
如果您想从 bash 执行此操作，即将一些数据从 csv 中的 mysql 导出到具有动态名称的文件，则可能更容易且更易读，如下所示。
The SQL with embedded bash variables:
带有嵌入式 bash 变量的 SQL：
where (e.timestamp >= ${begin_ts} and e.timestamp < ${end_ts}) order by ed.timestamp ASC ) a
INTO OUTFILE '${export_path}' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
And the bash script that runs the sql file. Notice the envsubst command that evaluates the sql script and substitutes the variables.
以及运行 sql 文件的 bash 脚本。请注意评估 sql 脚本并替换变量的 envsubst 命令。
#!/bin/bash
mysql_db="dbname"
mysql_user="mysqlpass"
mysql_pass="password"
export_path="./data.csv"
begin_ts="1478278490"
current_ts=$(date +%s -u)
sql=`export_path=${export_path} begin_ts=${last_ts} end_ts=${current_ts} envsubst < export.sql`
mysql $mysql_db -u $mysql_user -p$mysql_pass -e"${sql}"
回答by Shamis Shukoor
You cannot do it in mysql CLI but in this way it works
您不能在 mysql CLI 中执行此操作，但通过这种方式它可以工作
mysql -e "SELECT * FROM database.tableName;" -u user -p database > filename.csv
