在没有第三方库的情况下备份laravel中的mysql表

我刚到拉雷维尔。我一直在尝试创建一个以backup\u date\u.sql格式备份表的控制器，而根本不使用任何第三方库，但我很沮丧。我搜索了一些代码示例。我尝试在backupsconner中使用它们，但是事情变得越来越困难。非常感谢您的帮助。这是我的密码，提前谢谢。

<?php

public function query($data, $mode = \PDO::FETCH_ASSOC)
{
    $pdo  = DB::connection()->getPdo();
    $stmt = $pdo->query($data);
    $results = $stmt->fetchAll($mode);
    // $results = $stmt->fetch($mode);
    return $results;
}

public function backup(Request $request)
{

    if ($request->all())  {

        $output = '';

      foreach (request('table') as $table) {

        $show_table_query = $this->query("SHOW CREATE TABLE " . stripslashes($table) . "");

        foreach ($show_table_query as $show_table_row)
        {
             array_shift($show_table_row);

             $output .= implode(", ", $show_table_row);

        }

          $single_result = DB::select('select * from ' . stripslashes($table));

          foreach ($single_result as $key => $value)
          {
            $value = array_map(function($obj) {
                return (array) $obj;
            }, $single_result);

            $keys = array_keys($value[$key]);
            $val = array_values($value[$key]);
            $get_keys = array_shift($keys);
            $get_values = array_shift($val);

            $table_column = implode(",", $keys);
            // $table_value ="'" . implode("','", $val) . "'\n";
            $table_value ="'" . implode("','", $val) . "'";

            $output .= DB::insert(
            "INSERT INTO " . stripslashes($table) . "("
           . $table_column . ") VALUES(" . $table_value . ")"
         );

    }
}
?>

这是我发现的一个函数，后来修改为导出数据库，包括数据库中存在的所有数据、存储过程和函数（如果有的话）。该代码是为codeigniter应用程序编写的，但是您可以轻松地将其转换为laravel。
codeigniter版本：

<?php
    if(!function_exists("export_database")){
    function export_database($bkpFileName = null){

        $ci =& get_instance();
        $targetTables = [];
        $newLine = "\r\n";

        $queryTables = $ci->db->query('SHOW TABLES');

        foreach($queryTables->result() as $table){
            $targetTables[] = $table->Tables_in_my_db;
        }

        foreach($targetTables as $table){
            $tableData = $ci->db->query('SELECT * FROM '.$table);
            $res = $ci->db->query('SHOW CREATE TABLE '.$table);

            $cnt = 0;
            $content = (!isset($content) ?  '' : $content) . $res->row_array()["Create Table"].";" . $newLine . $newLine;
            foreach($tableData->result_array() as $row){
                $subContent = "";
                $firstQueryPart = "";
                if($cnt == 0 || $cnt % 100 == 0){
                    $firstQueryPart .= "INSERT INTO {$table} VALUES ";
                    if($tableData->num_rows() > 1)
                        $firstQueryPart .= $newLine;
                }

                $valuesQuery = "(";
                foreach($row as $key => $value){
                    $valuesQuery .= $ci->db->escape($value) . ", ";
                }

                $subContent = $firstQueryPart . rtrim($valuesQuery, ", ") . ")";

                if( (($cnt+1) % 100 == 0 && $cnt != 0) || $cnt+1 == $tableData->num_rows())
                    $subContent .= ";" . $newLine;
                else
                    $subContent .= ",";

                $content .= $subContent;
                $cnt++;
            }
            $content .= $newLine;
        }

        $content = trim($content);

        //check for stored procedures
        $storedProcedures = $ci->db->query("SHOW PROCEDURE STATUS WHERE Db = '{$ci->db->database}'");
        if($storedProcedures->num_rows() > 0){
            foreach($storedProcedures->result() as $procedure){
                $data = $ci->db->query("SHOW CREATE PROCEDURE {$procedure->Name}");
                if($data->num_rows() > 0){
                    $dropProcedureSQL = "DROP PROCEDURE IF EXISTS {$procedure->Name};";
                    $sqlQuery = $data->row_array()["Create Procedure"];
                    $sqlQuery = preg_replace("/CREATE DEFINER=.+? PROCEDURE/", "CREATE PROCEDURE IF NOT EXISTS", $sqlQuery);
                    $sqlQuery = "\r\n" . $sqlQuery . "//";
                    $content .=  $newLine . $newLine . $dropProcedureSQL . $sqlQuery ;
                }
            }
        }

        //check for functions
        $functions = $ci->db->query("SHOW FUNCTION STATUS WHERE Db = '{$ci->db->database}';");
        if($functions->num_rows() > 0){
            foreach($functions->result() as $function){
                $data = $ci->db->query("SHOW CREATE FUNCTION {$function->Name}");
                if($data->num_rows() > 0){
                    $dropFunctionSQL = "DROP function IF EXISTS {$function->Name};";
                    $sqlQuery = $data->row_array()["Create Function"];
                    $sqlQuery = preg_replace("/CREATE DEFINER=.+? FUNCTION/", "CREATE FUNCTION IF NOT EXISTS", $sqlQuery);
                    $sqlQuery = "\r\n" . $sqlQuery . "//";
                    $content .=  $newLine . $newLine . $dropFunctionSQL . $sqlQuery ;
                }
            }
        }

        $dbBackupFile = FCPATH . BKP_FILE_DIR;
        if(is_null($bkpFileName))
            $dbBackupFile .= "{$ci->db->database}.sql";
        else
            $dbBackupFile .= "{$bkpFileName}.sql";

        $handle = fopen($dbBackupFile, "w+");
        fwrite($handle, $content);
        fclose($handle);

        return $dbBackupFile;
    }
}

laravel版本：

<?php
if(!function_exists("export_database")){
    function export_database($bkpFileName = null){

        //$ci =& get_instance();
        $targetTables = [];
        $newLine = "\r\n";

        $queryTables = DB::select(DB::raw('SHOW TABLES'));

        foreach($queryTables->result() as $table){
            $targetTables[] = $table->Tables_in_my_database;
        }

        foreach($targetTables as $table){
            $tableData = DB::select(DB::raw('SELECT * FROM '.$table));
            $res = DB::select(DB::raw('SHOW CREATE TABLE '.$table));

            $cnt = 0;
            $content = (!isset($content) ?  '' : $content) . $res->row_array()["Create Table"].";" . $newLine . $newLine;
            foreach($tableData as $row){
                $subContent = "";
                $firstQueryPart = "";
                if($cnt == 0 || $cnt % 100 == 0){
                    $firstQueryPart .= "INSERT INTO {$table} VALUES ";
                    if(count($tableData) > 1)
                        $firstQueryPart .= $newLine;
                }

                $valuesQuery = "(";
                foreach($row as $key => $value){
                    $valuesQuery .= $value . ", ";
                }

                $subContent = $firstQueryPart . rtrim($valuesQuery, ", ") . ")";

                if( (($cnt+1) % 100 == 0 && $cnt != 0) || $cnt+1 == count($tableData))
                    $subContent .= ";" . $newLine;
                else
                    $subContent .= ",";

                $content .= $subContent;
                $cnt++;
            }
            $content .= $newLine;
        }

        $content = trim($content);

        //check for stored procedures
        $storedProcedures = DB::select(DB::raw("SHOW PROCEDURE STATUS WHERE Db = '{$ci->db->database}'");
        if($storedProcedures->count() > 0){
            foreach($storedProcedures->result() as $procedure){
                $data = DB::select(DB::raw("SHOW CREATE PROCEDURE {$procedure->Name}"));
                if($data->count() > 0){
                    $dropProcedureSQL = "DROP PROCEDURE IF EXISTS {$procedure->Name};";
                    $sqlQuery = $data->row_array()["Create Procedure"];
                    $sqlQuery = preg_replace("/CREATE DEFINER=.+? PROCEDURE/", "CREATE PROCEDURE IF NOT EXISTS", $sqlQuery);
                    $sqlQuery = "\r\n" . $sqlQuery . "//";
                    $content .=  $newLine . $newLine . $dropProcedureSQL . $sqlQuery ;
                }
            }
        }

        //check for functions
        $functions = DB::select(DB::raw("SHOW FUNCTION STATUS WHERE Db = '{$ci->db->database}';"));
        if($functions->count() > 0){
            foreach($functions->result() as $function){
                $data = DB::select(DB::raw("SHOW CREATE FUNCTION {$function->Name}");
                if($data->count() > 0){
                    $dropFunctionSQL = "DROP function IF EXISTS {$function->Name};";
                    $sqlQuery = $data->row_array()["Create Function"];
                    $sqlQuery = preg_replace("/CREATE DEFINER=.+? FUNCTION/", "CREATE FUNCTION IF NOT EXISTS", $sqlQuery);
                    $sqlQuery = "\r\n" . $sqlQuery . "//";
                    $content .=  $newLine . $newLine . $dropFunctionSQL . $sqlQuery ;
                }
            }
        }

        /*$dbBackupFile = FCPATH . BKP_FILE_DIR;
        if(is_null($bkpFileName))
            $dbBackupFile .= "{$ci->db->database}.sql";
        else
            $dbBackupFile .= "{$bkpFileName}.sql";

        $handle = fopen($dbBackupFile, "w+");
        fwrite($handle, $content);
        fclose($handle);*/

        return $content;
    }
}

注意：我已经尽力将上述代码从codeigniter转换为laravel。但是由于我没有运行laravel的示例来测试它，所以我不确定它是否能工作

在没有第三方库的情况下备份laravel中的mysql表

2条答案

相关问题

热门标签

最新问答