php - 使用php从数据库中获取数百万条记录

Question

我在使用 php 从 mysql 数据库获取数百万条记录时遇到问题，我想将这些记录加载到 Excel 文件中。当我执行查询时，如果结果集只有很少的记录(数百条)，那么它会被保存到 Excel 文件中，但是当我获取百万条记录时，它显示一个名为

的错误

内部服务器错误

服务器遇到内部错误或配置错误，无法完成您的请求。请联系服务器管理员，告知错误发生的时间以及您可能执行的任何操作导致错误。

服务器错误日志中可能提供有关此错误的更多信息。

我的代码是:

<?php
/*
author: shobhan
Date: 20-10-2014
Description: This file creates excel sheet from the database based on the selection and available for download
*/
ini_set('max_execution_time', 6000);
ini_set('memory_limit','1000M');
set_time_limit(0);
//echo ini_get('memory_limit');
include("db/config.php");   //include global configuration file
global $db_host,$db_name,$db_user,$db_password; //include global variables
$con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
$flag=1;

$download_filename="";

                header("Content-type: text/csv");
                header("Content-Disposition: attachment; filename=suburban_data.csv");
                // Disable caching
                header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
                header("Pragma: no-cache"); // HTTP 1.0
                header("Expires: 0"); // Proxies

                //echo $query; 
                //echo "<br/><br/><br/><br/><br/><br/><br/><br/>";
                $result_array=array();
                $output = fopen("php://output", "w");
                $headings=array("Patient Name","Mobile","Visit Date","Centre","Profiles","Gender","Age");
                fputcsv($output, $headings);



function preparewhere($ptstring){
global $db_host,$db_name,$db_user,$db_password; //include global variables
global $download_filename;
global $flag;
    $year=$_POST['year'];
    $centre=$_POST['centre_ids'];
    $gender=$_POST['gender'];
    $profile=$_POST['profile_ids'];
    $testresult=$_POST['testresult'];
    $age=$_POST['age'];
    $treatmenttype=$_POST['treatmenttype'];
    $where="";
    $year_condition="";
    $centre_condition="";
    $gender_condition="";
    $test_condition="";
    $testresult_condition="";
    $age_condition="";


    if($year=="0"){
        $year_condition="Visit.VstDate >=  '2013-01-01'";
    }
    else if($year=="2013"){
        $year_condition="Visit.VstDate >=  '2013-01-01' and Visit.VstDate <  '2014-01-01'";
        $download_filename.="2013";
    }
    else{
        $year_condition="Visit.VstDate >=  '2014-01-01'";
        $download_filename.="2014";
    }

    //centre condition
    if($centre!=0){
        $centre_condition=" and centres.SysNo in(".$centre.")";
        $download_filename.="_".$centre;
    }


    //gender condition
    if($gender!="0"){
        $gender_condition=" and patient.PatSex= '".$gender."'";
        $download_filename.="_".$gender;
    }
        if($flag==2){   //if input contains only profiles
        $test_condition=" and VisitProfile.ProfCode in(".$ptstring.")";
    }
    else{
        $test_condition=" and Test.TestCode in(".$ptstring.")";
    }

    //test result 
    if($flag==1){
    if($testresult!="0"){
        if($testresult=="HL"){
            $testresult_condition=" and (result.AbnormalFlag='H' or result.AbnormalFlag ='L')";
            $download_filename.="_abnormal";
        }else{
            $testresult_condition=" and result.AbnormalFlag='N'";
            $download_filename.="_normal";
        }       
    }
    }

    //age 
    if($age!="0"){
        if($age=="0-30")
            $age_condition=" and Visit.Age>=0 and Visit.Age<=30";
        else if($age=="31-40")
            $age_condition=" and Visit.Age>=31 and Visit.Age<=40";
        else if($age=="41-50")
            $age_condition=" and Visit.Age>=41 and Visit.Age<=50";
        else if($age=="51-60")
            $age_condition=" and Visit.Age>=51 and Visit.Age<=60";
        else
            $age_condition=" and Visit.Age>60";
    }
    $where=$year_condition.$centre_condition.$gender_condition.$test_condition.$testresult_condition.$age_condition;    
    return $where;      
}

