1、緣由

自從2010年開(kāi)始試用ThinkPHP以來(lái)，的確帶來(lái)了許多方便。的確給我?guī)?lái)了許多方便。此次應(yīng)為數(shù)據(jù)頻繁備份需要，而每次遠(yuǎn)程連接到服務(wù)器頗為不便。變萌生了寫(xiě)個(gè)ThinkPHP數(shù)據(jù)庫(kù)備份SQL生成類的念頭。

2、介紹

由于在數(shù)據(jù)庫(kù)中有使用觸發(fā)器。因此也需要一并備份。并且為了插入數(shù)據(jù)的時(shí)候不會(huì)受到觸發(fā)器影響而破壞先前插入的數(shù)據(jù)，在插入數(shù)據(jù)之前生成了刪除觸發(fā)器的代碼。 本類并不能生成數(shù)據(jù)表的創(chuàng)建和刪除代碼，因此在使用中請(qǐng)注意保證兩端表結(jié)構(gòu)的一致。

做WEB開(kāi)發(fā)，一直以來(lái)，都采用 Navicat For Mysql 將本地?cái)?shù)據(jù)庫(kù)同步到服務(wù)器上。前些天，突然心血來(lái)潮，將本地?cái)?shù)據(jù)庫(kù)升級(jí)到了 Mysql 5.5版本，再次將數(shù)據(jù)同步的時(shí)候卻發(fā)生了錯(cuò)誤。想起之前寫(xiě)過(guò)的?ThinkPHP 實(shí)現(xiàn)Mysql數(shù)據(jù)庫(kù)備份?只有備份數(shù)據(jù)的功能，而沒(méi)有導(dǎo)出表結(jié)構(gòu)的功能。于是想到了升級(jí)一下。讓其更完整。

本次升級(jí) 增加了 備份表結(jié)構(gòu)、視圖功能。導(dǎo)出數(shù)據(jù)增加了類型判斷，insert 語(yǔ)句當(dāng)字段為空的時(shí)候會(huì)輸出 NULL，當(dāng)為數(shù)字 的時(shí)候則不會(huì)帶上 單引號(hào)。

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?導(dǎo)出SQL數(shù)據(jù)，但不包含表創(chuàng)建代碼。
?????*?@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[]="rnDELETE?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("rn",$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']}`;rn{$tbSql};rn";
????????}

????????return?implode("rn",$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']}`;rn{$tbSql};rn";
????????}
????????return?implode("rn",$arrAll);
????}
????/**
?????*?@description?如果存在觸發(fā)器，生成刪除代碼。原因是:插入數(shù)據(jù)的時(shí)候可能會(huì)受到觸發(fā)器影響。
?????*?@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("rn",$arrAll);
????}

????/**
?????*?@description?生成所有觸發(fā)器的創(chuàng)建代碼。
?????*?@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("rn",$arrAll);
????}
}

調(diào)用示例:

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