php数据库文件导出代码

$servername = "127.0.0.1";

$username = "root";

$password = "********";

$dbname = "data";

$pice = 5000;

$start = 0;

$fileName = 'text.csv';

setLogInfo("脚本开始执行", 'export_csv_file');

while(true){

// 创建连接

setLogInfo("establishing the mysql connection......", 'export_csv_file');

$conn = new mysqli($servername, $username, $password, $dbname);

// 检测连接

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

//查询数据总条数total

setLogInfo("读取数据总条数", 'export_csv_file');

$totalSql = "SELECT count(*) as total FROM card";

$result = $conn->query($totalSql);

$total = $result->fetch_assoc()['total'];

setLogInfo("The number of the data is :" . $total , 'export_csv_file');

//计算分页批次

$batchCount = ceil($total/$pice);

$handel = fopen('test.csv', 'a+');

for($i = 0; $i < $batchCount; $i++){

/*if($i == 20){

//关闭文件句柄

fclose($handel);

die;

}*/

//设置分页偏移量 读取数据

$start = $i * $pice;

$sql = "select * from card limit " . $start . "," . $pice;

$res = $conn->query($sql);

if ($res->num_rows > 0) {

while($row = $res->fetch_assoc()) {

//将当前批次数据写入文件

fputcsv($handel,[$row['card_id'],$row['card_number']]);

}

} else {

echo "0 results";

die;

}

unset($start,$row,$res,$sql);

}

fclose($handel);

//所有批次数据写入完毕关闭数据库链接，退出脚本 die;

setLogInfo("所有批次数据写入完毕", 'export_csv_file');

$conn->close();

die;

}

/**

* 自定义日志

* @param $info

* @param $logFileName

* @param int $length

*/

function setLogInfo($info, $logFileName)

{

$time = date('Y-m-d H:i:s') . ' : ';

$data = $info;

if(is_array($info)){

$data = implode(',',$info);

}

$data = $time . $data . PHP_EOL;

$logFileName = $logFileName . '-' . date('Y-m-d') . '.log';

$outFilePath = $logFileName;

$handle = fopen($outFilePath, "a+");

fwrite($handle, $data);

fclose($handle);

}

/**

* 导出csv文件

* @param $filename

* @param $list

*/

function saveCsv($filename, $list,$mode)

{

$fp = fopen($filename, $mode);

if($mode == 'w'){

fwrite($fp,$list);

}else{

foreach ($list as $fields) {

fputcsv($fp, $fields);

}

}

fclose($fp);

}