Thinkphp5.1.x版本导出sql文件

拿走直接用的函数

 public function backups()
    {

        //1.获取数据库信息

        $info = Db::getConfig();

        $dbname = $info['database'];

        //2.获取数据库所有表

        $tables = Db::query("show tables");

        //3、组装头部信息

        header("Content-type:text/html;charset=utf-8");

        $path = './data/';

        $database = $dbname; //获取当前数据库

        $info = "-- ----------------------------\r\n";

        $info .= "-- 日期：" . date("Y-m-d H:i:s", time()) . "\r\n";

        $info .= "-- MySQL - 5.7-MariaDB : Database - " . $database . "\r\n";

        $info .= "-- ----------------------------\r\n\r\n";

        $info .= "SET NAMES utf8;\r\nSET FOREIGN_KEY_CHECKS = 0;\r\n\r\n";

        //4、检查目录是否存在

        if (is_dir($path)) {

            if (is_writable($path)) {
            } else {

                echo '目录不可写';
                exit();
            }
        } else {

            mkdir($path, 0777, true);
        }

        //5、保存的文件名称

        $file_name = $path . $database . '_' . date('Ymd_His') . '.sql';

        file_put_contents($file_name, $info, FILE_APPEND);

        //6、循环表，写入数据

        foreach ($tables as $k => $v) {

            $val = $v["Tables_in_$database"];

            $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='$val' AND TABLE_SCHEMA='$dbname'";

            // $sql = "SELECT * FROM ".$val;

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

            $tableStatus = Db::query("SHOW TABLE STATUS FROM `{$res[0]['TABLE_SCHEMA']}` WHERE name = '{$res[0]['TABLE_NAME']}'");

            $eng = strtolower($tableStatus[0]['Engine']);

            //查询表结构

            $info_table = "-- ----------------------------\r\n";

            $info_table .= "-- Table structure for `$val`\r\n";

            $info_table .= "-- ----------------------------\r\n\r\n";

            $info_table .= "DROP TABLE IF EXISTS `$val`;\r\n";

            if (count($res) < 1) {

                continue;
            }

            $info_table .= "CREATE TABLE `$val` (\n\r\t";

            foreach ($res as $kk => $vv) {

                $info_table .= " `" . $vv['COLUMN_NAME'] . "` ";

                $info_table .= $vv['COLUMN_TYPE'];

                //是否允许空值

                if ($vv['IS_NULLABLE'] == 'NO') {

                    $info_table .= " NOT NULL ";
                }

                //判断主键

                if ($vv['EXTRA']) {

                    $info_table .= " AUTO_INCREMENT ";

                    $key = $vv['COLUMN_NAME'];
                }

                //编码

                // if ($vv['CHARACTER_SET_NAME']) {

                // $info_table .= " CHARACTER SET ".$vv['CHARACTER_SET_NAME'];

                // }

                //字符集

                if ($vv['COLLATION_NAME']) {

                    $info_table .= " COLLATE " . $vv['COLLATION_NAME'];
                }

                //默认数值

                if ($vv['COLUMN_DEFAULT']) {

                    $info_table .= " DEFAULT " . $vv['COLUMN_DEFAULT'];
                }

                //注释

                if ($vv['COLUMN_COMMENT']) {

                    $info_table .= " COMMENT '" . $vv['COLUMN_COMMENT'] . "',\n\r\t";
                } else {

                    $info_table .= ",\n\r\t";
                }
            }

            $info_table .= " PRIMARY KEY (`$key`) USING BTREE";

            $info_table .= "\n\r) ENGINE={$eng} AUTO_INCREMENT=212 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;\r\n\r\n";

            //查询表数据

            $info_table .= "-- ----------------------------\r\n";

            $info_table .= "-- Data for the table `$val`\r\n";

            $info_table .= "-- ----------------------------\r\n\r\n";

            file_put_contents($file_name, $info_table, FILE_APPEND);

            $sql_data = "select * from $val";

            $data = Db::query($sql_data);

            $count = count($data);

            if ($count < 1) {

                continue;
            }

            foreach ($data as $key => $value) {

                $sqlStr = "INSERT INTO `$val` VALUES (";

                foreach ($value as $v_d) {

                    $v_d = str_replace("'", "\'", $v_d);

                    $sqlStr .= "'" . $v_d . "', ";
                }

                //需要特别注意对数据的单引号进行转义处理

                //去掉最后一个逗号和空格

                $sqlStr = substr($sqlStr, 0, strlen($sqlStr) - 2);

                $sqlStr .= ");\r\n";

                file_put_contents($file_name, $sqlStr, FILE_APPEND);
            }

            $info = "\r\n";

            file_put_contents($file_name, $info, FILE_APPEND);
        }

        //7、下载数据到本地

        ob_end_clean();
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Length: ' . filesize($file_name));
        header('Content-Disposition: attachment; filename=' . basename($file_name));
        readfile($file_name);
        DownloadFile($path . $file_name);
        echo "数据已备份";
    }