//echo "where condition ".preparewhere();
//echo "<br/>";


function preparequery(){
global $flag;
$selectquery="";
    if($flag==1){
        $selectquery="select Distinct(patient.PatName), Visit.VstMobile, Visit.VstDate, centres.SysField, Test.TestName, patient.Patsex, Visit.Age from Visit
        inner join centres on Visit.RegistrationCentreCode=centres.SysNo
        inner join patient on Visit.VstPatCode=patient.Patcode
        inner join result on result.TrJobCode=Visit.VstCode
        inner join Test on Test.TestCode=result.TrTestCode
        inner join Param on Param.ParamCode=result.TrParamCode
        ";      
    }
    else{
        $selectquery="select Distinct(patient.PatName), Visit.VstMobile, Visit.VstDate, centres.SysField, profiles.ProfName, patient.Patsex, Visit.Age from Visit
        inner join centres on Visit.RegistrationCentreCode=centres.SysNo
        inner join patient on Visit.VstPatCode=patient.Patcode
        inner join VisitProfile on VisitProfile.VstCode=Visit.VstCode
        inner join profiles on profiles.ProfCode=VisitProfile.ProfCode
        ";      
    }
    return $selectquery;
}

function strpos_offset($needle, $haystack, $occurrence) {
  // explode the haystack
  $arr = explode($needle, $haystack);
  // check the needle is not out of bounds
  switch( $occurrence ) {
    case $occurrence == 0:
      return false;
    case $occurrence > max(array_keys($arr)):
      return false;
    default:
      return strlen(implode($needle, array_slice($arr, 0, $occurrence)));
  }
}


header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=suburban_data.csv");
// Disable caching
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies

if(!mysqli_connect_errno())
    {
            $count=0;
            $pcount=substr_count($_POST['profile_ids'], 'p');   //stores number of profiles from the string
            $ptcount=substr_count($_POST['profile_ids'], ',');  //stores number of tests or profiles selected

            if($pcount==0)
                $flag=1;
            else if($pcount==$ptcount+1)
                $flag=2;
            else
                $flag=3;

            if($flag==1 || $flag ==2){              //if the selection contains either profiles or tests
                //echo "profiles".$ptstring;
                if($flag==1)
                {

                $tstring=$_POST['profile_ids'];
                //echo "test".$tstring;
                $mobile="";
                $query_count_mobilenumbers=preparequery()." where ".preparewhere($tstring);
                $con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
                $start = 0;
                $limit = 1000;

                    do {
                        $mobile_result = mysqli_query(
                            $con,
                            $query_count_mobilenumbers." LIMIT {$start}, {$limit}",
                            MYSQLI_USE_RESULT      // this always helps for this kind of processing (makes a smooth streaming)
                        );

                        $nbRows = 0;       // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`

                        while($row = mysqli_fetch_array($mobile_result)) {
                        fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6])); 
                        $nbRows ++;
                        }

    // next batch
                    $start += $limit;
                    } while ($nbRows); 
                    }
                else{
                $ptstring=str_replace("p","",$_POST['profile_ids']);
                //echo "profiles are".$_POST['profile_ids'];
                $query_count_mobilenumbers=preparequery()." where ".preparewhere($ptstring);
                //echo "query is".$query_count_mobilenumbers;
                $con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
                $start = 0;
                $limit = 1000;

                    do {
                        $mobile_result = mysqli_query(
                            $con,
                            $query_count_mobilenumbers." LIMIT {$start}, {$limit}",
                            MYSQLI_USE_RESULT      // this always helps for this kind of processing (makes a smooth streaming)
                        );

                        $nbRows = 0;       // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`

                        while($row = mysqli_fetch_array($mobile_result)) {
                        fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6])); 
                        $nbRows ++;
                        }

    // next batch
                    $start += $limit;
                    } while ($nbRows); 


                }

            }   
            else{                                   //if the selection contains both profiles and tests             
                //echo "inside both";
                $no_of_profiles=substr_count($_POST['profile_ids'], 'p');
                $nth_comma_index=strpos_offset(',',$_POST['profile_ids'], $no_of_profiles); //gets the nth comma's index
                $pstring=str_replace("p","",substr($_POST['profile_ids'],0,$nth_comma_index));
                $tstring=substr($_POST['profile_ids'],$nth_comma_index+1);
                $mobile="";
                $flag=1;
                //query as tests

                //echo "inside flag";
                $query1=preparequery()." where ".preparewhere($tstring);
                $con=mysqli_connect($db_host,$db_user,$db_password,$db_name);
                //echo "quer".$query1;
                $mobile_result1 = mysqli_query($con,$query1);
                $start = 0;
                $limit = 1000;

                    do {
                        $mobile_result1 = mysqli_query(
                            $con,
                            $query1." LIMIT {$start}, {$limit}",
                            MYSQLI_USE_RESULT      // this always helps for this kind of processing (makes a smooth streaming)
                        );

                        $nbRows = 0;       // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`

                        while($row = mysqli_fetch_array($mobile_result1)) {
                        fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6])); 
                        $nbRows ++;
                        }

    // next batch
                    $start += $limit;
                    } while ($nbRows); 


                $flag=2;

                    //echo "inside flag2";
                $query2=preparequery()." where ".preparewhere($pstring);
                $start = 0;
                $limit = 1000;

                    do {
                        $mobile_result2 = mysqli_query(
                            $con,
                            $query2." LIMIT {$start}, {$limit}",
                            MYSQLI_USE_RESULT      // this always helps for this kind of processing (makes a smooth streaming)
                        );

                        $nbRows = 0;       // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`

                        while($row = mysqli_fetch_array($mobile_result2)) {
                        fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6])); 
                        $nbRows ++;
                        }


                    $start += $limit;
                    } while ($nbRows); 



                }
    }
    else{
        echo "failed to connect to database";
    }

