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);
}