仅使用 SQL 将 Blob 从 MySQL 数据库导出到文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4646533/
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
Exporting Blob from MySQL database to file with only SQL
提问by suicide
I have a table with image data stored in a blob field in a MySQL database. Is there a way to export those images to files on the filesystem by using only SQL? The images should be named {imageId}.jpg
我有一个表，其中包含存储在 MySQL 数据库中的 blob 字段中的图像数据。有没有办法只使用 SQL 将这些图像导出到文件系统上的文件？图像应命名为 {imageId}.jpg
I know that it is easy to do this with Java or whatever but is it possible with just a SQL script?
我知道使用 Java 或其他任何东西很容易做到这一点，但仅使用 SQL 脚本是否可行？
采纳答案by ajreal
I don't like the idea ...
我不喜欢这个主意...
drop procedure if exists dump_image;
delimiter //
create procedure dump_image()
begin
declare this_id int;
declare cur1 cursor for select imageId from image;
open cur1;
read_loop: loop
fetch cur1 into this_id;
set @query = concat('select blob_field from image where imageId=',
this_id, ' into outfile "/tmp/xyz-', this_id,'.jpg"');
prepare write_file from @query;
execute write_file;
end loop;
close cur1;
end //
delimiter ;
Despite the error
尽管有错误
mysql> call dump_image(); ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
ls -1 /tmp/xyz*
回答by Shaun Hare
Using INTO, and assuming you have write permission as the mysqluser in the location you wish to store the files, you can do:
使用INTO, 并假设您作为mysql用户在要存储文件的位置具有写入权限，您可以执行以下操作：
SELECT id, blob INTO DUMPFILE '/tmp/path' FROM table;
Unfortunately, in MySQL it is not possible to specify the dumpfile as an expression/variable. However, you could achieve this if you wrapped it in a stored procedure and use variables.
不幸的是，在 MySQL 中不可能将转储文件指定为表达式/变量。但是，如果将其包装在存储过程中并使用变量，则可以实现这一点。
