PHP脚本导出MySQL数据库

背景：有时候需要同步数据库的表结构和部分数据，同步全表数据非常大，也不适合。还有一个种办法是使用数据库的dump命令执行备份，无法进入服务器？没有权限怎么办？

这里只要能访问服务器中的 information_schema数据库就能够进行导出。

1、查询 information_schema 中目标数据库的所有表名和存储引擎。

SELECT table_name,engine 
FROM information_schema.tables 
WHERE table_schema="数据库名";

2、查询 information_schema 中目标数据库的字符集。

SELECT CCSA.character_set_name,T.table_name
FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = '数据库名';

3、拿到目标数据库中的所有数据表名之后，查询表结构。

SELECT COLUMN_NAME 数据表的字段名
COLUMN_TYPE 字段的数据类型
COLUMN_COMMENT 字段的注释
COLUMN_DEFAULT 字段的默认值
EXTRA 字段的拓展信息，AUTO_INCREMENT

SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_DEFAULT,EXTRA 
FROM information_schema.columns 
WHERE table_schema='数据库名' 
AND table_name = '表名'

4、拿到表结构字段信息拼接表结构语句。

CREATE TABLE IF NOT EXISTS pur_accounting_log ( id int(11) auto_increment PRIMARY KEY COMMENT 'ID',accounting_time datetime default '0000-00-00 00:00:00' COMMENT '核算时间',supplier_code varchar(20) default '' COMMENT '核算维度编码',purchase_name varchar(20) default '' COMMENT '核算维度主体',is_accounting tinyint(1) default 0 COMMENT '是否核算'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5、查询表中的前 100 条数据，拼接 INSERT 语句。

 INSERT INTO pur_accounting_log (`id`,`accounting_time`,`supplier_code`,`purchase_name`,`is_accounting`) 
VALUES ('1','2023-09-25 00:00:00','A294494176','HK','0');

完成的代码：

<?php
/*** PDO：* PHP 数据对象 （PDO ：PHP Data Objects） 扩展为PHP访问数据库定义了一个轻量级的一致接口。* PDO 提供了一个数据访问抽象层，这意味着，不管使用哪种数据库，都可以用相同的函数（方法）来查询和获取数据。*/
$filePath           = './down_database.sql';// SQL文件存储位置const DB_NAME       = 'purchase';// 数据库名称
const DB_HOST       = '127.0.0.1';// 数据库IP
const DB_USERNAME   = 'root';// 用户名
const DB_PASSWORD   = '123456';// 密码
const LIMIT         = 5;
const DSN           = 'mysql:host='.DB_HOST.';dbname=' . DB_NAME;$options = [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,// 返回索引数组格式
];
$connectObj = new PDO(DSN, DB_USERNAME, DB_PASSWORD, $options);// 查询目标数据库中的所有表名称
$query_tables = "SELECT engine,table_name FROM information_schema.tables WHERE table_schema='" . DB_NAME . "'";
$tables_list  = $connectObj->query($query_tables)->fetchAll();// 查询所有表的存储字符集
$query_tables_charset = "SELECT CCSA.character_set_name,T.table_nameFROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSAWHERE CCSA.collation_name = T.table_collationAND T.table_schema = '" . DB_NAME . "'";$tables_charset  = $connectObj->query($query_tables_charset)->fetchAll();
$tables_charset  = array_column($tables_charset,'character_set_name','table_name');// 创建数据库并使用它
$create_database = "CREATE DATABASE `" . DB_NAME . "`;\n\n";
$use_database    = "USE `" . DB_NAME . "`;\n\n";rewriteSqlToFile($filePath, $create_database);
rewriteSqlToFile($filePath, $use_database);foreach ($tables_list as $value) {$table_name = $value['table_name'];$engine     = $value['engine'];$charset    = isset($tables_charset[$table_name]) ? $tables_charset[$table_name] : null;echo "开始导出表：" . DB_NAME . "." . $table_name;if (is_numeric($table_name)) {echo " ---> 失败：表明为数字无法导出\n";continue;}$query_table = "SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_DEFAULT,EXTRA FROM information_schema.columns WHERE table_schema='" . DB_NAME . "' AND table_name = '" . $table_name . "'";$tables_info = $connectObj->query($query_table)->fetchAll();// 拼接表结构$create_table_column = '';foreach ($tables_info as $column_value) {if (stripos($column_value['COLUMN_TYPE'], 'int') !== false) {$COLUMN_DEFAULT = intval($column_value['COLUMN_DEFAULT']);} else {$COLUMN_DEFAULT = "'" . strval($column_value['COLUMN_DEFAULT']) . "'";}$EXTRA = '';if ($column_value['EXTRA'] and $column_value['EXTRA'] == 'auto_increment') {$EXTRA = " " . $column_value['EXTRA'] . " PRIMARY KEY";// 自增主键} else {$EXTRA .= " default " . $COLUMN_DEFAULT;}$create_table_column .= "\t" . $column_value['COLUMN_NAME']. " " . $column_value['COLUMN_TYPE']. $EXTRA. " COMMENT '" . $column_value['COLUMN_COMMENT'] . "',\r\n";}$create_table = "CREATE TABLE IF NOT EXISTS " . $table_name . " ( \r\n" .trim($create_table_column, ",\r\n") . "\r\n". ") ENGINE=" .$engine;if( $charset ) $create_table .= " DEFAULT CHARSET=" . $charset;$create_table .= ";\r\n";rewriteSqlToFile($filePath, $create_table);// 拼接数据集合$query_table = "SELECT *FROM " . DB_NAME . "." . $table_name . " WHERE 1=1LIMIT " . LIMIT;$tables_data_list = $connectObj->query($query_table)->fetchAll();if($tables_data_list){$insert_list = [];foreach ($tables_data_list as $item) {$row_insert_sql = "INSERT INTO {$table_name} (`" . implode("`,`", array_keys($item)) . "`) ". "VALUES ('" . implode("','", array_values($item)) . "');";$insert_list[] = $row_insert_sql;}rewriteSqlToFile($filePath, implode("\r\n", $insert_list));rewriteSqlToFile($filePath, "");rewriteSqlToFile($filePath, "");}echo " ---> 成功\n";
}/*** 数据写入到SQL文件中* @param $filePath* @param $sql*/
function rewriteSqlToFile($filePath, $sql)
{file_put_contents($filePath, $sql . PHP_EOL, FILE_APPEND);
}echo "同步成功";
exit;

导出过程：