php使用Linux命令导出MySQL数据到csv

/**
 * @FILE_NAME:ExcelExport.php
 * @Description 数据导出excel,从数据库导出再下载，数据库配置
 * [mysqld]
 * secure_file_priv= ''
 * @User ZHaoGuiBin
 * @Date 2019-12-04 13:52:58
 */

class ExcelExport
{

    /**
     * @FuncName:exportExcel
     * @Description
     * @User ZHaoGuiBin
     * @Date 2019-12-24 13:42:11
     * @param string $title
     * @param $sql
     * @param $data
     * @return bool
     */
    public static function exportExcel($title = '报表导出', $sql, $data)
    {

        $data_dir = getcwd() . "/data_export/";

        //去除sql语句里的`,要不然执行shell命令报错
        $sql = str_replace('`', '', $sql);
        $sql = str_replace('"', "'", $sql);

        //删除历史表格
        self::deleteExcel();

        if (!file_exists($data_dir) || !is_writable($data_dir)) {
            if (!@mkdir($data_dir, 0755, TRUE)) {
                Tools::dieJs("alert('导出失败，请联系管理员处理');history.go(-1);");
            }
        }

        $file_name = $title . time() . ".csv";
        $temp_file_name = $title . time() . "_temp.csv";
        $file_path = $data_dir . $file_name;
        $temp_file_path = $data_dir . $temp_file_name;

        //拼接sql
        $fields = '';
        $title_fields = '';
        $temp_key = 0;
        $fields_count = count($data);
        foreach ($data as $key => $field) {
            $temp_key += 1;
            $fields .= "ifnull({$field},'') as {$field}" . ($temp_key < $fields_count ? ',' : '');
            $title_fields .= "'{$key}'" . ($temp_key < $fields_count ? ',' : '');
        }

        $sql_str = "SELECT {$title_fields} UNION SELECT {$fields} FROM ({$sql}) as t";

        $mysql_config = Yaf_Application::app()->getConfig()->toArray();
        $mysql_config = $mysql_config['database']['config'];

        // 2>&1 为了输出shell执行错误
        $export_shell = 'mysql -h ' . $mysql_config['host'] . ' -P' . $mysql_config['port'] . ' -u' . $mysql_config['username'] . ' -p' . '"' . $mysql_config['password'] . '"' . ' ' . $mysql_config['dbname'] . ' --default-character-set=utf8 -e "' . $sql_str . '" | sed ' . "'" . 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' . "'" . ' > ' . $temp_file_path . ' 2>&1';
        //文件转码
        $iconv_shell = "iconv -f UTF-8 -t GB18030 {$temp_file_path} > {$file_path} 2>&1";

//        dd($export_shell);

        exec($export_shell, $output, $return_var);
        exec($iconv_shell, $i_output, $i_return_var);
        unlink($temp_file_path);

        self::downloadExcel($file_path, $file_name);

        return true;
    }

    /**
     * @FuncName:downloadExcel
     * @Description
     * @User ZHaoGuiBin
     * @Date 2019-12-24 14:25:10
     * @param $file_path 文件地址
     * @param $file_name 文件名称
     * @return void
     */
    public static function downloadExcel($file_path, $file_name)
    {
        ob_end_clean();

        if (!file_exists($file_path)) {
            Tools::dieJs("alert('文件不存在，请联系管理员处理');history.go(-1);");
        }

        $fp = fopen($file_path, "r");
        $file_size = filesize($file_path);

        //下载文件需要用到的头
        header("Content-type:text/html;charset=utf-8");
        Header("Content-type: application/octet-stream");
        Header("Accept-Ranges: bytes");
        Header("Accept-Length:" . $file_size);
        Header("Content-Disposition: attachment; filename=" . $file_name);
        $buffer = 1024;
        $file_count = 0;

        while (!feof($fp) && $file_count < $file_size) {
            $file_con = fread($fp, $buffer);
            $file_count += $buffer;
            echo $file_con;
        }
        fclose($fp);
    }

    /**
     * @FuncName:deleteExcel
     * @Description 删除没用的表格
     * @User ZHaoGuiBin
     * @Date 2019-12-24 15:05:01
     * @return bool
     */
    public static function deleteExcel()
    {

        //几分钟前的时间戳
        $delete_time = strtotime("-5 minute");

        $data_dir = getcwd() . "/data_export/*.csv";
        $data_dir = glob($data_dir);

        if (empty($data_dir)) {
            return true;
        }

        foreach ($data_dir as $key => $file_path) {
            $temp_time = self::findNum($file_path);
            if ($temp_time < $delete_time) {
                unlink($file_path);
            }
        }

        return true;
    }

    /**
     * @FuncName:findNum
     * @Description
     * @User ZHaoGuiBin
     * @Date 2019-12-24 14:54:11
     * @param string $str
     * @return string
     */
    public static function findNum($str = '')
    {
        $str = trim($str);
        if (empty($str)) {
            return '';
        }
        $temp = array('1', '2', '3', '4', '5', '6', '7', '8', '9', '0');
        $result = '';
        for ($i = 0; $i < strlen($str); $i++) {
            if (in_array($str[$i], $temp)) {
                $result .= $str[$i];
            }
        }
        return $result;
    }


}