Example of MySQL database backup function implemented by ThinkPHP framework

This paper describes the MySQL database backup function realized by ThinkPHP framework. Share it for your reference, as follows:

1. Reason

Since the trial of ThinkPHP began in 2010, it has brought a lot of convenience. It did bring me a lot of convenience. This time, it should be necessary for frequent data backup, and it is inconvenient to connect to the server remotely every time. The idea of writing an ThinkPHP database backup SQL generation class came into being.

2. Introduction

Because of the use of triggers in the database. Therefore, 1 is also needed and backed up. And in order to insert the data will not be affected by the trigger and destroy the previously inserted data, before inserting the data, the code for deleting the trigger is generated. This class does not generate data table creation and deletion code, so in use, please pay attention to ensure that the table structure at both ends of the 1.

Do WEB development, 1 straight, Navicat For Mysql will be used to synchronize the local database to the server. A few days ago, on a whim, the local database was upgraded to Mysql 5.5, but an error occurred when the data was synchronized again. Think of ThinkPHP written before to realize Mysql database backup only backup data function, but not the function of exporting table structure. So I thought of upgrading 1. Make it more complete.

This upgrade has added the functions of backup table structure and view. The type judgment is added to the exported data. The insert statement will output NULL when the field is empty, and will not be quoted in single quotation marks when it is a number.

<?php
/**
 *  Describe : Based on ThinkPHP Framed Mysql Database export class 
 *  Date :2012-07-15
 *  Author : Gong Biyu 
 */
class DBExport
{
  /**
   * @description  Gets all table names for the current database. 
   * @static
   * @return array
   */
  static protected function getTables()
  {
    $dbName=C('DB_NAME');
    $result=M()->query("SHOW FULL TABLES FROM `{$dbName}` WHERE Table_Type = 'BASE TABLE'");
    foreach ($result as $v){
      $tbArray[]=$v['Tables_in_'.C('DB_NAME')];
    }
    return $tbArray;
  }
  static protected function getViews()
  {
    $dbName=C('DB_NAME');
    $result=M()->query("SHOW FULL TABLES FROM `{$dbName}` WHERE Table_Type = 'VIEW'");
    foreach ($result as $v){
      $tbArray[]=$v['Tables_in_'.C('DB_NAME')];
    }
    return $tbArray;
  }
  /**
   * @description  Export SQL Data, but does not contain table creation code. 
   * @static
   * @return string
   */
  static public function ExportAllData()
  {
    $tables = self::getTables();
    $arrAll = array(
      "SET FOREIGN_KEY_CHECKS=0;",
      self::BuildAllTriggerDropSql(),
      self::BuildTableSql(),
      self::BuildViewSql()
    );
    $tbl = new Model();
    foreach($tables as $table)
    {
      $arrAll[]="\r\nDELETE FROM {$table};";
      /*
      $rs = $tbl->query("SHOW COLUMNS FROM {$table}");
      $arrFields = array();
      foreach ($rs as $k=>&$v){
        $arrFields[] = "`{$v['Field']}`";
      }
      $sqlFields = implode($arrFields,",");
      */
      $rs=$tbl->query("select * from `{$table}`");
      foreach ($rs as $k=>&$v){
        $arrValues = array();
        foreach($v as $key=>$val)
        {
          if(is_numeric($val)){
            $arrValues[]=$val;
          }else if(is_null($val)){
            $arrValues[]='NULL';
          }else{
            $arrValues[]="'".addslashes($val)."'";
          }
        }
        $arrAll[] = "INSERT INTO `{$table}` VALUES (".implode(',',$arrValues).");";
      }
    }
    $arrAll[]=self::BuildTriggerCreateSql();
    return implode("\r\n",$arrAll);
  }
  static protected function BuildTableSql()
  {
    $tables = self::getTables();
    $arrAll = array();
    foreach($tables as &$val){
      $rs = M()->query("SHOW CREATE TABLE `{$val}`");
      $tbSql = preg_replace("#CREATE(.*)\\s+TABLE#","CREATE TABLE",$rs[0]['Create Table']);
      $arrAll[] = "DROP TABLE IF EXISTS `{$rs[0]['Table']}`;\r\n{$tbSql};\r\n";
    }
    return implode("\r\n",$arrAll);
  }
  static protected function BuildViewSql()
  {
    $views = self::getViews();
    $arrAll = array();
    foreach($views as &$val){
      $rs = M()->query("SHOW CREATE VIEW `{$val}`");
      $tbSql = preg_replace("#CREATE(.*)\\s+VIEW#","CREATE VIEW",$rs[0]['Create View']);
      $arrAll[] = "DROP VIEW IF EXISTS `{$rs[0]['View']}`;\r\n{$tbSql};\r\n";
    }
    return implode("\r\n",$arrAll);
  }
  /**
   * @description  If a trigger exists, the deletion code is generated. The reason is : Triggers may affect data insertion. 
   * @static
   * @return string
   */
  static public function BuildAllTriggerDropSql()
  {
    $rs = M()->query("show triggers");
    $arrAll = array();
    foreach ($rs as $k=>&$v)
    {
      $arrSql = array(
        'DROP TRIGGER IF EXISTS `',$v['Trigger'],'`;'
      );
      $arrAll[] = implode('',$arrSql);
    }
    return implode("\r\n",$arrAll);
  }
  /**
   * @description  Generate the creation code for all triggers. 
   * @static
   * @return string
   */
  static protected function BuildTriggerCreateSql()
  {
    $rs = M()->query("show triggers");
    $arrAll = array();
    foreach ($rs as $k=>&$v)
    {
      $arrSql = array(
        'CREATE TRIGGER `',$v['Trigger'],'` ',$v['Timing'],' ',$v['Event'],' ON `',
        $v['Table'],'` FOR EACH ROW ',$v['Statement'],';'
      );
      $arrAll[] = implode('',$arrSql);
    }
    return implode("\r\n",$arrAll);
  }
}

Invoke the example:

vendor('DBExport',COMMON_PATH);
header('Content-type: text/plain; charset=UTF-8');
$dbName = C('DB_NAME');
header("Content-Disposition: attachment; filename=\"{$dbName}.sql\"");
echo DBExport::ExportAllData()

For more readers interested in thinkPHP related contents, please check the topics of this site: "ThinkPHP Introduction Tutorial", "thinkPHP Template Operation Skills Summary", "ThinkPHP Common Methods Summary", "codeigniter Introduction Tutorial", "CI (CodeIgniter) Framework Advanced Tutorial", "Zend FrameWork Framework Introduction Tutorial" and "PHP Template Technology Summary".

I hope this article is helpful to the PHP programming based on ThinkPHP framework.