mysql、perl、从mysql表下载csv

Question

我用perl编写了这个脚本，但它崩溃了。数据库可能有百万行。我该怎么做呢？谢谢

sub MYSQL_CREATE_CSV
{
    my ($dbh, $tablename, $csvname) = @_;
    my $mysql_tablename = $dbh->quote_identifier($tablename);

    print "[INFO] EXTRACTING INFO FROM TABLE : $mysql_tablename\n";
    my $sth1 = $dbh->prepare(qq|SELECT * FROM $mysql_tablename | );
    $sth1->execute(  );

    open (FILE, ">$csvname") or die "[ERROR] $0 - cannot write to file : $! \n";
    #   EXTRACT COLUMN NAME
    my @COLUMNNAME = @{$sth1->{NAME}};
    my $temp = join (",",@COLUMNNAME);
    print FILE $temp , "\n";
    while ( my @row = $sth1->fetchrow_array(  ) )
    {
        my $temp = join(",",@row);
        print FILE $temp . "\n";
    }
    close FILE;

    print "[INFO] \t\tCREATING FILE : $csvname \n" if (-e $csvname);
}

Answer 1

在 $dbh->prepare()之前尝试使用此行

$dbh->{'mysql_use_result'} = 1;

这将告诉MySQL不要在内存中加载整个结果。

Answer 2

跳过所有perl，直接转储它：

SELECT * FROM my_table INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; 

它不会包含列名，但您可以调整查询以添加列名。