php导出百万级mysql数据到csv文件

<!--html代码-->

<a target="_blank" href="{:url('api/export/index')}">导出</a>

//controller代码

//注意超时

//如果导出时间过长，请调整nginx配置或者page_nums参数

//建议mysql版本 5.5，在mysql5.6中无效

//members表建立组合索引 create index v_type_id_index on xcx_members(v_type,id)

//v_type 为自定义字段，tinyint default 1，用来在子查询中触发组合索引

static function getData($total_page=1, $page_nums=10000)

    {

        for($i=0; $i<$total_page; $i++)

        {

            $start = $i * $page_nums;

            //子查询分页

            $sql = "SELECT mobile,realname,nickname,avatar FROM xcx_members where id>=(select id from xcx_members where v_type=1 order by id asc limit {$start},1) limit {$page_nums}";

            $list = Db::query($sql);

            foreach($list as $k=>$v)

            {

                yield [$v['mobile'],$v['realname'],$v['nickname'],$v['avatar']];

            }

            unset($list);

            //刷新缓冲区

            ob_flush();

            flush();

        }

    }

    public function index()

    {

        //生成的csv文件名称

        $demo_csv_filename = time().".csv";

        //每页查询数量

        $page_nums = 10000;

        //总页数

        $count = Db::name("members")->count("id");

        if($count < $page_nums)

        {

            $total_page = 1;

        }

        else

        {

            $total_page = intval($count / $page_nums) + 1;   

        }

        //csv文件存入的路径

        $base_path = CMF_ROOT."public/upload/";

        //导入表头

        $title = [

            '手机号','真实姓名','昵称','头像'

        ];

        $fp = fopen($base_path.$demo_csv_filename, 'a+');

        //写入标题

        fputcsv($fp, $title);

        foreach(self::getData($total_page, $page_nums) as $m=>$n)

        {

            //写入内容

            fputcsv($fp, $n);

        }

        fclose($fp);

        $download_url = cmf_get_domain()."/upload/".$demo_csv_filename;

        echo "<script type='text/javascript'>document.location.href='{$download_url}'</script>";

        die;

}