mysql - 从 MySQL 查询导出 CSV 时传递 outfile FILE_PATH 作为参数

Question

如何将 outfile 文件路径传递给 MySQL？我已经尝试过以下方法。它不起作用。

我的 SQL 文件

SET @query = CONCAT('SELECT *
FROM
     `registered_user`
INTO OUTFILE ',@output_location,' FIELDS TERMINATED BY \',\'
LINES TERMINATED BY \'\n\'');

PREPARE stmt FROM @query;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

我的命令行命令

mysql -u root -p test -e "set @output_location= '/var/tmp/out.csv'; source test2.sql;"

我收到以下错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/var/tmp/out.csv FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'' at line 12

如果我只提供 out.csv，它就会起作用。但我不知道该文件存储在哪里。我在上面的命令中遗漏了任何转义序列吗？

This problem已报告给MySQL。但有解决办法吗？

Answer 1

您可以通过选择查询(例如select @query)来简单地调试它的框架。

在 concat 上，如果内部有引号，请尝试使用双引号 " 而不是单引号 '。这样可以避免丢失 ' 你现在拥有的。当 concat 像 \\n

时，不要忘记转义 \n

SET @query = CONCAT("SELECT *
FROM
     `registered_user`
INTO OUTFILE '",@output_location,"'  -- you need to enclose path with quotes
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\n'");

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;