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

导出

//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

{

$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 "";

die;

}