Laravel 框架的sql文件导入与导出（laravel 数据库备份和数据恢复2）

导出代码如下

public function put_out(Request $request){
        $table_name = $request ->get('table_name');
        $DB_HOST = getenv('DB_HOST');
        $DB_DATABASE = getenv('DB_DATABASE'); //从配置文件中获取数据库信息
        $DB_USERNAME = getenv('DB_USERNAME');
        $DB_PASSWORD = getenv('DB_PASSWORD');
        $dumpfname = $DB_DATABASE . "_" . date("Y-m-d_H-i-s").".sql";
        $command = "D:\\phpserver\\server\\MySQL\\bin\\mysqldump --add-drop-table --host=$DB_HOST --user=$DB_USERNAME ";
        if ($DB_PASSWORD) $command.= "--password=". $DB_PASSWORD ." ";
        $command.= $DB_DATABASE." ";
        $command.= $table_name;
        $command.= " > " . $dumpfname;
        system($command);
      
    }

需要注意的是表名与表名之间用空格隔开，数据库名也要跟表名用空格隔开

导入代码：

 public function put_in(Request $request){
        $sql_name = $_FILES['file']['name'];
        $path = public_path();
 
        $file_name = $path.'\\'.$sql_name;  //要导入的SQL文件名
        $file_name = str_replace('\\','/',$file_name);
        //print_r($file_name);
 
        $DB_HOST = getenv('DB_HOST');
        $DB_DATABASE = getenv('DB_DATABASE'); //从配置文件中获取数据库信息
        $DB_USERNAME = getenv('DB_USERNAME');
        $DB_PASSWORD = getenv('DB_PASSWORD');
 
        set_time_limit(0); //设置超时时间为0，表示一直执行。当php在safe mode模式下无效，此时可能会导致导入超时，此时需要分段导入
        $fp = @fopen($file_name, "r") or die("不能打开SQL文件 $file_name");//打开文件
        //print_r($fp);exit;
        @$conf = mysqli_connect($DB_HOST, $DB_USERNAME, $DB_PASSWORD,$DB_DATABASE) or die("不能连接数据库 $DB_HOST");//连接数据库
        //@mysql_select_db($DB_DATABASE) or die ("不能打开数据库 $DB_DATABASE");//打开数据库
        echo "<p>正在清空数据库,请稍等....<br>";
        $result = mysqli_query($conf,"SHOW tables");
 
        //dd($result);
//        while ($currow=mysqli_fetch_array($result,MYSQLI_NUM))
//        {
//            mysqli_query($conf,"drop TABLE IF EXISTS $currow[0]");
//            echo "清空数据表【".$currow[0]."】成功！<br>";
//        }
 
        echo "<br>恭喜你清理MYSQL成功<br>";
 
        echo "正在执行导入数据库操作<br>";
        // 导入数据库的MySQL命令
        //echo "mysql -u$DB_USERNAME -p$DB_PASSWORD $DB_DATABASE < ".$file_name;
        //echo "source ".$file_name;
        //exec("source ".$file_name);
        $_sql = file_get_contents($file_name);
        $_arr = explode(';', $_sql);
        //dd($_arr);
        foreach ($_arr as $_value) {
            mysqli_query($conf,"SET NAMES 'utf8'");
            mysqli_query($conf,$_value.';');
        }
        echo "<br>导入完成！";
    }

开始以为用清空数据表，结果测试发现数据表会自动覆盖^_^