mysql - 我想将 mysql blob 导出到目录中的文件中，除了 select into dumpfile 之外还有更好的方法吗？

Question

我在 mysql 中有一个表，其中包含 blob 形式的附件。大约13GB。我想将它们导出到目录中的文件，以便用户可以通过目录路径而不是从数据库访问它们。

我做了一个小研究并得到了一些建议，但这个过程似乎比我预期的要花太多时间。下面是一个示例过程，它将循环表并导出所有 blob 并使用列文件名命名。

begin
declare this_id VARCHAR(255);
declare cur1 cursor for select DISTINCT(filename) from fileattachment where filename is not null;
open cur1;
  read_loop: loop
    fetch cur1 into this_id;
    set @query = concat('select attachment from fileattachment where filename is not null and filename="', 
        this_id, '" into DUMPFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/', this_id,'"');
    prepare write_file from @query;
    execute write_file;
  end loop;
close cur1;
end

我有大约 30,000 行，每行大约 300Kb。此过程大约需要 1 分钟才能导出单个文件，并卡在第 5 个文件上。

我尝试运行单个查询来查看它是否有任何区别，但我注意到在执行此查询之前我的内存已占满 13GB。我想象它会将整个数据库加载到内存中，即使我正在导出一个文件

select attachment from fileattachment where filename is not null and filename='kiki.jpg' 
 into DUMPFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/kiki.jpg';

我缺少一些可以优化这个的东西，或者有更好/不同的方法来做到这一点吗？

Answer 1

我终于发现真正的问题是在 MySQL 中执行此操作最终会导致读/写锁。诀窍是做一个单独的脚本，所以我修改了一些 php 代码( from MiffTheFox )，利用 file_put_contents 保存到文件。此外，如果您是从单独的机器上执行此操作，则可能需要暂时增加 mysql query_cache_size 的数据包大小。

<?php
$connection = mysql_connect("localhost", "****", "****");
mysql_select_db("myHugedatabase");
$sql = "select attachment,filename from fileattachment where filename is not null";
$result = mysql_query($sql);
if(!$result){
trigger_error("dbget: ".mysql_error()." in ".$sql);
    return false;
}

while ($row = mysql_fetch_assoc($result)){
    echo '$row["filename"]';
    file_put_contents($row["filename"], $row["attachment"]);
}
mysql_close($connection);
?>