Schenario：1( 假设我有一个名为myDataBase的数据库2( 假设myDataBase有一些表，如A、B、C、D3( 我必须从名为myDataBase的数据库中下载表A、B的模式
希望您已经完成了项目中所有表的迁移。如果您使用的是examplep服务器，请转到http://localhost/phpmyadmin然后选择你的数据库，然后转到顶部选项卡栏上的导出选项卡，只需按按钮即可导出
<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlUserName = "root";
$mysqlPassword = "";
$mysqlHostName = "localhost";
$DbName = "ukkoteknik";
$backup_name = "mybackup.sql";
$tables = array("admin", "sample");
Export_Database($mysqlHostName,$mysqlUserName,$mysqlPassword,$DbName, $tables, $backup_name=false );
function Export_Database($host,$user,$pass,$name, $tables=false, $backup_name=false )
{
$mysqli = new mysqli($host,$user,$pass,$name);
$mysqli->select_db($name);
$mysqli->query("SET NAMES 'utf8'");
$queryTables = $mysqli->query('SHOW TABLES');
while($row = $queryTables->fetch_row())
{
$target_tables[] = $row[0];
}
if($tables !== false)
{
$target_tables = array_intersect( $target_tables, $tables);
}
foreach($target_tables as $table)
{
$result = $mysqli->query('SELECT * FROM '.$table);
$fields_amount = $result->field_count;
$rows_num=$mysqli->affected_rows;
$res = $mysqli->query('SHOW CREATE TABLE '.$table);
$TableMLine = $res->fetch_row();
$content = (!isset($content) ? '' : $content) . "nn".$TableMLine[1].";nn";
for ($i = 0, $st_counter = 0; $i < $fields_amount; $i++, $st_counter=0)
{
while($row = $result->fetch_row())
{ //when started (and every after 100 command cycle):
if ($st_counter%100 == 0 || $st_counter == 0 )
{
$content .= "nINSERT INTO ".$table." VALUES";
}
$content .= "n(";
for($j=0; $j<$fields_amount; $j++)
{
$row[$j] = str_replace("n","\n", addslashes($row[$j]) );
if (isset($row[$j]))
{
$content .= '"'.$row[$j].'"' ;
}
else
{
$content .= '""';
}
if ($j<($fields_amount-1))
{
$content.= ',';
}
}
$content .=")";
//every after 100 command cycle [or at last line] ....p.s. but should be inserted 1 cycle eariler
if ( (($st_counter+1)%100==0 && $st_counter!=0) || $st_counter+1==$rows_num)
{
$content .= ";";
}
else
{
$content .= ",";
}
$st_counter=$st_counter+1;
}
} $content .="nnn";
}
//$backup_name = $backup_name ? $backup_name : $name."___(".date('H-i-s')."_".date('d-m-Y').")__rand".rand(1,11111111).".sql";
$date = date("Y-m-d");
$backup_name = $backup_name ? $backup_name : $name.".$date.sql";
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename="".$backup_name.""");
//echo $content; exit;
}
?>
只有DB的结构进入API响应，而不导出为(.sql(
路线
Route::group(['prefix' => 'v1/db-migration', 'middleware'=>'auth:api'], function (){
Route::post('server-db-structure-backup', [DBController::class, 'serverDBStructureBackup']);
});
DB控制器
<?php
namespace AppHttpControllersDBMigration;
use AppTraitsApiResponser;
use IlluminateHttpRequest;
use IlluminateSupportCarbon;
use IlluminateSupportFacadesDB;
use AppHttpControllersController;
use AppFacadesModulesDBMigrationDBBackupDBBackupFacade;
class DBController extends Controller
{
use ApiResponser;
public function serverDBStructureBackup()
{
try {
$database = config('app.db');
$user = config('app.dbuser');
$pass = config('app.dbpass');
$host = config('app.dbhost');
$tableViewsCounts = DB::select('SELECT count(TABLE_NAME) AS TOTALNUMBEROFTABLES FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ?', [$database]);
$tableViewsCounts = $tableViewsCounts[0]->TOTALNUMBEROFTABLES;
$viewsCounts = DB::select('SELECT count(TABLE_NAME) AS TOTALNUMBEROFVIEWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE LIKE "VIEW" AND TABLE_SCHEMA = ?', [$database]);
$viewsCounts = $viewsCounts[0]->TOTALNUMBEROFVIEWS;
$tablesCount = $tableViewsCounts-$viewsCounts;
$proceduresCounts = DB::select('SELECT count(TYPE) AS proceduresCounts FROM mysql.proc WHERE TYPE="PROCEDURE" AND db = ?', [$database]);
$proceduresCounts = $proceduresCounts[0]->proceduresCounts;
$functionsCounts = DB::select('SELECT count(TYPE) AS functionsCounts FROM mysql.proc WHERE TYPE="FUNCTION" AND db = ?', [$database]);
$functionsCounts = $functionsCounts[0]->functionsCounts;
$projectURL = url('/');
$deviceIP = Request::ip();
$all_table_create_statement = DBBackupFacade::all_table_create_statement(['database'=>$database, 'newline' => '<newline><newline>']);
$all_view_create_statement = DBBackupFacade::all_view_create_statement(['database'=>$database, 'newline' => '<newline><newline>']);
$all_procedure_create_statement = DBBackupFacade::all_procedure_function_create_statement(['database'=>$database, 'newline' => '<newline><newline>', 'ROUTINE_TYPE' => 'PROCEDURE']);
$all_function_create_statement = DBBackupFacade::all_procedure_function_create_statement(['database'=>$database, 'newline' => '<newline><newline>', 'ROUTINE_TYPE' => 'FUNCTION']);
$data = $database.' Database Backup Generated time = '.YmdTodmYPm(CarbonCarbon::now()).
'<newline>Project URL = '.$projectURL.
'<newline>Device IP = '.$deviceIP.
'<newline><newline>=============Objects Counting Start================= '.
'<newline>=============Objects Counting Start================= '.
'<newline>Total Tables + Views = '.$tableViewsCounts.
'<newline>Total Tables = '.$tablesCount.
'<newline>Total Views = '.$viewsCounts.
'<newline>Total Procedures = '.$proceduresCounts.
'<newline>Total Functions = '.$functionsCounts.
'<newline>=============Objects Counting End================= '.
'<newline>=============Objects Counting End================= '.
'<newline><newline>=============Table Structure Start================= '.
'<newline>=============Table Structure Start================= '.
'<newline><newline>SET FOREIGN_KEY_CHECKS=0; '.
'<newline>SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";'.
'<newline>START TRANSACTION;'.
'<newline>SET time_zone = "+06:00";'.
'<newline>drop database if exists '.$database.';'.
'<newline>CREATE DATABASE IF NOT EXISTS '.$database.' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'.
'<newline>use '.$database.';'.
'<newline><newline><newline>-- Total Tables = '.$tablesCount.
$all_table_create_statement. // all tables create statement will store here
'<newline><newline><newline>SET FOREIGN_KEY_CHECKS=1;'.
'<newline>COMMIT;'.
'<newline><newline>=============Table Structure end================= '.
'<newline>=============Table Structure end================= '.
'<newline><newline>=============View Structure Start================= '.
'<newline>=============View Structure Start================= '.
'<newline><newline><newline>-- Total Views = '.$viewsCounts.
$all_view_create_statement. // all views create statement will store here
'<newline><newline>=============View Structure end================= '.
'<newline>=============View Structure end================= '.
'<newline><newline>=============Procedure Structure Start================= '.
'<newline>=============Procedure Structure Start================= '.
'<newline><newline><newline>-- Total Procedures = '.$proceduresCounts.
$all_procedure_create_statement. // all procedures create statement will store here
'<newline><newline>=============Procedure Structure end================= '.
'<newline>=============Procedure Structure end================= '.
'<newline><newline>=============Function Structure Start================= '.
'<newline>=============Function Structure Start================= '.
'<newline><newline><newline>-- Total Functions = '.$functionsCounts.
$all_function_create_statement. // all functions create statement will store here
'<newline><newline>=============Function Structure end================= '.
'<newline>=============Function Structure end================= '
;
return $this->set_response([
'instructions' => [
'search <newline> and then replace with batch enter',
'search (backslash n) and then replace with batch enter ',
'search (") and then replace with only double quote ',
'search (backslash r) and then replace with empty string ',
],
'data' => $data
], 200,'success', ['DB Backup']);
} catch (Throwable $th) {
}
}
}
DB立面助手
<?php
namespace AppFacadesModulesDBMigrationDBBackup;
use IlluminateSupportFacadesDB;
class DBBackupHelper
{
public function all_table_create_statement($params=[])
{
$database = $params['database'] ?? '';
$newline = $params['newline'] ?? '';
$tables = DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema = ? and table_type="BASE TABLE"', [$database])
;
$str = '';
foreach ($tables as $key => $table)
{
$table_name = $database.'.'.$table->table_name;
$table_create_statement = ((array) DB::select('SHOW CREATE TABLE '.$table_name)[0])['Create Table'].';';
$str.= isset($newline) ? $newline.$table_create_statement : $table_create_statement;
}
return $str;
}
public function all_view_create_statement($params=[])
{
$database = $params['database'] ?? '';
$newline = $params['newline'] ?? '';
$views = DB::select('SELECT table_name FROM information_schema.tables WHERE table_schema = ? and table_type="VIEW"', [$database]);
$str = '';
foreach ($views as $key => $item)
{
$view_name = $database.'.'.$item->table_name;
$view_create_statement = ((array) DB::select('SHOW CREATE TABLE '.$view_name)[0])['Create View'].';';
$str.= isset($newline) ? $newline.$view_create_statement : $view_create_statement;
}
return $str;
}
public function all_procedure_function_create_statement($params=[])
{
$database = $params['database'] ?? '';
$newline = $params['newline'] ?? '';
$ROUTINE_TYPE = $params['ROUTINE_TYPE'] ?? '';
$prefix_proc_func = '<newline>DELIMITER $$<newline>';
$postfix_proc_func = '$$'.
'<newline>DELIMITER ;<newline>';
$list = DB::select('
SELECT
r.ROUTINE_NAME routine_name,
CONCAT("CREATE ", r.ROUTINE_TYPE, " ", r.ROUTINE_NAME, group_concat(" (",p.PARAMETER_MODE, " ", p.PARAMETER_NAME, " ", p.DATA_TYPE," ) "), r.ROUTINE_DEFINITION) AS create_statement
FROM
information_schema.ROUTINES r
LEFT JOIN information_schema.PARAMETERS p ON r.SPECIFIC_NAME = p.SPECIFIC_NAME
WHERE
r.ROUTINE_SCHEMA = ? AND r.ROUTINE_TYPE = ?
group by r.ROUTINE_NAME', [$database, $ROUTINE_TYPE]);
$str = '';
foreach ($list as $key => $item)
{
$routine_name = $item->routine_name;
$create_statement = $item->create_statement;
$drop_statement = 'DROP '.$ROUTINE_TYPE.' IF EXISTS '.$routine_name.';<newline> $$<newline>';
$create_statement= isset($newline) ? $newline.$create_statement : $routine_definition;
$str .= $prefix_proc_func.$drop_statement.$create_statement.$postfix_proc_func;
}
return $str;
}
}
文本响应前使用文本修改说明
- 搜索((，然后替换为批输入
- 搜索(\n(，然后替换为批处理输入
- 搜索("(，然后仅用双引号("(替换
- 搜索(\r(，然后用空字符串("(替换