如何将数据从SQL附加到现有文件

SQL has the option to dump data into a file, using the INTO OUTFILE option, for exmaple

SQL可以选择使用INTO OUTFILE选项将数据转储到文件中

SELECT * from FIshReport INTO OUTFILE './FishyFile'

The problem is, this command is only allowed if the file didn't exist before it. It creates the file and then enters the data. So, is there any way to append data to a file this way?

问题是,只有在文件之前不存在的情况下才允许使用此命令。它创建文件然后输入数据。那么,有没有办法以这种方式将数据附加到文件?

4 个解决方案

#1

As the MySQL page on SELECT syntax suggests:

由于SELECT语法的MySQL页面建议:

http://dev.mysql.com/doc/refman/5.0/en/select.html

the alternative to this is to issue the SELECT from the MySQL client:

替代方法是从MySQL客户端发出SELECT:

However, if the MySQL client software is installed on the remote machine,
you can instead use a client command such as mysql -e "SELECT ..." > file_name 
to generate the file on the client host.

which, in your case, would be modified to be:

在您的情况下,将被修改为:

mysql -e "SELECT * from FishReport" >> file_name

so that you simply append to the file.

这样你只需附加到文件中。

From your Tcl script, you could simply issue this as an exec command:

从您的Tcl脚本中,您可以简单地将其作为exec命令发出:

http://www.tcl.tk/man/tcl/tutorial/Tcl26.html

#2

I think MySQL does not allow appending data to an existing file or overwriting an existing file for security reasons. A work around could be to save resuts in seperate files and then append the using file IO.

我认为MySQL出于安全原因不允许将数据附加到现有文件或覆盖现有文件。解决方法可能是将resuts保存在单独的文件中,然后附加使用文件IO。

#3

You could always append the output from your SQL script to a file using >>

您始终可以使用>>将SQL脚本的输出附加到文件中

For example (for Sybase):

例如(对于Sybase):

isql < script.sql >> outputfile.out

I can't tell you what the equivalent is for MySQL but the principle should be the same.

我不能告诉你MySQL的等价物是什么,但原理应该是一样的。

Of course output will all go to one file so if your SQL script is outputting various SQL selects to different output files then you'd need to split the script up.

当然输出都将转到一个文件,因此如果您的SQL脚本输出各种SQL选择到不同的输出文件,那么您需要拆分脚本。

#4

You could just add it to a variable. Then use a SELECT with UNION.

您可以将其添加到变量中。然后使用SELECT with UNION。

declare t varchar(100);

set @myvar = concat('
    select *  INTO OUTFILE \'',file,'\'
    from (
       select \'',t,'\'
       union all
       SELECT col from tbl where x      
    ) a' 
);
PREPARE stmt1 FROM @myvar;
EXECUTE stmt1;
Deallocate prepare stmt1;

#1