使用php将20k或更多记录从我的SQL导出到CSV

Question

我正在尝试从mysql中的一个表导出大约20k条记录到csv，就像预期的那样，它会使我的系统崩溃，除了避免崩溃之外，还有其他选择吗？

以下是我当前的导出代码：

$filename2 = "csv/leads_".date("M-d-Y",time()).".csv";
            $fp2 = fopen($filename2, 'w') or die("can't open file");

            $sql2 = $sql_getcustomers;
            $res2 = mysql_query($sql2);

            // fetch a row and write the column names out to the file
            $row2 = mysql_fetch_assoc($res2);
            $line = "";
            $comma = "";
            if($row2){
                foreach($row2 as $name => $value) {
                    $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
                    $comma = ",";
                }

                $line .= ",crm_group";
                $line .= "\n";
                fwrite($fp2, $line);

                // remove the result pointer back to the start
                mysql_data_seek($res2, 0);

                // and loop through the actual data
                while($row2 = mysql_fetch_assoc($res2)) {      
                    $line = "";
                    $comma = "";
                    foreach($row2 as $index => $value) {
                        $line .= $comma . '"' . str_replace('"', '""', utf8_decode($value)) . '"';
                        $comma = ",";
                    }

                    //** GET THE CRM GROUPS
                    $sql_get_group = "SELECT a.crm_group_name, b.* FROM tbl_crm_members b JOIN tbl_crm_groups a ON (a.crm_gid = b.crm_groupid) WHERE crm_uid = ".$row2["uid"];
                    $sql_get_groups = mysql_query($sql_get_group);
                    $res_get_groups = "";
                    while($sgg = mysql_fetch_object($sql_get_groups)) $res_get_groups .= $sgg->crm_group_name.";";
                    $line .= ",".trim($res_get_groups, ";");
                    $line .= "\n";
                    fwrite($fp2, $line);    

                }
                fclose($fp2);

Answer 1

为什么不让mysql来做呢？

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM users WHERE 1

http://ariejan.net/2008/11/27/export-csv-directly-from-mysql/