fclose($output);


?>

Answer 1

我敢打赌，您可以在服务器错误日志中找到的错误表明 PHP 脚本内存不足。发生这种情况是因为默认情况下，当从数据库服务器接收到所有行时，对数据库的查询就会完成。由于您请求了数百万行，因此结果集非常庞大，并且占用了 PHP 进程的大量内存。

我认为您应该使用MYSQLI_USE_RESULT作为调用mysqli_query()的第三个参数:

$mobile_result = mysqli_query($con, $query_count_mobilenumbers, MYSQLI_USE_RESULT);

这使得mysqli_query()返回得更快(可能是在从服务器接收到结果的第一行时)，并且返回的结果集仅存储接收到的行，直到它们被使用 mysqli_fetch_*() 函数进行编程。

我从未使用过 mysqli，旧的 mysql 扩展有一个用于此目的的单独函数(称为 mysql_unbuffered_query())，但是 >mysql 扩展已失效，所以不要使用它。

如果这种方法还是不行，那么你可以尝试使用LIMIT从服务器批量获取数据。

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=suburban_data.csv");
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies

// The next line is very important: it prevents PHP abruptly stop the script
// before it finishes what it's doing
set_time_limit(0);
$output = fopen("php://output", "w");

$headings=array("PatientName","Email","VisitDate","Centre","Profiles","Parameter","Age");
fputcsv($output, $headings);

$start = 0;
$limit = 1000;

do {
    $mobile_result = mysqli_query(
        $con,
        $query_count_mobilenumbers." LIMIT {$start}, {$limit}",
        MYSQLI_USE_RESULT      // this always helps for this kind of processing (makes a smooth streaming)
    );

    $nbRows = 0;       // cannot use mysqli_num_rows() because of `MYSQLI_USE_RESULT`

    while($row = mysqli_fetch_array($mobile_result)) {
        fputcsv($output, array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6])); 
        $nbRows ++;
    }

    // next batch
    $start += $limit;
} while ($nbRows);           // Stop when the query didn't return any row

fclose($output);