Example of MySQL database backup function implemented by thinkphp framework

This article mainly introduces the MySQL database backup function implemented by thinkphp framework, and analyzes the implementation and use skills of thinkphp export MySQL database with the example form, the friends who need can refer to the following

This paper describes the MySQL database backup function implemented by thinkphp framework. Share to everyone for your reference, as follows:

1. Reason

Since the trial of thinkphp in 2010, it has indeed brought many conveniences. Did bring me a lot of convenience. This should be a frequent backup of data, and it is inconvenient to connect to the server remotely each time. The idea of writing a thinkphp database backup SQL Generation class.

2. Introduction

Because there are use triggers in the database. Therefore, you also need to back up. And in order to insert data without being affected by the trigger to break the previously inserted data, the code to delete the trigger is generated before the data is inserted. This class does not generate the creation and deletion code for a data table, so be aware of the consistency of the two-side table structure in use.

Web development, has been using Navicat for Mysql to synchronize the local database to the server. A few days ago, a sudden whim, the local database was upgraded to the Mysql 5.5 version, the data is synchronized again when the error occurred. Think of the thinkphp that I've written before. Implementing MySQL database backup is only a function of backing up data, without exporting the table structure. So I thought of an upgrade. Make it more complete.

This upgrade adds backup table structure and view functionality. The export data increases the type judgment, the INSERT statement outputs NULL when the field is empty, and does not enclose the single quotation mark when it is a number.

<?php/** * Description: MySQL database export class based on thinkphp framework * Date: 2012-07-15 * Author: 龚辟愚 */class dbexport{/** * @description gets all the 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 (), S    Elf::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: when inserting data, it may be affected by triggers. * @static * @return String */static Public Function Buildalltriggerdropsql () {$rs = M ()->query ("Show trigger    S ");    $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 Trigg    ERs ");    $arrAll = Array (); foreach ($rs as $k =>& $v) {$arrSql = Array (' CREATE TRIGGER ', $v [' TRIGGER '], ' ', $v [' Timing '], ', $      v[' Event '], ' on ', $v [' Table '], ' to each ROW ', $v [' Statement '], '; '      );    $arrAll [] = Implode (' ', $ARRSQL);  } return implode ("\ r \ n", $arrAll); }}

Invocation 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 ()

Articles you may be interested in:

Small program face recognition with PHP face login function

PHP implementation prevents form repeat submission function "Token-based verification"

TP Framework (thinkphp) realizes three login password error after locking account function example