• PHP脚本导出MySQL数据库


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

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

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

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

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

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

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

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

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

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

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

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

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

    完成的代码:

    1. /**
    2. * PDO:
    3. * PHP 数据对象 (PDO :PHP Data Objects) 扩展为PHP访问数据库定义了一个轻量级的一致接口。
    4. * PDO 提供了一个数据访问抽象层,这意味着,不管使用哪种数据库,都可以用相同的函数(方法)来查询和获取数据。
    5. */
    6. $filePath = './down_database.sql';// SQL文件存储位置
    7. const DB_NAME = 'purchase';// 数据库名称
    8. const DB_HOST = '127.0.0.1';// 数据库IP
    9. const DB_USERNAME = 'root';// 用户名
    10. const DB_PASSWORD = '123456';// 密码
    11. const LIMIT = 5;
    12. const DSN = 'mysql:host='.DB_HOST.';dbname=' . DB_NAME;
    13. $options = [
    14. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,// 返回索引数组格式
    15. ];
    16. $connectObj = new PDO(DSN, DB_USERNAME, DB_PASSWORD, $options);
    17. // 查询目标数据库中的所有表名称
    18. $query_tables = "SELECT engine,table_name
    19. FROM information_schema.tables
    20. WHERE table_schema='" . DB_NAME . "'";
    21. $tables_list = $connectObj->query($query_tables)->fetchAll();
    22. // 查询所有表的存储字符集
    23. $query_tables_charset = "SELECT CCSA.character_set_name,T.table_name
    24. FROM information_schema.`TABLES` T,
    25. information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
    26. WHERE CCSA.collation_name = T.table_collation
    27. AND T.table_schema = '" . DB_NAME . "'";
    28. $tables_charset = $connectObj->query($query_tables_charset)->fetchAll();
    29. $tables_charset = array_column($tables_charset,'character_set_name','table_name');
    30. // 创建数据库并使用它
    31. $create_database = "CREATE DATABASE `" . DB_NAME . "`;\n\n";
    32. $use_database = "USE `" . DB_NAME . "`;\n\n";
    33. rewriteSqlToFile($filePath, $create_database);
    34. rewriteSqlToFile($filePath, $use_database);
    35. foreach ($tables_list as $value) {
    36. $table_name = $value['table_name'];
    37. $engine = $value['engine'];
    38. $charset = isset($tables_charset[$table_name]) ? $tables_charset[$table_name] : null;
    39. echo "开始导出表:" . DB_NAME . "." . $table_name;
    40. if (is_numeric($table_name)) {
    41. echo " ---> 失败:表明为数字无法导出\n";
    42. continue;
    43. }
    44. $query_table = "SELECT COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_DEFAULT,EXTRA
    45. FROM information_schema.columns
    46. WHERE table_schema='" . DB_NAME . "'
    47. AND table_name = '" . $table_name . "'";
    48. $tables_info = $connectObj->query($query_table)->fetchAll();
    49. // 拼接表结构
    50. $create_table_column = '';
    51. foreach ($tables_info as $column_value) {
    52. if (stripos($column_value['COLUMN_TYPE'], 'int') !== false) {
    53. $COLUMN_DEFAULT = intval($column_value['COLUMN_DEFAULT']);
    54. } else {
    55. $COLUMN_DEFAULT = "'" . strval($column_value['COLUMN_DEFAULT']) . "'";
    56. }
    57. $EXTRA = '';
    58. if ($column_value['EXTRA'] and $column_value['EXTRA'] == 'auto_increment') {
    59. $EXTRA = " " . $column_value['EXTRA'] . " PRIMARY KEY";// 自增主键
    60. } else {
    61. $EXTRA .= " default " . $COLUMN_DEFAULT;
    62. }
    63. $create_table_column .= "\t" . $column_value['COLUMN_NAME']
    64. . " " . $column_value['COLUMN_TYPE']
    65. . $EXTRA
    66. . " COMMENT '" . $column_value['COLUMN_COMMENT'] . "',\r\n";
    67. }
    68. $create_table = "CREATE TABLE IF NOT EXISTS " . $table_name . " ( \r\n" .
    69. trim($create_table_column, ",\r\n") . "\r\n"
    70. . ") ENGINE=" .$engine;
    71. if( $charset ) $create_table .= " DEFAULT CHARSET=" . $charset;
    72. $create_table .= ";\r\n";
    73. rewriteSqlToFile($filePath, $create_table);
    74. // 拼接数据集合
    75. $query_table = "SELECT *
    76. FROM " . DB_NAME . "." . $table_name . "
    77. WHERE 1=1
    78. LIMIT " . LIMIT;
    79. $tables_data_list = $connectObj->query($query_table)->fetchAll();
    80. if($tables_data_list){
    81. $insert_list = [];
    82. foreach ($tables_data_list as $item) {
    83. $row_insert_sql = "INSERT INTO {$table_name} (`" . implode("`,`", array_keys($item)) . "`) "
    84. . "VALUES ('" . implode("','", array_values($item)) . "');";
    85. $insert_list[] = $row_insert_sql;
    86. }
    87. rewriteSqlToFile($filePath, implode("\r\n", $insert_list));
    88. rewriteSqlToFile($filePath, "");
    89. rewriteSqlToFile($filePath, "");
    90. }
    91. echo " ---> 成功\n";
    92. }
    93. /**
    94. * 数据写入到SQL文件中
    95. * @param $filePath
    96. * @param $sql
    97. */
    98. function rewriteSqlToFile($filePath, $sql)
    99. {
    100. file_put_contents($filePath, $sql . PHP_EOL, FILE_APPEND);
    101. }
    102. echo "同步成功";
    103. exit;

    导出过程:

    导出SQL的部分示例:

  • 相关阅读:
    javaweb基于ssm的仓库管理系统
    Vue学习第16天——全局事件总线$bus的理解
    Java进程和线程
    [GWCTF 2019]你的名字 - SSTI注入(waf绕过)
    Himall类型帮助类将string类型转换成decimal类型
    【C语言】【数据结构】【顺序表】
    王者并发课-铂金3:一劳永逸-如何理解锁的多次可重入问题
    【Robot Framework】发送GET和POST请求
    Powdersigner + PostgreSql 同步表结构到pg数据库
    P2910 [USACO08OPEN] Clear And Present Danger S
  • 原文地址:https://blog.csdn.net/qq_16149125/article/details/133273091