php导出Mysql数据含建表SQL和数据支持不加引号字段识别

最新推荐文章于 2023-09-25 15:15:38 发布

YUJIANYUE

最新推荐文章于 2023-09-25 15:15:38 发布

阅读量131

点赞数

文章标签： mysql php sql

本文链接：https://blog.csdn.net/YUJIANYUE/article/details/130664930

版权

<?php
// MySQL连接信息
$pubs = array();
$pubs["dbhost"] = "localhost";		//数据库地址本地localhost
$pubs["dbuser"] = "kehu_chalide_com";	//数据库账号
$pubs["dbpass"] = "6t4RdBJN2sas";	//数据库密码
$pubs["dbname"] = "kehu_chalide_com";	//数据库名称
$pubs["dbport"] = "3306";		//数据库端口号
$pubs["dbcode"] = "UTF8";		//数据库编码 UTF8 GB2312
$pubs["dbfile"] = "Data.bak.".date("YmdH").".sql";

foreach ($pubs as $ti=>$val) $$ti = $val; //调用

//不加引号字段属性列表
$typex = "VARBINARY|TIMESTAMP|MEDIUMINT|SMALLINT|DATETIME|TINYINT|BOOLEAN|DECIMAL|BIGINT|BINARY|DOUBLE|SERIAL|typex|FLOAT|BOOL|REAL|DATE|INT|BIT";

// 连接MySQL数据库
mysqli_report(MYSQLI_REPORT_OFF);
$conn = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport);
if (!$conn){ die("连接失败(请检查数据库配置信息): " . mysqli_connect_error()); }

// 获取所有表名
$tables = array();
$result = mysqli_query($conn, 'SHOW TABLES');
while ($row = mysqli_fetch_row($result)) { $tables[] = $row[0];}

// 导出表结构
$info = '';
$info .= "-- ----------------------------\r\n";
$info .= "-- 备份时间".date("Y-m-d H:i:s")."\r\n";
$info .= "-- 仅用于测试和学习,本程序不适合处理超大量数据\r\n";
$info .= "-- ----------------------------\r\n\r\n";
foreach ($tables as $table) {

//得到不加引号的列
$sqlc = "DESCRIBE `{$table}`";
$resu = mysqli_query($conn, $sqlc);
if (!$resu){ echo "<p>Err Table Info;</p>";}
$ii=0; $ili = "|";
while($err = $resu->fetch_assoc()){
foreach ($err as $ti=>$val) $$ti = $val; 
preg_match_all("/($typex)/i", $Type, $isu);
if($isu[0][0]){
$ili .= "$ii|"; echo "<br>$ii -> $Field -> $Type -> ".$isu[0][0];
}
$ii++;
}

//建表SQL指令
$result = mysqli_query($conn, 'SHOW CREATE TABLE ' . $table);
$row = mysqli_fetch_row($result);
$info .= "-- ----------------------------\r\n";
$info .= "-- Table structure for `".$table."`\r\n";
$info .= "-- ----------------------------\r\n";
$info .= $row[1] . ";\n\n";
}

// 导出表数据
$data = '';
foreach ($tables as $table) {
 $result = mysqli_query($conn, 'SELECT * FROM ' . $table);
 $num_fields = mysqli_num_fields($result);
 $num_recrec = mysqli_num_rows($result);
 $data .= "\r\n\r\n-- ----------------------------\r\n";
 $data .= "-- Records for `".$table."` [{$num_recrec}行{$num_fields}列]\r\n";
 $data .= "-- ----------------------------\r\n";
 if($num_recrec<1) continue;
 $data .= "DROP TABLE IF EXISTS `".$table."`;\r\n";
 $data .= 'INSERT INTO ' . $table . ' VALUES ';
 while ($row = mysqli_fetch_row($result)) {
 $data .= '(';
 for ($i = 0; $i < $num_fields; $i++) {
 if (is_null($row[$i])) {
 $data .= 'NULL';
 } elseif (stristr("$ili","|$i|")) {
 $data .= mysqli_real_escape_string($conn, $row[$i]);
 } else {
 $data .= "'" . mysqli_real_escape_string($conn, $row[$i]) . "'";
 }
 if ($i < $num_fields - 1) { $data .= ','; }
 }
 $data .= '),';
 }
 $data = rtrim($data, ',');
 $data .= ";\n";
}

/*
file_put_contents($dbfile, $info);
file_put_contents($dbfile, $data, FILE_APPEND);
*/

echo "<textarea style='width:88%;height:200px;'>$info \r\n $data</textarea>";
?>

php导出Mysql数据含建表SQL和数据支持不加引号字段识别,并以内置函数mysqli_real_escape_string转义影响 SQL的符号。